Report generation method and apparatus
By constructing a dependency tree and generating executable SQL, the problem of insufficient flexibility and automation in existing report generation methods is solved. It realizes a closed-loop automation of the entire chain from data preparation to report output, supports multi-level business logic configuration, and improves processing efficiency and flexibility.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- IND BANK CO
- Filing Date
- 2026-03-19
- Publication Date
- 2026-05-29
Smart Images
Figure CN122113878A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of computer technology, specifically to a report generation method and apparatus. Background Technology
[0002] As enterprises deepen their digital transformation, report generation, as a crucial component of data analysis and decision support, directly impacts business response efficiency through its flexibility and automation. Traditional report generation methods, relying primarily on predefined data models and rigid computational logic, are increasingly revealing their limitations when facing increasingly complex and dynamic business demands.
[0003] Several report generation solutions have been proposed in the current technology. However, these solutions still have significant technical shortcomings in practical applications. First, existing solutions lack a flexible mechanism for constructing derived variables, failing to effectively support multi-level derivation and complex combination operations based on basic variables, resulting in significant manual intervention when handling complex business logic. Second, the scalability of statistical indicators is limited, making it difficult to meet the flexible and ever-changing data statistics needs in business scenarios, especially when dynamic adjustments to statistical dimensions and measurement methods are required. Third, existing report definition methods lack sufficient dynamism, with low automation from field definition to report generation, failing to achieve true real-time response. Furthermore, existing solutions generally rely on manual coding to implement variable calculation and report generation logic, unable to adapt to changes in variable rules and report configurations. This not only increases development costs but also limits the autonomous operation capabilities of business personnel, making it difficult to meet personalized data application needs.
[0004] This section is intended to provide background or context for the embodiments of this application set forth in the claims. The description herein is not an admission that it is prior art simply because it is included in this section. Summary of the Invention
[0005] In order to solve at least one of the above-mentioned problems in the prior art, embodiments of this application provide a report generation method and apparatus.
[0006] This application provides a report generation method, including the following steps: S1. In response to the variable configuration operation, based on the specified basic variables and calculation rules, generate derived variables and their calculation rules, wherein the basic variables are constructed based on the business source table; S2. In response to the indicator configuration operation, generate derived indicators and their calculation rules based on the specified basic variables and / or derived variables, statistical dimensions, measurement methods and data ranges. S3. In response to the report configuration operation, save the report configuration information, which includes the selected output object and its corresponding calculation rule identifier. The output object includes at least one of the following: basic variable, derived variable, and derived indicator. S4. Analyze the calculation rules of the derived variables, construct a dependency tree based on the dependencies between variables, traverse and optimize the dependency tree to integrate variable processing logic at the same level, and generate executable SQL by combining the report configuration information. S5. Execute the SQL to generate a report.
[0007] In some embodiments, step S1, generating the derived variables and their calculation rules, is a single derivation process, specifically: in response to the variable configuration operation, generating the derived variables and their calculation rules based on the specified basic variables and calculation rules.
[0008] In some embodiments, step S1, the generation of derived variables and their calculation rules, is a recursive derivation process, specifically including: S11. Based on the specified basic variables and operation rules, generate the first-level derived variables and their calculation rules; S12. Based on the generated first-level derived variables and / or basic variables and the specified new operation rules, generate the next-level derived variables and their calculation rules.
[0009] In some embodiments, in step S1, at least one calculation rule is generated for each derived variable, and a unique identifier is generated for each calculation rule; in step S3, when the output object includes a derived variable, the report configuration information includes a calculation rule identifier for that derived variable.
[0010] In some embodiments, step S4, which involves constructing a dependency tree based on the dependencies between variables and optimizing the dependency tree to integrate variable processing logic at the same level, specifically includes: S41. Analyze the calculation rules of the derived variables, and construct at least one dependency tree with the basic variables as the root node according to the dependency relationship between the basic variables and / or derived variables in the calculation rules; S42. Use the depth-first traversal algorithm to traverse and prune each dependency tree; S43. Using a breadth-first traversal algorithm, nodes at the same derivative level in multiple dependency trees are integrated to generate hierarchical variable processing logic.
[0011] In some embodiments, step S43, which involves integrating nodes at the same derivative level in multiple dependency trees, specifically includes: grouping all nodes at the same derivative level into the same SQL execution batch.
[0012] In some embodiments, step S5, which involves executing the SQL to generate a report, specifically includes: dynamically scheduling and sequentially executing the SQL for processing basic variables, the SQL for processing derived variables, the SQL for calculating derived indicators, and the SQL for generating reports, based on the dependency relationship from basic variables, derived variables to derived indicators, to generate reports.
[0013] This application also provides a report generation device, including: The variable management module is used to respond to variable configuration operations and generate derived variables and their calculation rules based on specified basic variables and calculation rules. The basic variables are constructed based on the business source table. The indicator management module is used to respond to indicator configuration operations and generate derived indicators and their calculation rules based on specified basic and / or derived variables, statistical dimensions, measurement methods and data ranges. The report configuration module is used to respond to report configuration operations and save report configuration information. The report configuration information includes the selected output object and its corresponding calculation rule identifier. The output object includes at least one of the following: basic variables, derived variables, and derived indicators. The parsing module is used to parse the calculation rules of the derived variables, construct a dependency tree based on the dependencies between variables, traverse and optimize the dependency tree to integrate variable processing logic at the same level, and generate executable SQL in combination with the report configuration information. The execution module is used to execute the SQL and generate reports.
[0014] This application also provides an electronic device, including a memory, a processor, and a computer program stored in the memory, wherein the processor executes the program to implement the method described in any of the above embodiments.
[0015] This application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the methods described in any of the above embodiments.
[0016] This application also provides a computer program product, which includes a computer program that, when executed by a processor, implements the methods described in any of the above embodiments.
[0017] The report generation method and apparatus proposed in this application generate derived variables and their calculation rules based on specified basic variables and calculation rules in response to variable configuration operations. The basic variables are constructed based on business source tables. In response to indicator configuration operations, derived indicators and their calculation rules are generated based on specified basic variables and / or derived variables, statistical dimensions, measurement methods, and data ranges. In response to report configuration operations, report configuration information is saved, including selected output objects and their corresponding calculation rule identifiers. The output objects include at least one of the following: basic variables, derived variables, and derived indicators. The calculation rules of the derived variables are parsed, a dependency tree is constructed based on the dependencies between variables, the dependency tree is traversed and optimized to integrate variable processing logic at the same level, and executable SQL is generated in conjunction with the report configuration information. The SQL is executed to generate a report. This constructs a full-link, configurable, and automated closed-loop method from data preparation to final report output. The dynamic parsing and optimization steps based on the dependency tree can handle complex, multi-level business logic configurations and transform them into high-performance executable code, thus ensuring flexibility while maintaining processing efficiency and operational reliability. Attached Figure Description
[0018] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 This is a flowchart illustrating a report generation method provided in an embodiment of this application.
[0019] Figure 2 This is a flowchart illustrating a report generation method provided in an embodiment of this application.
[0020] Figure 3 This is a flowchart illustrating a report generation method provided in an embodiment of this application.
[0021] Figure 4 This is a schematic diagram illustrating the process of generating a derived variable according to an embodiment of this application.
[0022] Figure 5 This is a schematic diagram of the structure of a report generation device provided in an embodiment of this application.
[0023] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation
[0024] Detailed reference is made to exemplary embodiments, examples of which are illustrated in the accompanying drawings. The following description refers to the accompanying drawings, wherein the same reference numerals in different drawings denote the same or similar elements unless otherwise stated. In the following description of exemplary embodiments, the described embodiments do not represent all embodiments consistent with the present invention. Rather, they are merely examples of apparatuses and methods consistent with several aspects of the invention as described in the appended claims.
[0025] The terminology used in this invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention. In this invention and the appended claims, the singular forms “a,” “an,” and “the” are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein is intended to represent and include any or all possible combinations of one or more of the associated listed items.
[0026] It should be understood that although the terms “first,” “second,” “third,” etc., may be used herein to describe various types of information, such information should not be limited by these terms. These terms are only used to distinguish the types of information. For example, without departing from the scope of the invention, first information may be referred to as second information, and similarly, second information may be referred to as first information. The term “if” as used herein may be understood, depending on the context, to mean “at the time,” “at this moment,” or “in response to a judgment.”
[0027] The acquisition, transmission, storage, use, and processing of data in this application all comply with the relevant provisions of national laws and regulations.
[0028] Figure 1 This is a flowchart illustrating a report generation method provided in an embodiment of this application, as shown below. Figure 1 As shown in the figure, this application provides a report generation method, including the following steps: S1. In response to the variable configuration operation, based on the specified basic variables and calculation rules, generate derived variables and their calculation rules, wherein the basic variables are constructed based on the business source table; In step S1, the basic variables are constructed based on the business source table. For example, the basic attribute fields of the business source table, such as age, gender, education, and income, are converted into atomic variables and stored in the basic variable pool. Specifically, the basic variable pool can be physically stored in a specific table of a relational database (such as MySQL), a distributed database (such as Apache Doris), or a data warehouse.
[0029] Users can initiate variable configuration operations through the system's visual interface (such as graphical drag-and-drop or form filling). In this operation, users specify one or more basic variables from the basic variable pool and specify the operation rules between these variables. These operation rules can be provided by the rule calculator.
[0030] The operation rules may include logical relationships and / or computational relationships. For example, it may include: Logical combination: (Age > 30) AND (Customer Level = 'VIP') Mathematical calculation: (Monthly income - Monthly expenses) / Monthly income (to calculate the savings rate) Function processing: CONCAT(Region, '-', Product Line) (Generates a new tag by concatenation) Range Assessment: Case When Risk Score Between 60 and 80 Then 'Medium Risk' END Based on the configuration, the system generates derived variables (such as "high-value middle-aged customers") and their corresponding formalized "calculation rules." These derived variables can be stored decoupled from their calculation rules for easy reuse and management. The derived variable itself becomes a new data field that can be referenced by all subsequent stages (such as defining metrics and configuring reports).
[0031] The derived variables generated above can be immediately used as input in the next variable configuration operation to generate the next level of derived variables. For example, based on the basic variables "number of transactions in the past year" and "total transaction amount in the past year," the first-level derived variable "active customers" can be generated. Subsequently, using "active customers" and the basic variable "account balance" as input, and through new calculation rules (such as active customers = 'yes' AND account balance > 100,000), the second-level derived variable "high-net-worth active customers" can be generated. This process can be repeated multiple times to form a complex variable derivation network.
[0032] S2. In response to the indicator configuration operation, generate derived indicators and their calculation rules based on the specified basic variables and / or derived variables, statistical dimensions, measurement methods and data ranges. In step S2, users can define derived metrics through a visual configuration interface. The configuration process requires selecting the base variable or derived variable as the metric (such as "transaction amount" or "number of customers"); specifying the statistical dimension, i.e., the grouping condition (such as "by department", "by product category", or "by month"); selecting the measurement method, such as an aggregation function (such as SUM (summation), COUNT (count), AVG (average), or DISTINCT_COUNT (duplicate count)); and specifying the data range, i.e., the filtering condition (such as "only count customers under 30 years old" or "only count transactions with a status of 'success'").
[0033] Based on the above configuration, the system generates a "derived metric" with clear business significance (such as "A product line's Q3 average sales per employee") and its detailed "calculation rules." These rules record the metric's source, dimensions, calculation function, and filtering conditions. The derived metric can also be stored decoupled from its calculation rules for easy reuse and management.
[0034] S3. In response to the report configuration operation, save the report configuration information, which includes the selected output object and its corresponding calculation rule identifier. The output object includes at least one of the following: basic variable, derived variable, and derived indicator. In step S3, the report configuration operation specifically includes selecting the output object and its corresponding calculation rule identifier.
[0035] For detailed reports: "Output objects" mainly include basic variables and derived variables. For example, a customer list report might output fields such as "Customer ID," "Age," and "Whether a customer is a high-net-worth individual." The system needs to know which specific rule (specified through the "Calculation Rule Identifier") is used to calculate each derived variable.
[0036] For statistical reports: the "output objects" are mainly derived indicators. For example, a sales performance dashboard displays "sales revenue by region" and "year-on-year growth rate." The report configuration information must include these indicators and their rule identifiers.
[0037] For hybrid reports: "Output objects" can include both variables and metrics to meet complex business needs.
[0038] S4. Analyze the calculation rules of the derived variables, construct a dependency tree based on the dependencies between variables, traverse and optimize the dependency tree to integrate variable processing logic at the same level, and generate executable SQL by combining the report configuration information. In step S4, the calculation rules of derived variables are first parsed to analyze the reference relationships between variables (i.e., which other variables a derived variable depends on for its calculation). Based on this, a "dependency tree" or "directed acyclic graph (DAG)" is constructed with the basic variables as leaf nodes or root nodes and the derived variables as intermediate nodes. A tree describes a complete chain of derivations.
[0039] To generate efficient and non-redundant SQL, the dependency tree can be optimized through traversal. This process is automatic, and its core objective is to integrate scattered computational logic, which may have nested dependencies, into well-defined, batch-executable processing steps.
[0040] For example, a specific efficient optimization method includes: Depth-first search (DFS) pruning: Traverse each tree, identify and merge duplicate nodes caused by the same calculation rules or shared subexpressions, and avoid the same data being calculated multiple times.
[0041] Breadth-First Search (BFS) integration: After pruning, the entire tree is scanned horizontally (by level), and all nodes at the same derivative level (i.e., equidistant from the root node) are grouped together. For example, all "first-level derived variables" calculated directly from the basic variables are scheduled to be executed in the same batch; all "second-level derived variables" calculated based on the first-level derived variables are scheduled to be executed in the next batch.
[0042] Through this optimization, the system generates a hierarchical, optimal variable processing logic execution plan.
[0043] Finally, this optimized execution plan is combined with the report configuration information saved in step S3 (which fields to output, by what group, and how to aggregate), and dynamically compiled to generate a series of Structured Query Language (SQL) statements that can be directly executed in the underlying database (such as Apache Doris). These SQL statements include the complete logic for creating intermediate tables, calculating derived variables, calculating derived metrics, and finally outputting the report results.
[0044] S5. Execute the SQL to generate a report.
[0045] In step S5, based on the SQL parsed in step S4 and the dependencies between them (for example, a derived variable table must be generated first before derived metrics can be calculated), these SQL tasks are dynamically scheduled and executed sequentially.
[0046] The results of SQL execution at each stage can be automatically written to a predefined or dynamically generated result table. Finally, based on the report configuration, data can be extracted from the final result table and the final report can be generated and presented to the user in real time according to a predetermined format (such as tables and charts). The entire process requires no manual intervention, realizing configuration as development and deployment as deployment.
[0047] The report generation method provided in this application, in response to variable configuration operations, generates derived variables and their calculation rules based on specified basic variables and calculation rules, wherein the basic variables are constructed based on business source tables; in response to indicator configuration operations, it generates derived indicators and their calculation rules based on specified basic variables and / or derived variables, statistical dimensions, measurement methods, and data ranges; in response to report configuration operations, it saves report configuration information, which includes selected output objects and their corresponding calculation rule identifiers, wherein the output objects include at least one of the following: basic variables, derived variables, and derived indicators; it parses the calculation rules of the derived variables, constructs a dependency tree based on the dependencies between variables, traverses and optimizes the dependency tree to integrate variable processing logic at the same level, and generates executable SQL in conjunction with the report configuration information; it executes the SQL to generate a report. In this way, a full-link, configurable, and automated closed-loop method is constructed from data foundation preparation to final report output. The dynamic parsing and optimization steps based on the dependency tree can handle complex, multi-level business logic configurations and transform them into high-performance executable code, thereby ensuring flexibility while also considering processing efficiency and operational reliability.
[0048] In some embodiments, step S1, generating the derived variables and their calculation rules, is a single derivation process, specifically: in response to the variable configuration operation, generating the derived variables and their calculation rules based on the specified basic variables and calculation rules.
[0049] Specifically, "single-time derivation process": This mode refers to the user generating the required derived variables directly and solely based on the original base variables through a single configuration operation.
[0050] "Based on specified base variables": This means that you need to associate one or more base attributes (such as combining "age", "income" and "region") to create a composite label.
[0051] "Specified operation rules" refer to the formal definitions that can perform operations on basic variable data and can be parsed and converted into database query logic by the system, including but not limited to logical comparisons (AND / OR), arithmetic operations, set operations, or function processing. Derived variables can be combined from multiple basic variables or derived from a single basic variable. For example, if the basic variable is specified as "age", the operation rules can be selected such that age >= 16 and age <= 30, resulting in a young customer tag.
[0052] like Figure 2 As shown, in some embodiments, in step S1, the generation of derived variables and their calculation rules is a recursive derivation process, specifically including: S11. Based on the specified basic variables and operation rules, generate the first-level derived variables and their calculation rules; In step S11, the user can select one or more basic variables from the basic variable pool through the configuration interface and define the operational relationships between them (such as logical judgments and arithmetic calculations). The system then generates "first-level derived variables" and their precise calculation rules accordingly. This step is similar to the "single derivation" process.
[0053] S12. Based on the generated first-level derived variables and / or basic variables and the specified new operation rules, generate the next-level derived variables and their calculation rules.
[0054] In step S12, the first-level derived variables are recombined and abstracted to construct more complex business concepts that are closer to specific scenarios. The system allows users to configure new variables, with their input sources no longer limited to the original basic variables, but instead allowing them to select pre-generated "first-level derived variables." Here, "and / or" means: it can be based entirely on existing derived variables (such as combining label A with label B), or it can mix derived variables with the original basic variables (such as adding a new real-time behavior condition based on label A).
[0055] Each derivative operation, regardless of how many existing derived variables its input source contains, defines entirely new and independent operational rules. This ensures the clarity and maintainability of each layer of derivative logic. "Next-level derived variable" is a relative concept. Variables generated by S12 automatically acquire the same attributes as "first-level derived variables"—that is, they can continue to serve as input sources and participate in the next S12 operation. This cycle continues, constructing a multi-level, tree-like, or even network-like business tag system.
[0056] For example, based on the basic variables "average monthly spending" and "average monthly login days", the rule "average monthly spending > 1000 AND average monthly login days > 20" is defined to generate the first-level derived variable "highly active and high-spending users".
[0057] Based on the first-level derived variable "highly active and high-spending users" and the basic variable "average order value", a new rule is defined: Highly active and high-spending users = 'Yes' AND average order value > industry average order value, generating the next-level variable "high-value contributing users".
[0058] Furthermore, based on "high-value contributing users" and the basic variable "recently purchased product category", a rule can be defined: "high-value contributing user = 'yes' AND recently purchased product category = 'luxury goods'", to generate the next level variable "luxury goods high-value user".
[0059] Therefore, by legally transforming each derived output into the input of the next derived one, complex business concepts can be layered together through simple, standard configuration actions. This mechanism greatly enhances the flexibility and expressiveness of business configuration.
[0060] In some embodiments, in step S1, at least one calculation rule is generated for each derived variable, and a unique identifier is generated for each calculation rule; in step S3, when the output object includes a derived variable, the report configuration information includes a calculation rule identifier for that derived variable.
[0061] Specifically, it allows configuring and maintaining multiple computation rules for the same derived variable name (such as "high-value customer"). Each computation rule fully records its specific input variables, operators, and / or thresholds. These rules logically produce derived variables with the same name but potentially different meanings.
[0062] Generating a unique identifier (e.g., a code value) for each computation rule is key to achieving fine-grained management. When creating each computation rule object, the system assigns it a globally unique identifier (ID), such as a UUID or an auto-incrementing sequence number. This identifier is used internally to precisely point to a specific computation rule, decoupled from the name of the derived variable.
[0063] When configuring a report and selecting a derived variable to be output (such as high-value customers), the user must also specify which specific calculation rule under the variable name will be used in this report. The system will then store the unique identifier of the calculation rule in the report configuration information.
[0064] like Figure 3 As shown, in some embodiments, step S4, which involves constructing a dependency tree based on the dependencies between variables and traversing and optimizing the dependency tree to integrate variable processing logic at the same level, specifically includes: S41. Analyze the calculation rules of the derived variables, and construct at least one dependency tree with the basic variables as the root node according to the dependency relationship between the basic variables and / or derived variables in the calculation rules; In step S41, the calculation rules for all derived variables are read. Each rule is an expression that explicitly lists the input items it depends on (i.e., the "parent"). These input items may be basic variables or other derived variables.
[0065] For each derived variable, treat it as a node and other variables referenced in its rules as parent nodes, establishing dependency edges from parent nodes to child nodes. The base variable, as the initial source, has no parent node.
[0066] Based on this dependency graph, the system constructs a tree structure with the basic variables as root nodes. A tree represents a complete and continuous data processing chain. Since a derived variable may be referenced by multiple downstream variables, the actual construction is usually a directed acyclic graph (DAG), but for ease of traversal optimization, it is often expanded or processed into multiple trees according to different dependency paths for analysis.
[0067] For example: Basic variables: A (age), B (income) Derivative variable rules: C = A > 30 AND B > 50000 (depending on A and B) The dependency tree constructed by D = C = 'is' AND A < 50 (depending on C, A) is shown in the example: Tree 1 (rooted at A, partially): A -> C -> D Tree 2 (rooted at B, partially): B -> C S42. Use the depth-first traversal algorithm to traverse and prune each dependency tree; In step S42, starting from the root node (basic variable) of each tree, the system traverses down each dependency path to the leaf node (a variable no longer depended on by other derived variables). During the traversal, the system records the unique computation rule represented by each visited node (usually determined by the hash value or normalized expression of the computation rule). When a logically identical node is reached again via another path, the system performs "pruning"—that is, it directs subsequent paths to the existing node instead of creating a duplicate node. This ensures that in the final execution plan, the same logic (e.g., calculating "high-value customers") is physically computed only once, regardless of how many upper-level variables reference it, significantly reducing unnecessary computation and storage overhead and improving processing performance.
[0068] S43. Using a breadth-first traversal algorithm, nodes at the same derivative level in multiple dependency trees are integrated to generate hierarchical variable processing logic.
[0069] In step S43, a breadth-first traversal is performed starting from the root node (basic variable, level 0) of all dependency trees. All derived variables that directly depend on the basic variable are grouped into the first level. Then, all variables that depend only on first-level variables (or combinations of basic variables and first-level variables) are grouped into the second level, and so on. The "derivative level" of a node is determined by the length of the longest path from it to the root node.
[0070] Same-level integration: Integrate all nodes (i.e. computational tasks) in the same derived level of the tree into a set.
[0071] Generate hierarchical processing logic: Based on this hierarchical division, the system generates a hierarchical execution plan. The plan explicitly indicates: first, all first-level computational tasks are executed in parallel; after all of them are completed, all second-level tasks are executed in parallel, and so on.
[0072] This ensures that parent node tasks complete before child node tasks, and that there are no dependencies between tasks at the same level, allowing for safe parallel execution and full utilization of distributed computing resources, thus significantly reducing overall execution time. The task set at each level can be directly compiled into one or a batch of efficient SQL statements, thereby achieving an efficient transformation from declarative configuration to procedural execution.
[0073] Steps S41 to S43 together constitute a complete, declarative configuration-oriented compilation optimization process. This allows users to freely combine complex data logic without worrying about underlying execution efficiency issues, as the system automatically generates the optimal processing path.
[0074] In some embodiments, step S43, which involves integrating nodes at the same derivative level in multiple dependency trees, specifically includes: grouping all nodes at the same derivative level into the same SQL execution batch.
[0075] For example, suppose that after optimization, there are variables C (derivative level 1), E (derivative level 1), D (derivative level 2), and F (derivative level 2). Then, the following is generated: Batch_1 = {C, E} Batch_2 = {D, F} Each non-empty `Batch_d` can generate an independent SQL task batch that can be submitted and executed as a unit. For multiple derived variables within a batch, their computational logic can be compiled and integrated into one or a set of efficient, coordinated SQL statements. For example: Write an SQL statement that uses conditional expressions such as CASE WHEN to simultaneously calculate the values of all derived variables within a batch during a single data scan, and outputs the results as multiple fields to a temporary wide table.
[0076] These SQL batches (Batch_1, Batch_2, ...) will be submitted to the underlying distributed execution engine (such as Apache Doris) according to their hierarchical order (d in ascending order).
[0077] In this way, SQL queries within the same batch contain multiple independent computations, but share data scanning and context, greatly reducing I / O overhead and task scheduling overhead. Sequential execution between batches ensures dependency correctness. This pattern perfectly complements the architectural advantages of MPP (Massively Parallel Processing) databases, enabling the parallel execution of a batch of SQL queries across all computing nodes, achieving true computational parallelism.
[0078] The generated execution plan is well-structured, making it easy to monitor and debug. Each batch corresponds to a clearly defined intermediate result, and data dependencies are clear.
[0079] In some embodiments, step S5, which involves executing the SQL to generate a report, specifically includes: dynamically scheduling and sequentially executing the SQL for processing basic variables, the SQL for processing derived variables, the SQL for calculating derived indicators, and the SQL for generating reports, based on the dependency relationship from basic variables, derived variables to derived indicators, to generate reports.
[0080] Specifically, the "dependency relationship" here refers to the global data dependency tree that has been clearly established after parsing in step S4. It precisely describes that: the basic variable is the data source, the first-level derived variables depend on the basic variable, the second-level derived variables depend on the first-level derived variables and / or the basic variable, the derived indicators depend on the variables they specify, and the final report depends on all preceding results.
[0081] Based on this graph, the execution engine strictly follows the principle of "no dependencies first, dependencies wait". This means that a task (such as calculating a derived metric) will only be scheduled for execution after all its dependent upstream tasks (such as calculating its required derived variables) have been successfully completed.
[0082] "Dynamic scheduling" refers to: Non-fixed scripts: Unlike traditional pre-written ETL scripts with fixed order, the execution plan of this system is dynamically generated based on the current configuration before each batch run. If business users modify the rules of a derived variable, the dependency tree will change accordingly, and the system will automatically re-derive and generate a new, correct execution sequence without requiring manual code adjustments or scheduling configurations.
[0083] Task encapsulation and scheduling unit: The system encapsulates different types of processing logic into standard task units: SQL for processing basic variables: This usually involves cleaning and transforming data from the source table and writing it into the basic variable table.
[0084] Processing SQL with derived variables: This refers to the SQL execution batches (Batch_1, Batch_2, ...) generated in step S4 and aggregated hierarchically. Each batch corresponds to one scheduling unit.
[0085] SQL for calculating derived metrics: Based on ready variable data, perform aggregation and statistics to generate metric results.
[0086] Report generation SQL: The final query, which associates, filters, and sorts the indicator and variable results according to the format configured in the report, and outputs the final dataset.
[0087] The system can embed or integrate a lightweight task scheduler. This scheduler receives the aforementioned task units and their dependencies, dynamically constructs a directed acyclic graph (DAG) for task execution, and monitors task status in real time. Whenever a task completes, the scheduler checks which downstream tasks have met all their preconditions and submits them to the execution engine (such as Apache Doris) for execution.
[0088] The specific manifestation of "sequential execution" is that this "sequence" is a logical, dependent order, not an absolute sequential order. Its typical execution sequence is as follows: Execute basic variable processing SQL: Retrieve the latest data from the business source system, process it into basic variables, and store it. This is the starting point for all calculations.
[0089] The SQL for processing derived variables at each layer is executed sequentially: The scheduler submits Batch_1, Batch_2, ... sequentially according to the dependency layer (derived layer). Each batch itself is a SQL task, which is computed in parallel within the distributed engine, but there is a strict sequential waiting between batches.
[0090] Executing derived metric calculation SQL: Once all variables that a metric depends on (whether basic or derived variables) have been calculated and stored, the scheduler triggers one or more metric calculation tasks. These tasks can also be executed in parallel if they do not depend on each other.
[0091] Execute report generation SQL: When all the variables and metrics data required for the report are ready, the scheduler triggers the final report query task and produces the result set.
[0092] The entire process forms a streamlined data value-added pipeline, where data is processed step by step like going through a series of procedures, and finally becomes a report.
[0093] This mechanism provides the technical support for the "flexible and ever-changing" business needs. Any configuration change will be reflected in the current execution plan through dynamic parsing and automatically executed by the scheduler, realizing "configuration as deployment". Strict scheduling based on dependencies fundamentally avoids calculation errors caused by incomplete data, ensuring the accuracy and consistency of report results.
[0094] To better understand this application, the report generation method provided in this application will be described in detail below through a specific embodiment.
[0095] This embodiment implements an automated report generation method based on a derived variable pool. This method constructs a basic variable pool based on atomic tags, employs a flexibly configurable derived variable and derived indicator definition module, supports multiple derivations and combinations of derived variables, and supports flexible expansion of statistical indicators. Simultaneously, it adopts a dynamic report definition method, combining depth-first and breadth-first traversal algorithms of trees and multithreading techniques to dynamically parse derived variables, derived indicators, and report rules into SQL. This achieves flexible BI reporting functionality from field definition to report generation, supporting fully automated generation of derived variables, derived indicators, and reports. It can meet the flexible and ever-changing data tagging and reporting application needs of businesses, giving business personnel more autonomy and greater flexibility.
[0096] The specific implementation of this application's embodiment is as follows: By adopting the open-source big data engine Apache Doris, combined with flexible derived variables, derived indicators, and report definition modules, and employing a self-developed dynamic parsing algorithm, a flexible BI reporting system is constructed. This system provides an automated solution from field definition to report definition and output, meeting the flexible and ever-changing data application needs of the data analysis field and enabling the customized generation of massive data reports. The system is mainly divided into a basic variable module, a derived variable and derived indicator definition module, a report definition module, a derived variable and report rule parsing module, and a dynamic batch processing module.
[0097] Basic Variable Module: Based on atomic tagging rules and the open-source big data engine Apache Doris, basic attribute fields (such as age, gender, education, income, etc.) from the business source table are converted into atomic variables and stored in the basic variable table. Leveraging Apache Doris's big data processing capabilities, it supports the processing of massive amounts of tagged data, laying the data foundation for the application of derived variables.
[0098] Module for defining derived variables and derived indicators: such as Figure 4As shown, derived variables are based on basic variables. Using a rule calculator, multiple basic variables can be freely combined according to requirements, and the combined variable rules are marked with code values. Similarly, based on the combined derived variables, further derivation and combination can be performed. After the derived variable rules are defined, the variables are stored as fields in the derived variable table, and the rules are stored in the derived variable rule table, thus decoupling the basic attributes of the variables from the variable rules, thereby enabling flexible definition of derived variables.
[0099] The definition of derived indicators is based on basic and derived variables. Users can freely choose the fields to be measured, the dimensions to be counted, and the measurement method. They can also perform personalized filtering on the data range. After the definition is completed, the derived indicators are implemented as fields in the corresponding dimension indicator table, and the rules are implemented in the derived indicator rule table. This decouples the basic attributes of the indicator from the indicator rules, thereby enabling the flexible definition of derived indicators.
[0100] Report Definition Module: This module adopts a dynamic configuration scheme, allowing businesses to filter specific code value data based on custom-configured derived variables to achieve personalized data filtering. After filtering, businesses can further define the report type. For detailed reports, basic and derived variables can be selected as output fields; for statistical reports, basic or derived variables can be selected for summarization, and the statistical dimensions (department level, branch level, and overall level, etc.) and measurement methods (such as counting, summing, deduplication, etc.) can be selected. After completion, the relevant configuration can be saved.
[0101] The derived variable and report rule parsing module: Derived variable parsing is based on the parent-child relationship between variables. Using a traversal algorithm, it constructs multiple trees for derived variables with parent-child dependencies (the root of the tree is the basic variable pool, the next node is a derived variable based on the basic variable, the next-next node is a secondary derived variable, and so on). A depth-first traversal algorithm is used to prune the trees to avoid duplicate nodes. Then, a breadth-first traversal algorithm is used to integrate derived variables at the same level from multiple trees to achieve hierarchical parsing of derived variables, which is then implemented as executable SQL. The results are stored in the derived variable and indicator tables. Report rule parsing is based on report configuration and uses Java multi-threaded dynamic parsing to parse the report output logic into SQL and report templates, enabling dynamic report parsing.
[0102] Dynamic Batch Processing Module: The system encapsulates task calls as APIs. During batch processing, it calls the derived variable and report rule parsing module in real time to generate SQL, and dynamically generates result tables based on derived variables and report rules. According to hierarchy and dependencies, it sequentially calls the SQL for basic variables, derived variables, derived indicators, and dynamic reports, and saves the batch results in the result table. This approach supports dynamic parsing and batch processing without requiring manual code development, and it adapts to changes in variables and report rules, enabling real-time generation of derived variables and reports. This meets the flexible and ever-changing data tagging and reporting application needs of businesses.
[0103] Based on the same inventive concept, this application also provides a report generation apparatus, which can be used to implement the method described in the above embodiments, as shown in the following embodiments. Since the principle by which the report generation apparatus solves the problem is similar to the above method, the implementation of the report generation apparatus can refer to the implementation of the above method, and repeated details will not be elaborated further. As used below, the terms "unit" or "module" can refer to a combination of software and / or hardware that implements a predetermined function. Although the apparatus described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.
[0104] Figure 5 This is a structural block diagram of a report generation device provided in an embodiment of this application, such as... Figure 5 As shown, the report generation apparatus provided in this application embodiment includes: The variable management module 21 is used to generate derived variables and their calculation rules based on specified basic variables and calculation rules in response to variable configuration operations, wherein the basic variables are constructed based on the business source table; Indicator management module 22 is used to generate derived indicators and their calculation rules in response to indicator configuration operations, based on specified basic and / or derived variables, statistical dimensions, measurement methods and data ranges. Report configuration module 23 is used to save report configuration information in response to report configuration operations. The report configuration information includes the selected output object and its corresponding calculation rule identifier. The output object includes at least one of the following: basic variables, derived variables, and derived indicators. The parsing module 24 is used to parse the calculation rules of the derived variables, construct a dependency tree based on the dependencies between variables, traverse and optimize the dependency tree to integrate variable processing logic at the same level, and generate executable SQL in combination with the report configuration information. Execution module 25 is used to execute the SQL and generate reports.
[0105] The report generation device provided in this application, in response to variable configuration operations, generates derived variables and their calculation rules based on specified basic variables and calculation rules, wherein the basic variables are constructed based on business source tables; in response to indicator configuration operations, it generates derived indicators and their calculation rules based on specified basic variables and / or derived variables, statistical dimensions, measurement methods, and data ranges; in response to report configuration operations, it saves report configuration information, which includes selected output objects and their corresponding calculation rule identifiers, wherein the output objects include at least one of the following: basic variables, derived variables, and derived indicators; it parses the calculation rules of the derived variables, constructs a dependency tree based on the dependencies between variables, traverses and optimizes the dependency tree to integrate variable processing logic at the same level, and generates executable SQL in conjunction with the report configuration information; it executes the SQL to generate a report. In this way, a full-link, configurable, and automated closed-loop method is constructed from data foundation preparation to final report output. The dynamic parsing and optimization steps based on the dependency tree can handle complex, multi-level business logic configurations and transform them into high-performance executable code, thereby ensuring flexibility while also considering processing efficiency and operational reliability.
[0106] Figure 6 This is a schematic diagram of the physical structure of an electronic device provided in an embodiment of this application, as shown below. Figure 6 As shown, the electronic device may include a processor 301, a communications interface 302, a memory 303, and a communication bus 304, wherein the processor 301, the communications interface 302, and the memory 303 communicate with each other via the communication bus 304. The processor 301 may call logical instructions in the memory 303 to execute the methods described in any of the above embodiments.
[0107] Furthermore, the logical instructions in the aforementioned memory 303 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.
[0108] This embodiment of the application provides a computer program product, which includes a computer program stored on a non-transitory computer-readable storage medium. The computer program includes program instructions, and when the program instructions are executed by a computer, the computer is able to execute the methods provided in the above-described method embodiments.
[0109] This embodiment provides a computer-readable storage medium storing a computer program that causes the computer to execute the methods provided in the above-described method embodiments.
[0110] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0111] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0112] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0113] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0114] In the description of this specification, the references to terms such as "an embodiment," "a specific embodiment," "some embodiments," "for example," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.
[0115] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of this application. It should be understood that the above descriptions are merely specific embodiments of this application and are not intended to limit the scope of protection of this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of protection of this application.
Claims
1. A report generation method, characterized in that, Includes the following steps: S1. In response to the variable configuration operation, based on the specified basic variables and calculation rules, generate derived variables and their calculation rules, wherein the basic variables are constructed based on the business source table; S2. In response to the indicator configuration operation, generate derived indicators and their calculation rules based on the specified basic variables and / or derived variables, statistical dimensions, measurement methods and data ranges. S3. In response to the report configuration operation, save the report configuration information, which includes the selected output object and its corresponding calculation rule identifier. The output object includes at least one of the following: basic variable, derived variable, and derived indicator. S4. Analyze the calculation rules of the derived variables, construct a dependency tree based on the dependencies between variables, traverse and optimize the dependency tree to integrate variable processing logic at the same level, and generate executable SQL by combining the report configuration information. S5. Execute the SQL to generate a report.
2. The method according to claim 1, characterized in that, In step S1, the generation of derived variables and their calculation rules is a single derivation process, specifically: in response to the variable configuration operation, based on the specified basic variables and calculation rules, derived variables and their calculation rules are generated.
3. The method according to claim 1, characterized in that, In step S1, the generation of derived variables and their calculation rules is a recursive derivation process, specifically including: S11. Based on the specified basic variables and operation rules, generate the first-level derived variables and their calculation rules; S12. Based on the generated first-level derived variables and / or basic variables and the specified new operation rules, generate the next-level derived variables and their calculation rules.
4. The method according to claim 1, characterized in that, In step S1, at least one calculation rule is generated for each derived variable, and a unique identifier is generated for each calculation rule; in step S3, when the output object includes a derived variable, the report configuration information includes a calculation rule identifier for that derived variable.
5. The method according to claim 1, characterized in that, In step S4, the construction of a dependency tree based on the dependencies between variables, and the traversal optimization of the dependency tree to integrate variable processing logic at the same level, specifically includes: S41. Analyze the calculation rules of the derived variables, and construct at least one dependency tree with the basic variables as the root node according to the dependency relationship between the basic variables and / or derived variables in the calculation rules; S42. Use the depth-first traversal algorithm to traverse and prune each dependency tree; S43. Using a breadth-first traversal algorithm, nodes at the same derivative level in multiple dependency trees are integrated to generate hierarchical variable processing logic.
6. The method according to claim 5, characterized in that, In step S43, the integration of nodes at the same derivative level in multiple dependency trees specifically includes: grouping all nodes at the same derivative level into the same SQL execution batch.
7. The method according to claim 1, characterized in that, In step S5, executing the SQL to generate a report specifically includes: dynamically scheduling and sequentially executing the SQL for processing basic variables, SQL for processing derived variables, SQL for calculating derived indicators, and SQL for generating reports based on the dependency relationship from basic variables, derived variables to derived indicators, and generating reports.
8. A report generation device, characterized in that, include: The variable management module is used to respond to variable configuration operations and generate derived variables and their calculation rules based on specified basic variables and calculation rules. The basic variables are constructed based on the business source table. The indicator management module is used to respond to indicator configuration operations and generate derived indicators and their calculation rules based on specified basic and / or derived variables, statistical dimensions, measurement methods and data ranges. The report configuration module is used to respond to report configuration operations and save report configuration information. The report configuration information includes the selected output object and its corresponding calculation rule identifier. The output object includes at least one of the following: basic variables, derived variables, and derived indicators. The parsing module is used to parse the calculation rules of the derived variables, construct a dependency tree based on the dependencies between variables, traverse and optimize the dependency tree to integrate variable processing logic at the same level, and generate executable SQL in combination with the report configuration information. The execution module is used to execute the SQL and generate reports.
9. An electronic device comprising a memory, a processor, and a computer program stored in the memory, characterized in that, When the processor executes the program, it implements the method as described in any one of claims 1 to 7.
10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1 to 7.