Configurable data report generation method

Abstract

The invention discloses a configurable data report generation method, and relates to the technical field of report configuration. The method comprises the steps of data source configuration and report configuration, wherein a configurable report component performs configuration according to different requirements to generate a user-defined report; by means of the configurable report configuration method and the dynamic report processing method, the problem that due to report rule or requirement change, product codes are frequently modified is solved, the risk that reusing statistical items are missed to be modified is avoided, the report rule can be rapidly configured, and the report processing efficiency is improved. The report result is dynamically obtained, so that the development cycle of software can be shortened, the development cost is reduced, the product competitiveness is improved, the development time is shortened, and the cost is reduced; logic modification does not need code modification and program release, and only configuration modification is needed, so that the demand customization process is faster and more flexible; configuration is interface-based, operation is simple, and use is easy.

Description

Technical Field

[0001] The invention belongs to the technical field of report configuration, and in particular relates to a configurable data report generation method. Background Art

[0002] In software product development, developers usually directly develop code for a certain function and complete report statistics.

[0003] In the prior art, for example, Chinese patent CN104809254A discloses a data query method and device, the method is: pre-write SQL templates corresponding to various statistical items, wherein the query parameters of each SQL template are variables, so when the user queries the statistical results of a certain target statistical item, he only needs to enter the query parameters to find the SQL template corresponding to the target statistical item, and use the query parameters to convert the SQL template into a specific SQL statement, and then use the SQL statement to query the statistical results. When the user uses the method of this application to query data, he only needs to enter the query parameters, which saves the process of writing SQL statements in the prior art, thereby reducing the user's workload and greatly improving the query efficiency. For example, Chinese patent CN111752999A provides a configurable report generation system and method, including: a report data configuration module, which configures the report data. During the configuration process, the data is read from the database by writing SQL statements, and the server data is read by configuring the RESTful API path; a report parsing module, which parses the configured report data; a report drawing and display module, which draws the report according to the preset drawing method based on the parsing results, generates a report configuration template, and displays the report configuration template. Report development is achieved through interface configuration. When modifying a report, only the configuration needs to be modified, and there is no need to republish the system; developers can focus on report business and get rid of the cumbersome report page drawing; shorten the report development cycle, and complete customer customization requirements in a short time; realize report version control for different enterprises; and realize standardized management of reports.

[0004] Based on the above technical references, the solutions and shortcomings of the existing technology can be summarized as follows:

[0005] 1. For new statistical rules, if the code can be reused, modify the code; otherwise, add corresponding code; for the business logic of complex reports, the code is obscure and difficult to understand, and personnel changes make it difficult to quickly understand the original code, which may lead to errors and omissions in corrections and even delays in delivery;

[0006] 2. If the original statistical rules need to be changed, the corresponding code needs to be modified; when the reused statistical item algorithm is updated, multiple report SQLs need to be changed, which has the risk of missing changes and low delivery efficiency;

[0007] 3. The configuration interface is simple to operate and provides SQL verification function. The process from development to launch is long and cannot quickly respond to the ever-changing needs of customers.

[0008] 4. In the online environment, if a report needs to add statistical fields or modify statistical algorithms, the code needs to be modified and the report needs to be shut down before being published;

[0009] 5. The report statistics have low reusability and insufficient flexibility. For similar statistical rules, the original code needs to be modified, or a new statistical method has to be written because the original code involves too many reports.

[0010] With the iteration of software products, customized reports for different projects need to be modified / added according to the report statistics rules. In addition, if the algorithm of a reused statistical item needs to be changed, all SQL associated with this statistical item needs to be updated. This approach has the risk of missing changes. Frequent modification of product codes is inconsistent with the meaning of the product itself, and even brings a lot of workload to software developers, reducing the market competitiveness of products. Summary of the invention

[0011] The purpose of the present invention is to provide a configurable data report generation method, which realizes dynamic reports through configuration, achieves the purpose of no code modification, no downtime, reduces development costs and management costs, and quickly responds to customer needs. It solves the existing problems of frequent modification of product codes due to changes in report rules or requirements and avoids the risk of missing changes in reused statistical items. It can quickly configure report rules and dynamically obtain report results, which can shorten the software development cycle, reduce development costs, and improve product competitiveness.

