A complex report generation method and system based on conditional binding

Abstract

The present invention belongs to the technical field of report data processing, and discloses a complex report generation method and system based on conditional binding. The method comprises: loading a predefined processor, maintaining predefined processor registration information, and sending the maintained registration information to a query report process; the predefined processor comprises a predefined condition processor and a custom time processor; the predefined condition processor is used to convert the time condition into a specific range or extract information such as year, month, etc. from the condition; the custom time processor is used to support free configuration parameters to solve the scenario that the predefined condition processor does not support; the obtained configuration condition processing parameters are sent to the query report process; the query report process is used to complete the query of the data set SQL by receiving the registration information and configuration condition processing parameters. The present invention can reduce the amount of SQL writing by 30% and reduce the complex SQL function calls by 80%.

Description

Technical Field

[0001] The present invention belongs to the technical field of report data processing, and in particular relates to a complex report generation method and system based on conditional binding. Background Art

[0002] With the continuous improvement of enterprise informatization, enterprise users have more and more data report production needs. The traditional report development model relies on collecting user needs, system design and coding implementation, user acceptance and adjustment, application release and online release, etc. The development cycle is long, the quality is unstable, and it cannot be flexibly adjusted according to the personalized needs of users. Therefore, it is necessary to use the capabilities provided by the template-based report engine system to complete the rapid and WYSIWYG development of reports.

[0003] In general report engine systems, users must create data sets that meet report display requirements based on complex SQL, and need to pre-design query conditions in the data set. When user display and query requirements change, the data set often needs to be adjusted. In addition, for more complex reports (for example: year-on-year and month-on-month calculations), users must build multiple complex and logically repetitive data sets, which invisibly increases configuration costs and system complexity.

[0004] The solution for the traditional reporting engine to handle the year-on-year scenario is as follows: offset processing is performed on the parameters input by the front end in the SQL condition; repeated SQL fragments are used to ensure that data with the same structure is queried; and additional SQL is used to perform secondary processing on the data obtained in the previous step.

[0005] Through the above analysis, the problems and defects of the existing technology are as follows: the existing technology cannot effectively reduce the complexity of data set configuration due to complex conditional processing, repeated SQL, and secondary data processing, the reusability of the data set is poor, and the cost of various report query requirements is high. Summary of the invention

[0006] To overcome the problems existing in the related art, the disclosed embodiments of the present invention provide a complex report generation method and system based on conditional binding, specifically relating to a template-based report engine system, and more particularly to a complex report engine system supporting multiple data sets and conditional processing.

[0007] The technical solution is as follows: A complex report generation method based on conditional binding, comprising:

[0008] S1, application startup process: load predefined processors, maintain predefined processor registration information, and implement the maintained registration information sending query report process; the predefined processors include predefined condition processors and custom time processors; the predefined condition processors are used to convert the conditions input by the user into actual conditions according to the processing rules built into the system, and the custom time processors are used to support free configuration parameters to solve the problem of scenarios that the predefined condition processors do not support;

[0009] S2, through the configuration report process, the obtained configuration condition processing parameters are sent to the query report process;

[0010] S3, through the query report process, will receive the registration information, configure the condition processing parameters, and complete the query of the data set SQL.

[0011] In step S1, a conditional processor is predefined, including the following five cases:

[0012] Convert the time range in the condition to a new time range: the original condition interval is set to , the offset is defined in the processor and offset units , where the offset unit is is day, month, year; the conditional interval after processing is ;

[0013] The time points in the conditions are fixedly offset: the original conditions are set to , the offset is defined in the processor and offset units , where the offset unit is is day, month, year; the processed condition is ;

[0014] Convert the time points in the condition to a time range: Set the original condition to , the range unit defined in the processor is , the conditional interval after processing is As a unit and contains Range ;

[0015] Pattern extraction: Convert the date in the condition to a string and get the date fragment: The original condition is set to , the format defined in the processor is , the conditions after processing are ,in, Format function for time;

[0016] Field extraction: Extract the month, year and month in the parameters: The original condition is set to , the processor defines the fields as , the conditions after processing are ,in, Gets the function for a time field value.

[0017] Furthermore, the time range in the condition is converted to a new time range, including: calculating the year-on-year and month-on-month data for a certain period of time: the original condition interval is set to , the processor is defined as year-on-year, and the condition interval after processing is ; The processor is defined as a ring, and the condition interval after processing is ,in, For 1 year.

[0018] In step S1, when calculating the year-on-year and other periodic data of a certain time period through a custom time processor, this option is used to control whether the period takes the natural end time: the original condition interval is set to , and the latest time of the month in which the interval is , if the natural end time is taken, the conditional interval after processing is ; Otherwise, the processed condition interval is .

