Method and system for quickly developing data report

Abstract

The invention provides a method for quickly developing a data report. The method comprises the following steps: S1, acquiring data information, wherein the data information is interface data provided by an enterprise; s2, atomic table creation: forming an atomic table by inputting atomic table basic information such as atomic table names, selection hierarchies and databases, inputting storage process statements, selecting parameters and setting Chinese names and types of result fields; s3, data table creation: selecting a hierarchy, an atomic table and an atomic table field, setting a corresponding parameter conversion rule, a parameter value and a field processing rule, and determining a data table name to complete creation of the data table; s4, creating a combination table: selecting a hierarchy, a data table and a data table field, setting a field alias, a parameter conversion rule, a parameter value and a field processing rule, and determining a combination table name to complete creation of the combination table; and S5, visual report making: selecting a data table or a combination table by a user, and completing report page making in a visual report making interface in a dragging manner.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method and system for quickly developing data reports. Background Art

[0002] In business operations, there are a large number of reporting requirements, and report developers need to develop a large number of reports. Generally, there are three main ways to develop enterprise reports: 1. Native development: According to the report requirements, developers write SQL statements, write back-end codes, make front-end pages, and publish them to the report server, which is inefficient, time-consuming and labor-intensive.

[0003] 2. Self-built reporting software: According to the reporting requirements, developers write SQL statements and generate a simple reporting interface through configuration. The format is relatively fixed and has poor flexibility.

[0004] 3. Use commercial reporting software: According to the report requirements, developers write SQL statements and use commercial reporting software to complete report production. They need to learn how to use commercial reporting software, which has a certain learning cost.

[0005] All three of the above methods require developers to write a large number of SQL statements. Due to the different levels of report developers, it is inevitable that there will be problems such as inefficient statistical logic writing, easy errors, and high resource consumption. In addition, most reports have different statistical logics for the same indicators, especially daily reports. Daily reports have high similarity, complex statistical logics for different report indicators, repeated indicators, and frequent updates. Report development often requires repetitive development, which is time-consuming and labor-intensive. Summary of the invention

[0006] The purpose of the present invention is to solve the shortcomings of the prior art and to propose a method and system for quickly developing data reports.

[0007] To achieve the above object, the present invention adopts the following technical solutions: A method for quickly developing a data report includes the following steps: S1: Get data information: The data information is the interface data provided by the enterprise, and the interface data comes from SQL, stored procedures or Excel files; The data information includes a basic level table and a basic parameter table. The basic level table is a summary keyword for a report storage process to merge data with a certain field; the basic parameter table is a parameter of the storage process, the purpose of which is to achieve the distinction and reuse of different storage process parameters; S2: Atom table creation: An atomic table is formed by entering the name of the atomic table, selecting the basic information of the atomic table such as the level and database, entering the stored procedure statement or uploading an Excel table, or entering the SQL statement, selecting the parameters, and setting the Chinese name and type of the result field; The atomic table is a data set of indicators of the same type, which only contains fields of indicators of the same type, and does not contain fields of other indicators. The fields of other indicators create atomic tables separately; S3: Data table creation: Select the level, atomic table, atomic table field, set the corresponding parameter conversion rules, parameter values ​​and field processing rules, and determine the data table name to complete the creation of the data table; The data table is a data set created by atomic tables of the same level. Any number of atomic table indicators are selected at the same level, and are automatically merged according to the level fields to form the required report data. S4: Combined table creation: Select the level, data table, data table field, set the field alias, parameter conversion rule, parameter value and field processing rule, and determine the combined table name to complete the creation of the combined table; The combination table is a data set created by the same data table. Any number of data indicators are selected at the same level. The required report data is automatically merged according to the level fields, or the required report data is generated through a custom statement. S5: Visual report production: The user selects a data table or a combination table and completes the creation of the report page by dragging and dropping in the visual report creation interface.