[0012] In order to solve the above technical problems, the present invention is achieved through the following technical solutions:

[0013] The present invention is a configurable data report generation method, comprising the following steps:

[0014] Data source configuration: Users select one or more data sources to extract data and connect, filter and process data across data sources as required;

[0015] Report configuration: Configurable report components can be configured to generate customized reports according to different requirements; Configurable report components support the configuration of report statistics items, general SQL statements, and actual statistical SQL statements. The same general SQL statements and report statistics items can be reused in different statistical reports.

[0016] Report generation and publishing: After modifying the report configuration, the background automatically matches and replaces the custom tags in the general SQL statement according to the configuration information and assembles the generated report SQL. Use the addMappedStatement method of Configuration in Mybatis to create MappedStatement and execute the assembled report SQL. This method makes the report take effect in real time and does not require downtime for publishing, which improves delivery efficiency.

[0017] Furthermore, the custom tags in the general SQL statement include:

[0018] Report field usage without algorithm <columnlist>, report fields with algorithms and formatting use <formatcolumn>, Grouping field usage <groupcolumn>, grouped and formatted report fields using <asgroupcolumn>, Use the report fields with the algorithm <ascolumnlist>、Use other SQL query conditions <otherwhere>;

[0019] By using custom tags as the basis for assembling report SQL.

[0020] Furthermore, the report SQL execution method includes the following steps:

[0021] The front end passes in the report SQL configuration parameters and report statistical parameters to be executed, and the back end assembles and executes the report SQL according to the parameters passed in by the front end and returns the results;

[0022] When saving the report SQL configuration, the report SQL verification function is provided: the background assembles the configuration information passed in by the front end into the report SQL and executes it. If there is an error in the report SQL statement, a prompt is returned; if not, the configuration is saved.

[0023] Furthermore, the query method for report SQL data includes the following steps:

[0024] The front end passes the configuration ID of the report SQL, and the back end queries the configuration of the report SQL based on the configuration ID, queries the corresponding basic configuration based on the report SQL configuration, and completes the report SQL assembly. After the report SQL assembly is completed, the report SQL is executed;

[0025] If the configuration ID of the report SQL is not found, add a Statement and execute the report SQL;

[0026] After the report SQL is executed, the corresponding type of result is returned based on the query type.

[0027] Furthermore, the report statistics items are configured through a report statistics item table, wherein the field STATISTICS_ALGO represents the statistics item algorithm, the field FORMAT_SQL represents the formatted SQL statement, and the extended fields reserved in the report statistics item table are used when subsequent business needs are expanded.

[0028] Furthermore, the general SQL statement is configured through the report general SQL configuration table, wherein the field SETTING_SCHEMA represents the database library name involved in the report, the field RELATIVE_TABLES represents the data table involved in the report SQL, and the SQL management field SQL_PATTERN is used to save the SQL statement content.

[0029] Furthermore, the actual statistical SQL is configured through the report configuration table structure, wherein the field RELATIVE_SQL_ID represents the ID of the general SQL statement associated with the report, the field STATISTIC_ITEM_CODES represents the statistical item code associated with the report, and multiple statistical item codes are separated by commas; the field GROUP_SQL_PART represents the grouping field associated with the report, and the field WHERE_SQL_PART represents the where condition of the report, and supports mybatis tags.

[0030] Furthermore, the report SQL assembly and execution processing method includes the following steps:

[0031] Process the custom tags in the report SQL configuration accordingly and replace them with the corresponding configuration content;

[0032] After processing, the backend uses the generated report SQL as a method parameter and calls the selectOne / selectList method according to the query result return type;

[0033] MyBatis registers and executes report SQL through addMapperStatement for testing. If the report SQL is executed successfully, it means that the configuration is correct. If the report SQL fails to execute, a failure message is returned.

[0034] Furthermore, during the report SQL assembly process:

[0035] Statistics formatting: by replacing <formatcolumn>Tag generation;

[0036] Statistical Item Algorithm: By replacing <ascolumnlist>Tag generation;

[0037] Where condition: By replacing <otherwhere>Tag generation.

[0038] The present invention has the following beneficial effects:

[0039] The present invention solves the problem of frequently modifying product codes and avoiding the risk of missing changes in reused statistical items due to changes in report rules or requirements through a configurable report configuration method and a dynamic report processing method. By using the present invention, report rules can be quickly configured and report results can be dynamically obtained, which can shorten the software development cycle, reduce development costs, improve product competitiveness, reduce development time, and reduce costs; logic modifications do not require code modifications and program releases, only configuration modifications are required, making the demand customization process faster and more flexible; the configuration interface is simple to operate and easy to use.

[0040] Of course, any product implementing the present invention does not necessarily need to achieve all of the advantages described above at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for describing the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative work.

[0042] Figure 1 This is the SQL execution flow chart of the report of the present invention;

[0043] Figure 2 Assemble and execute the report SQL flow chart for the report SQL of the present invention;

[0044] Figure 3 This is a schematic diagram of the statistical items of the report in the embodiment of the present invention;

[0045] Figure 4 This is a schematic diagram of the structure of the general SQL configuration table for reports in an embodiment of the present invention;

[0046] Figure 5 This is a schematic diagram of the structure of a report configuration table in an embodiment of the present invention;

[0047] Figure 6 This is a schematic diagram of an assembled SQL statement in an embodiment of the present invention. DETAILED DESCRIPTION

[0048] In the following description, specific details such as specific system structures, technologies, etc. are provided for the purpose of illustration rather than limitation, so as to provide a thorough understanding of the embodiments of the present application. However, it should be clear to those skilled in the art that the present application may also be implemented in other embodiments without these specific details. In other cases, detailed descriptions of well-known systems, devices, circuits, and methods are omitted to prevent unnecessary details from obstructing the description of the present application.

[0049] It should be understood that when used in the present specification and the appended claims, the term "comprising" indicates the presence of described features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.

[0050] It should also be understood that the term “and / or” used in the present specification and the appended claims refers to any combination and all possible combinations of one or more of the associated listed items, and includes these combinations.

[0051] As used in this specification and the appended claims, the term "if" may be interpreted as "when" or "uponce" or "in response to determining" or "in response to detecting," depending on the context. Similarly, the phrase "if it is determined" or "if [described condition or event] is detected" may be interpreted as meaning "uponce it is determined" or "in response to determining" or "uponce [described condition or event] is detected" or "in response to detecting [described condition or event]," depending on the context.

[0052] In addition, in the description of the present application specification and the appended claims, the terms "first", "second", "third", etc. are only used to distinguish the descriptions and cannot be understood as indicating or implying relative importance.

[0053] References to "one embodiment" or "some embodiments" etc. described in the specification of this application mean that one or more embodiments of the present application include specific features, structures or characteristics described in conjunction with the embodiment. Therefore, the phrases "in one embodiment", "in some embodiments", "in some other embodiments", "in some other embodiments", etc. that appear in different places in this specification do not necessarily refer to the same embodiment, but mean "one or more but not all embodiments", unless otherwise specifically emphasized in other ways. The terms "including", "comprising", "having" and their variations all mean "including but not limited to", unless otherwise specifically emphasized in other ways.

[0054] Embodiment 1:

[0055] The present invention is a configurable data report generation method, which includes the following steps to realize dynamic reports through configuration, without code modification or downtime, reduce development costs and management costs, and quickly respond to customer needs:

[0056] Data source configuration: the user selects one or more data sources to extract data and connects, filters and processes data across data sources as required; as an embodiment provided by the present invention, preferably, unified data source: configurable reports can extract data from a unified data source, thereby reducing the possibility of data duplication and confusion;

[0057] Report configuration: Configurable report components can be configured to generate custom reports according to different requirements. Among them, configurable report components support the configuration of report statistics items, general SQL statements and actual statistical SQL. The same general SQL statements and report statistics items can be reused in different statistical reports. Traditional reports require developers to customize and develop, while configurable report components can be configured according to different requirements, supporting table statistics items (statistical algorithms, formatting statements), general SQL statements (combining custom tags to complete SQL settings, where condition settings, grouping field settings), and actual statistical SQL configuration. In addition, the same general SQL statements and report statistics items can be reused in different statistical reports, making the customization process faster and more flexible.