[0019] In step S1, the absolute value of the year and month of a time period is set by a custom time processor, and the original condition is set to Year moon Day, the custom year value is , the conditions after processing are Year moon day; when calculating the month-on-month data of the time period by week, specify the day of the week when each week starts, and set the original condition interval to , specify At the beginning of each week, Search forward to the first week start date , the conditional interval after processing is .

[0020] Furthermore, the time points in the conditions are processed with fixed offsets, including: calculating the same day last year and the same day last month.

[0021] Furthermore, the time point is offset through a custom time processor, and the offset field and offset are configured.

[0022] Furthermore, the time points in the conditions are converted into time ranges, including: calculating the total amount for this month and the total amount for this year.

[0023] Furthermore, a time point is converted into a time range through a custom time processor, and the time range that needs to be converted is configured.

[0024] Another object of the present invention is to provide a complex report generation system based on conditional binding, the system implements the complex report generation method based on conditional binding, and the system comprises:

[0025] Report condition configuration component: The report condition configuration component obtains the associated dataset list from the report configuration, and then obtains the data field information from the dataset metadata; basic information configuration supports user-defined condition name, display width, required field validation, form type and value type, and default value information; binding information configuration supports the selection of bound datasets, data dictionaries, and time processor information;

[0026] Conditional Processor Management Component: Automatically loads predefined conditional processors when the application starts, and loads custom configuration item information of custom time processors; provides search for corresponding processors according to the processing method at runtime;

[0027] Report condition processing component: Based on the report condition configuration information, call the corresponding predefined condition processor to convert the query conditions entered by the user into the query conditions corresponding to the data set, apply them to the bound data set and complete the final data query.

[0028] In combination with all the above technical solutions, the beneficial effects of the present invention are as follows: the present invention can avoid directly defining conditions in the data set, effectively reduce the complexity of data set configuration, and improve the reusability of the data set. It can provide a rich condition processing method, and various report query requirements can be realized without code. Taking the common periodic statistical report as an example, the present invention can reduce the amount of SQL writing by 30% and reduce the complex SQL function calls by 80%.

[0029] Taking civil aviation airport business as an example, there are hundreds of different business reports. According to the traditional solution, hundreds of different SQL statements need to be written to meet all business needs. After using the report generation method based on conditional binding in the present invention, since many reports have commonalities, only 1 / 3 of the original number of data set SQL statements need to be written to meet the same business needs, which simplifies the complexity of SQL and reduces the development and maintenance costs by more than 60%.

[0030] In order to achieve data statistics of different periods, existing products all require the use of fixed time periods (e.g., daily statistics, monthly statistics, annual statistics, etc.) when designing the data set SQL, or the reservation of corresponding fields in the data set results (e.g., the year, month, quarter to which the data belongs). The processing process based on condition binding mentioned in the present invention overcomes the above technical bias. Users can perform condition binding and condition processor configuration through the interface, so that the same SQL can achieve queries of different periods, so that SQL writers can focus on business data processing without considering how to design the conditions. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] The accompanying drawings herein are incorporated in and constitute a part of the specification, illustrate embodiments consistent with the present disclosure, and together with the description, serve to explain the principles of the present disclosure;

[0032] Figure 1 It is a flow chart of a complex report generation method based on conditional binding provided by an embodiment of the present invention;

[0033] Figure 2 It is a schematic diagram of a complex report generation method based on conditional binding provided by an embodiment of the present invention;

[0034] Figure 3 It is a configuration data set provided by an embodiment of the present invention, and the time query condition does not need to be reflected in the SQL interface diagram;

[0035] Figure 4 The option list of the time processor provided in the embodiment of the present invention is a processor interface diagram loaded in the application startup process;

[0036] Figure 5 When the user-defined time processor is selected and used according to the embodiment of the present invention, there will be an additional processing parameter configuration interface diagram;

[0037] Figure 6 This is a diagram of the final report interface generated by the rendering table provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0038] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the specific embodiments of the present invention are described in detail below in conjunction with the accompanying drawings. In the following description, many specific details are set forth to facilitate a full understanding of the present invention. However, the present invention can be implemented in many other ways different from those described herein, and those skilled in the art can make similar improvements without violating the connotation of the present invention, so the present invention is not limited by the specific implementation disclosed below.