[0008] Preferably, in step S1, data of the same level in the basic level table can be directly merged without processing, and the basic parameter table divides the parameters of the stored procedure.

[0009] Preferably, in step S2, when there are many indicator fields in the atomic table, it can be split into multiple atomic tables.

[0010] Preferably, in step S3, the data table parameters are created by multiple atomic tables of the same level, and different atomic tables have the same or different parameters. The data table receives parameter rules and implements the parameter request method desired by the report requester through parameter conversion. The parameter conversion rules need to create a basic parameter rule table to achieve reuse and expansion, and the merged parameters are achieved through pre-made script rules such as monthly decrement, daily decrement, and quarterly decrement.

[0011] Preferably, in step S3, result set field processing rules such as custom sorting, adding field sorting numbers, adding custom operation fields, adding data filtering, 10 to yes or no, YN to yes or no, percentage processing, rounding, etc. are set, and a set of result set field processing rules is formulated to achieve reuse and expansion.

[0012] Preferably, in step S4, when the combination table has the same data fields but different request parameters, the problem of the same data fields is solved by setting a data table alias. For some special user requirement fields, complex case-when judgment logic fields are added through custom SQL statements to achieve it.

[0013] A system for quickly developing data reports, including a data information acquisition module, an atomic table creation module, a data table creation module, a combination table creation module, and a visual report production module; The data information acquisition module is used to collect data from outside the system software and input the data into the system; The data information is the interface data provided by the enterprise, and the interface data comes from SQL, stored procedures or Excel files; The data information includes a basic level table and a basic parameter table. The basic level table is a summary keyword for a report storage process to merge data with a certain field; the basic parameter table is a parameter of the storage process, the purpose of which is to achieve the distinction and reuse of different storage process parameters; The atomic table creation module is used to create a data table whose fields are all atomic table fields, and directly use the atomic table to create the data table; An atomic table is formed by entering the name of the atomic table, selecting the basic information of the atomic table such as the level and database, entering the stored procedure statement or uploading an Excel table, or entering the SQL statement, selecting the parameters, and setting the Chinese name and type of the result field; The atomic table is a data set of indicators of the same type, which only contains fields of indicators of the same type, and does not contain fields of other indicators. The fields of other indicators create atomic tables separately; The data table creation module is used to create a data table where some fields are not atomic table fields, to create atomic tables of different levels for some fields and to create a data table using the atomic tables; Select the level, atomic table, atomic table field, set the corresponding parameter conversion rules, parameter values ​​and field processing rules, and determine the data table name to complete the creation of the data table; The data table is a data set created by atomic tables of the same level. Any number of atomic table indicators are selected at the same level, and are automatically merged according to the level fields to form the required report data. The combined table creation module is used for a data table whose fields are not data table fields, creates atomic tables of different levels for all fields, and uses the atomic tables to create a data table; Select the level, data table, data table field, set the field alias, parameter conversion rule, parameter value and field processing rule, and determine the combined table name to complete the creation of the combined table; The combination table is a data set created by the same data table. Any number of data indicators are selected at the same level. The required report data is automatically merged according to the level fields, or the required report data is generated through a custom statement. The visual report making module is used to complete the making of data reports in the visual report making interface; The user selects a data table or a combination table and completes the creation of the report page by dragging and dropping in the visual report creation interface.

[0014] Compared with the prior art, the beneficial effects of the present invention are: it can solve the inefficiency problem of developing a large number of similar and repeated reports in an enterprise. Developers do not need to write a large number of SQL statements, but only need to analyze the indicators required for the report. Reports can be made by selecting different data tables, converting parameters and parameter rules, which saves time and effort, is convenient and fast, and is easy to maintain and update. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 It is a schematic diagram of the process of the present invention. Implementation

[0016] In order to provide a further understanding of the purpose, structure, features, and functions of the present invention, the following detailed description is given in conjunction with the embodiments.