[0058] Report generation and publishing: After modifying the report configuration, the background automatically matches and replaces the custom tags in the general SQL statement according to the configuration information and assembles the report SQL. Use the addMappedStatement method of Configuration in Mybatis to create MappedStatement and execute the assembled report SQL. Traditional reports usually need to be manually generated and published during downtime, while configurable reports only need to modify the report configuration. The background automatically matches and replaces the custom tags in the general SQL according to the configuration information, assembles and executes the report SQL. This method makes the report take effect in real time and does not require downtime for publishing, which improves delivery efficiency.

[0059] The report is configurable, and users can configure it according to their own needs and business scenarios, thereby realizing the design and generation of custom reports. At the same time, the addMappedStatement method of Configuration in Mybatis is used to create MappedStatement and execute the assembled report SQL. This application provides two report statistics methods, namely list (selectList) and object (selectOne), and users can choose according to actual needs (such as Examples 2 and 3).

[0060] This method enables the report to take effect in real time and does not require downtime for publication, thereby improving delivery efficiency, solving the problem of frequent modification of product codes due to changes in report rules or requirements, and avoiding the risk of missing changes in reused statistical items. Using the present invention, report rules can be quickly configured and report results can be dynamically obtained, which can shorten the software development cycle, reduce development costs, and improve product competitiveness.

[0061] Embodiment 2:

[0062] The present invention is a configurable data report generation method, and the configurable report design includes:

[0063] 1. Configuration stored in cache: Considering that when configuration changes are infrequent, frequent reading of database configuration when calling report statistics will affect performance, this embodiment stores the configuration in cache;

[0064] 2. The configuration interface is simple, and users can directly operate the configuration through the front-end interface, which is convenient to operate;

[0065] 3. Data source configuration: users can select one or more data sources (data sources represent independent data sources of each business center), such as business data sources such as activity center data and membership system center data, and support cross-data source data connection, filtering and processing according to needs (here is the existing technology, which supports users to associate, filter and process data across data sources according to business needs). For example, to query the activity data and member-related information that a member has participated in, this data involves two data sources: the activity center and the membership system center;

[0066] 4. Report statistical item configuration: Users can add statistical items to the report and configure their algorithms (the specific algorithm needs to be customized by the customer, and this algorithm can be understood as the rules for specific business statistics. For example, to query users aged 20-30, simply enter the statistical item name, algorithm statement, and formatting statement in the configuration interface), so that the report can perform statistics based on the statistical item configuration. For example, to count the number of users aged 20-30: count(if age>20 and age<30,1,0)as userNum;

[0067] 5. General SQL configuration for reports: Users can configure a general SQL according to their needs (for example, for two reports, the data source comes from the same table, and the SQL is roughly the same, but the statistical algorithms are different, then a general SQL can be configured for the two reports) for report statistics in different business scenarios;

[0068] 6. Report Configuration (Report Assembly Configuration): Users can configure the report assembly based on the statistical items, data sources, and general SQL configured in the previous steps. Additionally, since MyBatis itself supports dynamic SQL statements, users can add conditional parameter / grouping rule configurations to the report (grouping fields, conditional parameters, etc. Conditional parameters are conditional statements, and grouping is the grouping field), and generate report SQL by replacing content with tags, enabling the report to be queried and generated according to different conditions.

[0069] As an embodiment provided by the present invention, preferably, if a user needs to count by year and support querying the number of people aged 20 - 30 according to name:

[0070] (1) Add the following statement to the where clause in the report configuration:

[0071] <iftest="param.userName!=null and param.userName!=”">and user_name!=#{param.userName};

[0072] (2) Fill in birthYear in the grouping field item in the report configuration;

[0073] (3) Pass in the corresponding conditional parameter at the front end: userName = Zhang San;

[0074] (4) The background matches the conditions according to the conditional parameters and executes the SQL to filter out the relevant data where userName = Zhang San. The executed SQL is approximately as follows:

[0075] Select birthYear,count(if age>20 and age<30,1,0)as userNum from userwhere 1=1and user_name=‘张三’group by birthYear;

[0076] Meanwhile, if there are other statistical requirements, they can be configured by oneself. For example, if it is also necessary to count the number of people in each gender, only relevant statistical items need to be configured and added to the report configuration.