[0039] The innovation of the present invention lies in that the existing report query products all rely on users to write condition placeholders in the data set SQL, and each data set can only be used to query a specific data result. The present invention dynamically converts user input content and generates query conditions through a condition processor, so that one data set can support the return of multiple data results to meet various statistical needs, thereby effectively reducing the amount of SQL writing and avoiding the problem of a large number of repeated SQL in traditional report query products.

[0040] Embodiment 1, as Figure 1 As shown, the complex report generation method based on conditional binding provided by the embodiment of the present invention includes:

[0041] S1, application startup process: load predefined processors, maintain predefined processor registration information, and implement the maintained registration information sending query report process; the predefined processors include predefined condition processors and custom time processors; the predefined condition processors are used to convert the conditions input by the user into actual conditions according to the processing rules built into the system, and the custom time processors are used to support free configuration parameters to solve the problem of scenarios that the predefined condition processors do not support;

[0042] S2, through the configuration report process, the obtained configuration condition processing parameters are sent to the query report process;

[0043] S3, through the query report process, will receive the registration information, configure the condition processing parameters, and complete the query of the data set SQL.

[0044] In step S1, the predefined conditional processor includes the following five cases:

[0045] (A) Range -> Range:

[0046] Convert the time range in the condition to a new time range: the original condition interval is set to , the offset is defined in the processor and offset units , where the offset unit is is day, month, year; the conditional interval after processing is ;

[0047] Offset the start and end time points in the condition respectively, and convert the time range in the condition into a new time range. The most common scenario is to calculate the year-on-year and month-on-month data for a specific time period (same period last year, same period last month, etc.); To calculate the year-on-year and month-on-month data for a certain time period: Set the original condition interval to , the processor is defined as year-on-year, and the condition interval after processing is ; The processor is defined as a ring, and the condition interval after processing is ,in, For 1 year.

[0048] (B) Time point -> time point: The time point in the condition is fixed offset: the original condition is set to , the offset is defined in the processor and offset units , where the offset unit is is day, month, year; the processed condition is ;

[0049] Apply a specific offset to the time point in the condition to obtain a new time point. For example, calculate the same day last year or the same day last month.

[0050] (C) Time point -> range: Convert the time point in the condition to a time range: The original condition is set to , the range unit defined in the processor is , the conditional interval after processing is As a unit and contains Range ;

[0051] Calculate the interval of the conditional time point and convert the time point in the condition into a time range. For example, the scenario is to calculate the cumulative amount of this month and this year. Calculation example: The month of May 10, 2024 is May 2024, and the converted time range of "this month" is May 1, 2024-May 31, 2024; the year is 2024, and the converted time range of "this year" is January 1, 2024-December 31, 2024; for example: t=2024-09-10, u=month, then u0=(2024-09-01,2024-09-30);

[0052] (D) Pattern Extraction: Pattern Extraction: Convert the date in the condition to a string and get the date fragment: The original condition is set to , the format defined in the processor is , the conditions after processing are ,in, Format function for time;

[0053] Convert the date in the condition to a string (date fragment) through date formatting. For example, if the condition time is May 10, 2024, and the actual query parameter obtained by formatting it as year and month (YYYYMM) is 202405;

[0054] (E) Field extraction: Extract the month and year in the parameters: The original condition is set to , the processor defines the fields as , the conditions after processing are ,in, Get function for time field value;

[0055] Extract the month and year in the parameters. For example, if the condition time is May 10, 2024 and the extracted field is the year, the actual query parameter obtained is 2024.

[0056] In step S1 of the embodiment of the present invention, when calculating the year-on-year and other periodic data of a certain time period through a custom time processor, this option is used to control whether the period takes the natural end time: the original condition interval is set to , and the latest time of the month in which the interval is , if the natural end time is taken, the conditional interval after processing is ; Otherwise, the processed condition interval is . Use a custom time processor to set absolute values ​​for the year and month of a certain time period. The original condition is set to Year moon Day, the custom year value is , the conditions after processing are Year moon day; when calculating the month-on-month data of the time period by week, specify the day of the week when each week starts, and set the original condition interval to , specify At the beginning of each week, Search forward to the first week start date , the conditional interval after processing is .

[0057] Custom event processors can support freely configurable parameters to solve scenarios that are not supported by predefined processors. The supported parameters are as follows:

[0058] Whether to end naturally: In step S101, when calculating the year-on-year and other periodic data for a specific time period, this option can be used to control whether the period takes a natural end time. When the option is checked, the natural end time of the corresponding period will be used. For example: May 1, 2024 - May 10, 2024 corresponds to the same period last year. If "Use natural end time" is checked, the corresponding period is May 1, 2023 - May 31, 2023, otherwise the corresponding period is May 1, 2023 - May 10, 2023.