[0017] A method for quickly developing a data report includes the following steps: S1: Get data information: The data information is the interface data provided by the enterprise, which comes from SQL, stored procedures or Excel files; The data information includes the basic level table and the basic parameter table. The basic level table is the summary keyword for the report storage process to merge data with a certain field; the basic parameter table is the parameters of the stored procedure, the purpose of which is to distinguish and reuse parameters of different stored procedures. S2: Atom table creation: An atomic table is formed by entering the name of the atomic table, selecting the basic information of the atomic table such as the level and database, entering the stored procedure statement or uploading an Excel table, or entering the SQL statement, selecting the parameters, and setting the Chinese name and type of the result field; The atomic table is a data set of indicators of the same type. It only contains fields of indicators of the same type, but not fields of other indicators. The fields of other indicators create separate atomic tables. S3: Data table creation: Select the level, atomic table, atomic table field, set the corresponding parameter conversion rules, parameter values ​​and field processing rules, and determine the data table name to complete the creation of the data table; The data table is a data set created by atomic tables of the same level. Any number of atomic table indicators are selected at the same level, and they are automatically merged according to the level fields to form the required report data. S4: Combined table creation: Select the level, data table, data table field, set the field alias, parameter conversion rule, parameter value and field processing rule, and determine the combined table name to complete the creation of the combined table; The combined table is a data set created from the same data table. You can select any number of data indicators at the same level and automatically merge the required report data according to the level fields, or generate the required report data through custom statements. S5: Visual report production: The user selects a data table or a combination table and completes the creation of the report page by dragging and dropping in the visual report creation interface.

[0018] Preferably, in step S1, data of the same level in the basic level table can be directly merged without processing, and the basic parameter table divides the parameters of the stored procedure.

[0019] Preferably, in step S2, when there are many indicator fields in the atomic table, it can be split into multiple atomic tables. Splitting into multiple atomic tables can ensure that the storage process will not connect to irrelevant data tables. There are fewer connected tables and simpler logic, which makes the storage process simple and easy to maintain, reducing database pressure.

[0020] Preferably, in step S3, data table parameters are created by multiple atomic tables of the same level, and different atomic tables have the same or different parameters. The data table receives parameter rules and implements the parameter request method desired by the report requester through parameter conversion. The parameter conversion rules need to create a basic parameter rule table to achieve reuse and expansion, and the parameters are merged through pre-made script rules such as monthly decrement, daily decrement, and quarterly decrement. The pre-made script rules can merge parameters, simplify request parameters, and simplify the query interface.

[0021] Preferably, in step S3, result set field processing rules such as custom sorting, adding field sorting numbers, adding custom operation fields, adding data filtering, 10 to yes or no, YN to yes or no, percentage processing, rounding, etc. are set, and a set of result set field processing rules is formulated to achieve reuse and expansion.

[0022] Preferably, in step S4, when the same data fields exist in the combined table but the request parameters are different, the problem of identical data fields is solved by setting a data table alias to ensure that data will not cause errors due to identical fields. For some special user requirement fields, custom SQL statements are used to add complex case-when judgment logic fields to achieve this.