[0077] Through these configurable components, users can quickly design and generate reports, and at the same time, they can freely combine and optimize according to actual needs, making the design and generation of reports more flexible and efficient.

[0078] The overall functions are as follows:

[0079] I. Report statistical item configuration, general SQL configuration, report configuration saving and querying;

[0080] 1. Save the report statistics item configuration, configure the statistics item algorithm and formatting statements;

[0081] 2. General SQL configuration, edit general SQL. Since SQL query syntax itself has rules, we formulate custom tags according to the rules of query syntax, such as: report fields without algorithms use <columnlist>, report fields with algorithms and formatting use <formatcolumn>, the grouping field uses <groupcolumn>Etc., by using custom tags as the basis for assembling SQL;

[0082] 3. Report configuration, record the statistical items, grouping fields, and associated general SQL required for report statistics;

[0083] 4. When saving the report configuration, the SQL verification function is provided. The background assembles the configuration information passed in by the front end into SQL and executes it. If there is an error in the SQL statement, a prompt is returned. If not, the configuration is saved.

[0084] Embodiment three:

[0085] Based on the first and second embodiments, as an embodiment provided by the present invention, preferably, the method for executing the report SQL and returning the result includes:

[0086] The front end passes in the report SQL configuration parameters and report statistical parameters to be executed, and the back end assembles and executes the report SQL according to the parameters passed in by the front end and returns the results;

[0087] When saving the report SQL configuration, the report SQL verification function is provided: the background assembles the configuration information passed in by the front end into the report SQL and executes it. If there is an error in the report SQL statement, a prompt is returned; if not, the configuration is saved.

[0088] The custom tags in the general SQL statement include:

[0089] Report field usage without algorithm <columnlist>, report fields with algorithms and formatting use <formatcolumn>, Grouping field usage <groupcolumn>, grouped and formatted report fields using <asgroupcolumn>, Use the report fields with the algorithm <ascolumnlist>、Use other SQL query conditions <otherwhere>;

[0090] By using custom tags as the basis for assembling report SQL.

[0091] As an embodiment provided by the present invention, preferably, the custom tags in the general SQL statement include:

[0092] <groupcolumn>: Grouping fields, for example: EMP_CODE, EMP_NAME;

[0093] <asgroupcolumn>: Grouping field formatting, such as groupbyEMP_CODE,EMP_NAME;

[0094] <columnlist>: Original report fields, for example: FIRST_LOGIN_TIME, LOGIN_COUNT;

[0095] <ascolumnlist>:After adding the algorithm, replace the report fields according to the table to which the fields belong, for example: casewhen direction=1 then 1 else 0 end as IN_CALL_COUNT,0 as FIRST_LOGIN_TIME;

[0096] <formatcolumn>: The report formatting field at the top level of SQL, for example: sum(PRESON_COUNT)asPRESON_COUNT,sum(EVALUATE_COUNT)as EVALUATE_COUNT;

[0097] <otherwhere>:Other SQL query conditions, for example:and EVENT_TYPE='1'.

[0098] As an embodiment provided by the present invention, preferably, the report SQL execution process is described as follows:

[0099] 1. Save the configuration:

[0100] like Figure 1 As shown in the figure, after the user completes the basic configuration of the report, he configures the report and submits the form information. The backend reads the relevant basic configuration from the cache and combines it into the report SQL. The report SQL is executed to verify whether there is any problem with the configuration. If there is a problem, an error message is returned. If there is no problem, the configuration is saved.

[0101] 2. Query data:

[0102] like Figure 2 As shown, the front-end passes the report configuration ID, the back-end queries the report configuration based on the ID, queries the corresponding basic configuration based on the report configuration, and completes the report SQL assembly. After the assembly is completed, the report SQL is executed.

[0103] As an embodiment provided by the present invention, preferably, the query method for report SQL data includes the following steps:

[0104] The front end passes the configuration ID of the report SQL, and the back end queries the configuration of the report SQL based on the configuration ID, queries the corresponding basic configuration based on the report SQL configuration, and completes the report SQL assembly. After the report SQL assembly is completed, the report SQL is executed;