[0059] Absolute value: In step S101, if the year or month of a specific time period is required, you can enter a value in the offset and check "Use absolute value", for example: compare the data of 2020;

[0060] Week start day: In step S101, when calculating the weekly statistics of the month-on-month data of a specific time period, the day of the week that the week starts can be specified according to actual statistical needs. For example, if the user's statistical needs are from last Thursday to this Wednesday, the week start day can be set to Thursday.

[0061] Offset field and offset: In step S102, if the time point needs to be offset, the offset field can be selected through the drop-down box and the offset value can be entered, for example: -1 week, +1 day, etc.

[0062] Range field: In step S103, if the time point needs to be converted into a time range, the time range to be converted can be selected through the drop-down box, for example: year, month, etc.

[0063] Example 2, as another embodiment of the present invention, Figure 2 The complex report generation method based on conditional binding provided by the embodiment of the present invention includes:

[0064] (1) Application startup process:

[0065] (1.1) Load the predefined processor;

[0066] (1.2) Maintain processor registration information;

[0067] (2) Configuration report process:

[0068] (2.1) Configure the data set: Write SQL to query data from the database and define dimension and column information;

[0069] (2.2) Configure query conditions: Configure the query condition form through the interface;

[0070] (2.3) Configure the binding relationship between conditions and data sets: select the data sets and dimensions corresponding to the query conditions;

[0071] (2.4) Configure the condition processing method: select a predefined condition processor or a custom condition processor;

[0072] (2.5) Configure conditional processing parameters: When using a custom time processor, configure the necessary parameters;

[0073] (3) Query report process:

[0074] (3.1) Enter query conditions: Enter appropriate values ​​in the generated query form;

[0075] (3.2) Obtaining conditional configuration data and parameter information: The background loads user-configured conditions and binding relationships and other information;

[0076] (3.3) Select the appropriate processor: Create a corresponding conditional processor instance based on the conditional processor selected by the user in step 2.4;

[0077] (3.4) Set processing condition parameters: Set the conditional processor of step 3.3 according to the parameters configured by the user in step 2.5;

[0078] (3.5) Calling the processor to convert the query condition: Executing the processor obtained in 3.4 to convert the condition value input by the user into the condition value actually used for the query;

[0079] (3.6) Splice the processed conditions into the data set SQL: Splice the condition values ​​processed in 3.5 into the SQL to dynamically generate new SQL;

[0080] (3.7) Execute the data set SQL to complete the query: Execute the SQL generated in 3.6 to obtain the query results.

[0081] Example 3, an example of a complex report based on time statistics, the complex report generation method based on conditional binding provided by the embodiment of the present invention first needs to perform a report configuration process;

[0082] (1) Configure the data set. The time query condition does not need to be reflected in the SQL; for example Figure 3 As shown,

[0083] (2) Select the data set configured in the previous step and configure the report interface template (query conditions and data table);

[0084] (3) Configure the binding relationship between conditions and data sets, as well as the condition processing methods and processing parameters;

[0085] The option list of the time processor is the processor loaded during the application startup process; Figure 4 As shown;

[0086] When you choose to use a custom time processor, there will be additional processing parameter configurations such as Figure 5 shown.

[0087] (4) Enter the conditions to execute the report query process. After the background processes the conditions, it returns the data for the current day, this month, and this year in turn. Appropriate splicing conditions are used to execute the query, and then the table is rendered to generate the final report, such as Figure 6 shown.

[0088] Embodiment 4, the complex report generation system based on conditional binding provided by the embodiment of the present invention, based on the binding relationship configuration of query conditions and data sets, uses a predefined processor to complete conditional processing. Specifically includes:

[0089] Report condition configuration component: The report condition configuration component obtains the associated dataset list from the report configuration, and then obtains the data field information from the dataset metadata. Basic information configuration supports user-defined condition name, display width, required field validation, form type and value type, default value and other information. Binding information configuration supports the selection of bound datasets, data dictionaries, time processors and other information.

[0090] Conditional Processor Management Component: Automatically loads predefined conditional processors when the application starts, as well as custom configuration items for custom time processors. Provides the ability to find corresponding processors based on processing methods at runtime.

[0091] Report condition processing component: Based on the report condition configuration information, call the corresponding predefined condition processor to convert the query conditions entered by the user into the query conditions corresponding to the data set, apply them to the bound data set and complete the final data query.