[0023] A system for quickly developing data reports, including a data information acquisition module, an atomic table creation module, a data table creation module, a combination table creation module, and a visual report production module; The data acquisition module is used to collect data from outside the system software and input the data into the system; The data information is the interface data provided by the enterprise, which comes from SQL, stored procedures or Excel files; The data information includes the basic level table and the basic parameter table. The basic level table is the summary keyword for the report storage process to merge data with a certain field; the basic parameter table is the parameters of the stored procedure, the purpose of which is to distinguish and reuse parameters of different stored procedures. The atomic table creation module is used to create a data table whose fields are all atomic table fields. The atomic table is used directly to create the data table. An atomic table is formed by entering the name of the atomic table, selecting the basic information of the atomic table such as the level and database, entering the stored procedure statement or uploading an Excel table, or entering the SQL statement, selecting the parameters, and setting the Chinese name and type of the result field; The atomic table is a data set of indicators of the same type. It only contains fields of indicators of the same type, but not fields of other indicators. The fields of other indicators create separate atomic tables. The data table creation module is used to create data tables whose fields are not atomic table fields, create atomic tables of different levels for some fields and use atomic tables to create data tables; Select the level, atomic table, atomic table field, set the corresponding parameter conversion rules, parameter values ​​and field processing rules, and determine the data table name to complete the creation of the data table; The data table is a data set created by atomic tables of the same level. Any number of atomic table indicators are selected at the same level, and they are automatically merged according to the level fields to form the required report data. The combined table creation module is used for data tables whose fields are not data table fields. All fields create atomic tables of different levels, and use atomic tables to create data tables. Select the level, data table, data table field, set the field alias, parameter conversion rule, parameter value and field processing rule, and determine the combined table name to complete the creation of the combined table; The combined table is a data set created from the same data table. You can select any number of data indicators at the same level and automatically merge the required report data according to the level fields, or generate the required report data through custom statements. The visual report making module is used to complete the making of data reports in the visual report making interface; The user selects a data table or a combination table and completes the creation of the report page by dragging and dropping in the visual report creation interface.

[0024] In the first embodiment, the fields of the demand report are analyzed. If all the fields are atomic table fields, the atomic table is directly used to create a data table, and the report production is completed in the visual report production interface; if some fields are not atomic table fields, atomic tables of different levels are created for these fields, and then the atomic table is used to create a data table, and the report production is completed in the visual report production interface; if all the fields are not data table fields, atomic tables of different levels are created for all the fields, and then the atomic table is used to create a data table, and the report production is completed in the visual report production interface.

[0025] Embodiment 2: Data information from SQL, stored procedures or Excel files are transmitted into the system through the data information acquisition module; the level is selected and the atomic interface is created through the atomic table creation module; the level is selected, parameter conversion rules are set, result processing rules are set, and a combination of any atomic interfaces of the same level is selected through the data table creation module to create a data interface; parameter conversion rules and result processing rules are set and a combination interface is created through the combination table creation module; the report interface is set in the visual report production module, and the final report is formed by dragging and dropping.

[0026] The present invention has been described by the above-mentioned relevant embodiments, however, the above-mentioned embodiments are only examples for implementing the present invention. It must be pointed out that the disclosed embodiments do not limit the scope of the present invention. On the contrary, changes and modifications made without departing from the spirit and scope of the present invention are all within the scope of patent protection of the present invention.

Claims

1. A method for quickly developing data reports, characterized in that: The following steps are involved: S1: Get data information: The data information is the interface data provided by the enterprise, and the interface data comes from SQL, stored procedures or Excel files; The data information includes a basic level table and a basic parameter table. The basic level table is a summary keyword for a report storage process to merge data with a certain field; the basic parameter table is a parameter of the storage process, the purpose of which is to achieve the distinction and reuse of different storage process parameters; S2: Atomic table creation: An atomic table is formed by entering the name of the atomic table, selecting the basic information of the atomic table such as the level and database, entering the stored procedure statement or uploading an Excel table, or entering the SQL statement, selecting the parameters, and setting the Chinese name and type of the result field; The atomic table is a data set of indicators of the same type, which only contains fields of indicators of the same type, and does not contain fields of other indicators. The fields of other indicators create atomic tables separately; S3: Data table creation: Select the level, atomic table, atomic table field, set the corresponding parameter conversion rules, parameter values ​​and field processing rules, and determine the data table name to complete the creation of the data table; The data table is a data set created by atomic tables of the same level. Any number of atomic table indicators are selected at the same level, and are automatically merged according to the level fields to form the required report data. S4: Combined table creation: Select the level, data table, data table field, set the field alias, parameter conversion rule, parameter value and field processing rule, and determine the combined table name to complete the creation of the combined table; The combination table is a data set created by the same data table. Any number of data indicators are selected at the same level. The required report data is automatically merged according to the level fields, or the required report data is generated through a custom statement. S5: Visual report production: The user selects a data table or a combination table and completes the creation of the report page by dragging and dropping in the visual report creation interface.