[0105] If the configuration ID of the report SQL is not found, add a Statement and execute the report SQL;

[0106] After the report SQL is executed, the corresponding type of result is returned based on the query type.

[0107] Embodiment 4:

[0108] Let's take the example of counting the total number of store leads:

[0109] like Figure 3 As shown, as an embodiment provided by the present invention, preferably, the report statistics item is configured through a report statistics item table, wherein the field STATISTICS_ALGO represents the statistics item algorithm, the field FORMAT_SQL represents the formatted SQL statement, and the extended fields reserved in the report statistics item table are used when subsequent business needs to be expanded.

[0110] like Figure 4 As shown, as an embodiment provided by the present invention, preferably, the general SQL statement is configured through the report general SQL configuration table, wherein the field SETTING_SCHEMA represents the database library name involved in the report, the field RELATIVE_TABLES represents the data table involved in the report SQL, and the SQL management field SQL_PATTERN is used to save the SQL statement content.

[0111] like Figure 5 As shown, as an embodiment provided by the present invention, preferably, the actual statistical SQL is configured through a report configuration table structure, wherein the field RELATIVE_SQL_ID represents the ID of the general SQL statement associated with the report, the field STATISTIC_ITEM_CODES represents the statistical item code associated with the report, and multiple statistical item codes are separated by commas; the field GROUP_SQL_PART represents the grouping field associated with the report, and the field WHERE_SQL_PART represents the where condition of the report, and supports mybatis tags.

[0112] As an embodiment provided by the present invention, preferably, the report SQL assembly and execution processing method includes the following steps:

[0113] Process the custom tags in the report SQL configuration accordingly and replace them with the corresponding configuration content;

[0114] After processing, the backend uses the generated report SQL as a method parameter and calls the selectOne / selectList method according to the query result return type;

[0115] MyBatis registers and executes report SQL through addMapperStatement for testing. If the report SQL is executed successfully, it means that the configuration is correct. If the report SQL fails to execute, a failure message is returned.

[0116] As an embodiment provided by the present invention, preferably, during the report SQL assembly process:

[0117] Statistics formatting: by replacing <formatcolumn>Tag generation;

[0118] Statistical Item Algorithm: By replacing <ascolumnlist>Tag generation;

[0119] Where condition: By replacing <otherwhere>Tag generation.

[0120] As an embodiment provided by the present invention, preferably, the test SQL call sample is:

[0121]

[0122]

[0123] The assembled SQL statement is as follows Figure 6 shown.

[0124] A configurable data report generation method solves the problem of frequently modifying product codes due to changes in report rules or requirements and avoiding the risk of missing changes in reused statistical items through a configurable report configuration method and a dynamic report processing method. By using the present invention, report rules can be quickly configured and report results can be dynamically obtained, which can shorten the software development cycle, reduce development costs, improve product competitiveness, reduce development time, and reduce costs; logic modification does not require code modification and program release, but only requires configuration modification, making the demand customization process faster and more flexible; configuration interface is used, the operation is simple, and it is easy to get started.

[0125] In the description of this specification, the description with reference to the terms "one embodiment", "example", "specific example", etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in any one or more embodiments or examples in a suitable manner.

[0126] The preferred embodiments of the present invention disclosed above are only used to help illustrate the present invention. The preferred embodiments do not describe all the details in detail, nor do they limit the invention to the specific implementation methods described. Obviously, many modifications and changes can be made according to the content of this specification. This specification selects and specifically describes these embodiments in order to better explain the principles and practical applications of the present invention, so that those skilled in the art can understand and use the present invention well. The present invention is limited only by the claims and their full scope and equivalents.< / otherwhere> < / ascolumnlist> < / formatcolumn> < / otherwhere> < / formatcolumn> < / ascolumnlist> < / columnlist> < / asgroupcolumn> < / groupcolumn> < / otherwhere> < / ascolumnlist> < / asgroupcolumn> < / groupcolumn> < / formatcolumn> < / columnlist> < / groupcolumn> < / formatcolumn> < / columnlist> < / otherwhere> < / ascolumnlist> < / formatcolumn> < / otherwhere> < / ascolumnlist> < / asgroupcolumn> < / groupcolumn> < / formatcolumn> < / columnlist>

Claims