[0092] To further illustrate the effects of the embodiments of the present invention, the following experiment was conducted: Based on the report engine system of the present invention, the amount of SQL writing for complex multi-period statistical reports was reduced by 70% through a configuration method based on conditional binding (the traditional method of implementing this report requires repeated writing of similar SQL three times, and date parameters need to be processed in the SQL).

[0093] In typical usage scenarios, the present invention effectively avoids directly defining conditions in the data set, effectively reduces the complexity of data set configuration, improves the reusability of data sets, provides rich processing methods, greatly reduces the difficulty of report configuration, and improves the maintainability and scalability of the system.

[0094] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any modifications, equivalent substitutions and improvements made by any technician familiar with the technical field within the technical scope disclosed by the present invention and within the spirit and principles of the present invention should be covered within the protection scope of the present invention.

Claims

1. A complex report generation method based on conditional binding, characterized in that: The method specifically includes: S1, application startup process: load predefined processors, maintain predefined processor registration information, and implement the maintained registration information to send query report process; the predefined processors include predefined condition processors and custom time processors; the predefined condition processors are used to convert the conditions input by the user into actual conditions according to the processing rules built into the system, and the custom time processors are used to support free configuration parameters; the user binds conditions and configures condition processors through the interface, so that the same SQL can realize queries of different cycles; S2, through the configuration report process, the obtained configuration condition processing parameters are sent to the query report process; S3, through the query report process, receives the registration information, configures the condition processing parameters, and completes the query of the data set SQL; In step S1, a conditional processor is predefined, including the following five cases: Convert the time range in the condition to a new time range: the original condition interval is set to , the offset is defined in the processor and offset units , where the offset unit is is day, month, year; the conditional interval after processing is ; The time points in the conditions are fixedly offset: the original conditions are set to , the offset is defined in the processor and offset units , where the offset unit is is day, month, year; the processed condition is ; Convert the time points in the condition to a time range: Set the original condition to , the range unit defined in the processor is , the conditional interval after processing is As a unit and contains Range ; Pattern extraction: Convert the date in the condition to a string and get the date fragment: The original condition is set to , the format defined in the processor is , the conditions after processing are ,in, Format function for time; Field extraction: Extract the month, year and month in the parameters: The original condition is set to , the processor defines the fields as , the conditions after processing are , where is the function for obtaining the time field value; Convert the time range in the condition to a new time range, including: Calculate the year-on-year and month-on-month data for a certain period of time: Set the original condition interval to , the processor is defined as year-on-year, and the condition interval after processing is ; The processor is defined as a ring, and the condition interval after processing is ,in, 1 year; In step S1, when calculating the year-on-year and other periodic data of a certain time period through a custom time processor, this option is used to control whether the period takes the natural end time: the original condition interval is set to , and the latest time of the month in which the interval is , if the natural end time is taken, the conditional interval after processing is ; Otherwise, the processed condition interval is ; In step S1, the absolute value of the year and month of a time period is set by a custom time processor, and the original condition is set to Year moon Day, the custom year value is , the conditions after processing are Year moon day; calculation When calculating the month-on-month data of the time period by week, specify the day of the week when each week starts. Set the original condition interval to specify At the beginning of each week, Search forward to the first week start date , the conditional interval after processing is .

2. The complex report generation method based on conditional binding according to claim 1 is characterized in that: Perform fixed offset processing on the time point in the condition, including: calculating the same day last year and the same day last month.

3. The complex report generation method based on conditional binding according to claim 1 is characterized in that: Use a custom time processor to offset the time point and configure the offset field and offset.

4. The complex report generation method based on conditional binding according to claim 1 is characterized in that: Convert the time point in the condition into a time range, including calculating the total amount for this month and this year.

5. The complex report generation method based on conditional binding according to claim 4 is characterized in that: Convert a time point into a time range by using a custom time processor and configure the time range that needs to be converted.

6. A complex report generation system based on conditional binding, characterized in that: The system implements the complex report generation method based on conditional binding according to any one of claims 1 to 5, and the system comprises: Report condition configuration component: The report condition configuration component obtains the associated dataset list from the report configuration, and then obtains the data field information from the dataset metadata; basic information configuration supports user-defined condition name, display width, required field validation, form type and value type, and default value information; binding information configuration supports the selection of bound datasets, data dictionaries, and time processor information; Conditional Processor Management Component: Automatically loads predefined conditional processors when the application starts, and loads custom configuration item information of custom time processors; provides search for corresponding processors according to the processing method at runtime; Report condition processing component: Based on the report condition configuration information, call the corresponding predefined condition processor to convert the query conditions entered by the user into the query conditions corresponding to the data set, apply them to the bound data set and complete the final data query.

Images