2. The method for quickly developing data reports according to claim 1, characterized in that: In step S1, data of the same level in the basic level table can be directly merged without processing, and the basic parameter table divides the parameters of the stored procedure.

3. The method for quickly developing data reports according to claim 1, characterized in that: In step S2, when there are many indicator fields in the atom table, it can be split into multiple atom tables.

4. The method for quickly developing data reports according to claim 1, characterized in that: In step S3, the data table parameters are created by parameters of multiple atomic tables of the same level. Different atomic tables may have the same or different parameters. The data table receives parameter rules and implements the parameter request method desired by the report requester through parameter conversion. The parameter conversion rules need to create a basic parameter rule table to achieve reuse and expansion, and merge parameters through prefabricated script rules such as monthly decrement, daily decrement, and quarterly decrement.

5. The method for quickly developing data reports according to claim 1, characterized in that: In step S3, set custom sorting, add field sorting number, add custom operation field, add data filtering, 10 to yes or no, YN to yes or no, percentage processing, rounding and other result set field processing rules. By formulating a set of result set field processing rules, reuse and expansion can be achieved.

6. The method for quickly developing data reports according to claim 1, characterized in that: In step S4, when the combined table has the same data fields but different request parameters, the problem of the same data fields is solved by setting the data table alias. For some special user-required fields, complex case-when judgment logic fields are added through custom SQL statements.

7. A system for quickly developing data reports, applicable to the method for quickly developing data reports according to claims 1-6, characterized in that: It includes data information acquisition module, atomic table creation module, data table creation module, combination table creation module and visual report making module; The data information acquisition module is used to collect data from outside the system software and input the data into the system; The data information is the interface data provided by the enterprise, and the interface data comes from SQL, stored procedures or Excel files; The data information includes a basic level table and a basic parameter table, wherein the basic level table is a summary keyword for merging data with a certain field in the report storage process; The basic parameter table is the parameters of the stored procedure, and its purpose is to distinguish and reuse the parameters of different stored procedures; The atomic table creation module is used to create a data table whose fields are all atomic table fields, and directly use the atomic table to create the data table; An atomic table is formed by entering the name of the atomic table, selecting the basic information of the atomic table such as the level and database, entering the stored procedure statement or uploading an Excel table, or entering the SQL statement, selecting the parameters, and setting the Chinese name and type of the result field; The atomic table is a data set of indicators of the same type, which only contains fields of indicators of the same type, and does not contain fields of other indicators. The fields of other indicators create atomic tables separately; The data table creation module is used to create a data table where some fields are not atomic table fields, to create atomic tables of different levels for some fields and to create a data table using the atomic tables; Select the level, atomic table, atomic table field, set the corresponding parameter conversion rules, parameter values ​​and field processing rules, and determine the data table name to complete the creation of the data table; The data table is a data set created by atomic tables of the same level. Any number of atomic table indicators are selected at the same level, and are automatically merged according to the level fields to form the required report data. The combined table creation module is used for a data table whose fields are not data table fields, creates atomic tables of different levels for all fields, and uses the atomic tables to create a data table; Select the level, data table, data table field, set the field alias, parameter conversion rule, parameter value and field processing rule, and determine the combined table name to complete the creation of the combined table; The combination table is a data set created by the same data table. Any number of data indicators are selected at the same level. The required report data is automatically merged according to the level fields, or the required report data is generated through a custom statement. The visual report making module is used to complete the making of data reports in the visual report making interface; The user selects a data table or a combination table and completes the creation of the report page by dragging and dropping in the visual report creation interface.

Images