1. A configurable data report generation method, characterized in that: The following steps are involved: Data source configuration: Users select one or more data sources to extract data and connect, filter and process data across data sources as required; Report configuration: Configurable report components can be configured to generate customized reports according to different requirements; Configurable report components support the configuration of report statistics items, general SQL statements, and actual statistical SQL statements. The same general SQL statements and report statistics items can be reused in different statistical reports. Report generation and publishing: After modifying the report configuration, the background automatically matches and replaces the custom tags in the general SQL statement according to the configuration information and assembles the generated report SQL. Use the addMappedStatement method of Configuration in Mybatis to create MappedStatement and execute the assembled report SQL.

2. A configurable data report generation method according to claim 1, characterized in that: The custom tags in the general SQL statement include: Report field usage without algorithm <columnlist>, report fields with algorithms and formatting use <formatcolumn>, Grouping field usage <groupcolumn>, grouped and formatted report fields using <asgroupcolumn>, Use the report fields with the algorithm <ascolumnlist>、Use other SQL query conditions <otherwhere> ；< / otherwhere> < / ascolumnlist> < / asgroupcolumn> < / groupcolumn> < / formatcolumn> < / columnlist> By using custom tags as the basis for assembling report SQL.

3. A configurable data report generation method according to claim 1, characterized in that: The SQL statement execution method includes the following steps: The front end passes in the report SQL configuration parameters and report statistical parameters to be executed, and the back end assembles and executes the report SQL according to the parameters passed in by the front end and returns the results; When saving the report SQL configuration, the report SQL verification function is provided: the background assembles the configuration information passed in by the front end into the report SQL and executes it. If there is an error in the report SQL statement, a prompt is returned; if not, the configuration is saved.

4. A configurable data report generation method according to claim 1, characterized in that: The query method for report SQL data includes the following steps: The front end passes the configuration ID of the report SQL, and the back end queries the configuration of the report SQL based on the configuration ID, queries the corresponding basic configuration based on the report SQL configuration, and completes the report SQL assembly. After the report SQL assembly is completed, the report SQL is executed; If the configuration ID of the report SQL is not found, add a Statement and execute the report SQL; After the report SQL is executed, the corresponding type of result is returned based on the query type.

5. A configurable data report generation method according to claim 1, characterized in that: The report statistics items are configured through the report statistics item table, wherein the field STATISTICS_ALGO represents the statistics item algorithm, the field FORMAT_SQL represents the formatted SQL statement, and the extended fields reserved in the report statistics item table are used when subsequent business needs are expanded.

6. A configurable data report generation method according to claim 1, characterized in that: The general SQL statement is configured through the report general SQL configuration table, wherein the field SETTING_SCHEMA represents the database library name involved in the report, the field RELATIVE_TABLES represents the data table involved in the report SQL, and the SQL management field SQL_PATTERN is used to save the SQL statement content.

7. A configurable data report generation method according to claim 1, characterized in that: The actual statistical SQL is configured through the report configuration table structure, wherein the field RELATIVE_SQL_ID represents the ID of the general SQL statement associated with the report, the field STATISTIC_ITEM_CODES represents the statistical item code associated with the report, and multiple statistical item codes are separated by commas; the field GROUP_SQL_PART represents the grouping field associated with the report, and the field WHERE_SQL_PART represents the where condition of the report, and supports mybatis tags.

8. A configurable data report generation method according to claim 1, characterized in that: The report SQL assembly and execution processing method comprises the following steps: Process the custom tags in the report SQL configuration accordingly and replace them with the corresponding configuration content; After processing, the backend uses the generated report SQL as a method parameter and calls the selectOne / selectList method according to the query result return type; MyBatis registers and executes report SQL through addMapperStatement for testing. If the report SQL is executed successfully, it means that the configuration is correct. If the report SQL fails to execute, a failure message is returned.

9. A configurable data report generation method according to claim 8, characterized in that: During the report SQL assembly process: Statistics formatting: by replacing <formatcolumn> Tag generation;< / formatcolumn> Statistical Item Algorithm: By replacing <ascolumnlist> Tag generation;< / ascolumnlist> Where condition: By replacing <otherwhere> Tag generation.< / otherwhere>

Images