CN100478956C - Method and corresponding system for creating and obtaining report forms - Google Patents

Abstract

The invention discloses a method for generating and obtaining reports, which is used to realize generating and obtaining reports without software for generating reports. The method for generating the report is as follows: the database system searches the database for the original data needed to generate the report according to the predefined report business rules; the database system executes the source code of the report template to generate the report, and converts the original Data related data is inserted into the report. The method for obtaining a report is: receiving a request for obtaining a report; obtaining relevant information from the request to generate a report identifier according to a predefined report naming rule, and querying a corresponding report according to the report identifier, wherein the report is provided by the database The system obtains the raw data needed to generate the report according to the predefined report business rules and generates it by executing the source code of the report template; outputs the query result. The invention also discloses a device for generating and acquiring reports.

Description

Method for generating and obtaining report and corresponding system

technical field

The invention relates to the field of statistical machines and databases, in particular to a method for generating and obtaining reports and a corresponding system.

Background technique

Reporting is a common information management strategy. It is a result displayed in tabular form after screening and summarizing information, which can intuitively reflect the information that people care about. In the field of automation applications, reports are usually generated with the help of statistical machines and information management systems.

In the current prior art, an information management system for processing reports usually consists of a database system and report generating software, and the report must be generated by the report generating software. When the report generation software receives external instructions, it obtains data by directly accessing the database to generate reports. When n users query a report with m records, the database needs to return n×m records.

It can be seen that in the prior art, two systems, a database system and a report generating software, are required to generate reports, and reports can only be generated by the report generating software, which increases the development cost of generating reports. Whenever a user needs a report, the report generation software accesses the database and instructs the database system to query the data. If a large number of users need to obtain the same report, the database will do a lot of repetitive work, which will bring a heavy burden to the database. In the case of a large query result set and a large number of users, the performance of the database system is highly dependent on the hardware of the statistical machine, which is not conducive to reducing the cost of system construction. If the hardware of the statistical machine cannot support such requirements, the database system will be paralyzed.

Contents of the invention

This embodiment provides a method for generating and obtaining a report and a corresponding system, which are used to generate and obtain a report without using report generating software.

A method for generating a report, comprising the steps of:

The database system searches the database for the original data needed to generate the report according to the predefined report business rules;

The database system executes the report template source code to generate a report, and inserts the relevant data of the original data into the report during the report generation process, and the report template source code and original data exist in the database system;

A database system stores the generated reports.

A method for obtaining a report, comprising the steps of:

Receive requests to obtain reports;

Obtain relevant information from the request according to the predefined report naming rules to generate a report identifier, and query the corresponding report according to the report identifier, wherein the database system obtains the original data required for generating the report according to the predefined report business rules The data is saved after being generated by executing the source code of the report template, and the source code and original data of the report template exist in the database system;

Output query results.

A system for generating reports, comprising:

Storage module, used to store raw data and report template source code;

The preprocessing module is used to search the storage module for the raw data needed to generate the report according to the predefined report business rules;

The report generation module is used to execute the source code of the report template to generate the report, and insert the relevant data of the original data into the report during the report generation process, and the source code and the original data of the report template exist in the database system;

The report storage module is used for storing reports generated by the database system.

A system for obtaining reports, comprising:

The database system is used to obtain the original data needed to generate the report according to the predefined report business rules and store it in the storage device after the report is generated by executing the source code of the report template, and the source code and original data of the report template exist in the database system;

a storage device for storing the reports generated by the database system;

The user device is used to send a request for obtaining the report and output the result of the request;

The downloading device is used to receive the request sent by the user device, obtain relevant information from the request according to the predefined report naming rules to generate a report identifier, and query the corresponding report in the storage device according to the report identifier.

In this embodiment, the report is generated by the database system, and the report generation software is no longer needed in the report generation process, which saves the cost of generating the report.

In this embodiment, the report is generated by the database system, and the report generation software is no longer needed when the user obtains the report, and the user obtains the report from the report storage module without accessing the database system, which greatly reduces the load on the database system.

Description of drawings

Fig. 1 is the structural diagram of database system in the embodiment of the present invention;

Fig. 2 is the system structural diagram of report generation in the embodiment of the present invention;

Fig. 3 is a flow chart of a method for generating a report according to statistical results after the database system counts raw data in an embodiment of the present invention;

Fig. 4 is the flow chart of the method that database system generates report according to raw data in the embodiment of the present invention;

Fig. 5 is the system structural diagram of obtaining report form in the embodiment of the present invention;

6 is a flowchart of a method for obtaining reports when the user module and the download module belong to the same system in an embodiment of the present invention;

FIG. 7 is a flowchart of a method for obtaining reports when the user module and the download module belong to different systems in the embodiment of the present invention.

Detailed ways

In order to realize that reports can be generated and obtained without report generation software, in this embodiment, the database system generates reports according to locally stored data and source codes of report templates, and further stores the reports in the report storage module, and the user obtains them from the report storage module. The reports that are needed, no longer need to access the database.

Referring to FIG. 1 , the database system for generating reports in this embodiment includes an interface module 101 , a storage module 102 , a preprocessing module 103 , and a report generation module 104 . Wherein, the storage module 102 also includes a report storage module 105 .

The interface module 101 provides data interaction between the database system and external systems. The storage module 102 includes a data table storing raw data, a report file format template data table storing the report template source code, and a data table storing preprocessing results, etc.; wherein, the report template source code is a script code containing report template information, which is easy to modify and Updates and more. The preprocessing module 103 accepts the periodic scheduling of preprocessing jobs in the database system, executes the report preprocessing storage process according to the report business rules, searches the required raw data from the storage module 102, and further performs corresponding statistics on the raw data, and compiles the statistics The results are stored in the corresponding data tables. The report generation module 104 accepts the periodic scheduling of the report generation job in the database system, executes the report generation stored procedure, combines the original data with the source code of the report template, or combines the statistical results in the data table with the source code of the report template to generate a report, and The report is stored in the report storage module 105 , and the report storage module 105 belongs to the storage module 102 .

This embodiment provides a better way. The report generation module 104 stores the generated report in the report storage module 105 through the interface module 101. The report storage module 105 is independent of the database system, as shown in FIG. 2 . Users do not need to access the database system when obtaining reports, which can greatly reduce the load on the database system.

In this embodiment, the telecommunication service is taken as an example, especially the call fee query service is taken as an example for illustration.

Referring to FIG. 3 , in this embodiment, the database system performs statistics on raw data according to the business rules of the report and generates a report as follows:

Step 301: The database system saves the received original data locally.

Step 302: The preprocessing job in the database system starts according to the report business rules and time parameters, triggers the report preprocessing stored procedure, executes the report preprocessing task, and searches the corresponding original data in the database.

For example, according to the business rules of the report, it is necessary to make statistics on the call records of the previous month on the second day of each month and calculate the call charges (abbreviated as call charges). According to the preset time parameters 2 and 0, the database system queries the locally stored call records of each user in the last month at 0 o'clock on the 2nd according to the report business rules.

Step 303: The database system judges whether there is corresponding original data in the database, if yes, proceed to step 305, otherwise proceed to step 304.

Step 304: When the database system judges that there is no corresponding original data in the database, there are multiple processing methods, and step 306 is continued after the processing is completed. This embodiment provides multiple processing methods, which can be like the first method: no operation is performed on the data table, and an empty data table is reserved. As shown in Table 1:

Table 1

User ID username Accounting time Number of calls Total call charges

Or, as in the second method, the contents of the fields related to the original data in the data table are blanked out, and other corresponding data are filled in, as shown in Table 2:

Table 2

User ID username Accounting time Number of calls Total call charges 13812345678 Zhang San 2006-11

Or, the third way is to set the content of the field related to the original data in the data table to 0, and fill in other corresponding data, as shown in Table 3:

table 3

User ID username Accounting time Number of calls Total call charges 13812345678 Zhang San 2006-11 0 0

Step 305: When the database system judges that there is corresponding raw data in the database, it reads the raw data, performs corresponding statistics on the raw data according to the report business rules, stores the statistical results in the corresponding data table, and continues to step 306.

Take a user as an example. The user has 30 call records in the last month. According to the report business rules, the cost of each call is calculated according to the call time of each record and the corresponding cost unit price, and the 30 call charges are summed. , to get the total call charges incurred last month. Then store the statistical results in the corresponding data table. An example of a data table is as follows:

Table 4

User ID username Accounting time Number of calls Total call charges 13812345678 Zhang San 2006-11 30 20

Step 306: The report preprocessing job in the database system starts according to the preset time parameters 2 and 1, triggers the report generation stored procedure to execute the report generation task, and queries the data with statistical results according to the report business rules at 1:00 on the 2nd Table, and read the required data from the data table, the database system executes the source code of the report template, generates the source code of the corresponding file type, forms a report, and inserts the read data into the report during the process of generating the report. The corresponding cycle can be set according to the needs of the report business. In this embodiment, the call service is taken as an example for illustration. Different services may use the same report template source code, depending on the actual situation.

The database system generates corresponding reports according to the data stored in the data table, as shown in Table 1. Since no information is stored in Table 1, the generated report is only a message such as "no data retrieved" or no report is generated; as shown in Table 2 , since the information related to the original data is not stored in Table 2, the database system reads the information related to the user from the data table and combines the source code of the report template to generate a report, as shown in Table 5:

table 5

account number 13812345678 Name Zhang San time November Total call charges

Or the database system generates a report according to Table 3, as shown in Table 6:

Table 6

account number 13812345678 Name Zhang San time November Total call charges 0 yuan

Or the database system generates a report according to Table 4, as shown in Table 7:

Table 7

account number 13812345678 Name Zhang San time November Total call charges 20 yuan

In step 305, the database system may delete existing data in the data table when storing the statistical results in the corresponding data table. Alternatively, in step 306, the database system may delete the read data in the data table when reading the statistical results from the corresponding data table, so as to save storage space.

Step 307: The database system names the report according to user-related information and naming conventions. User-related information includes user ID, destination time, and business type inquired, etc., and the naming convention is: user ID statistical time business type.file type. In the present embodiment, the user identification is a mobile communication number, and the example of the report name is: 13800000000_20061201_fee.xls, which means that there are all telephone charges that 13800000000 users took place in November 2006 in the report; if the report is placed on http://www .aaa.com/, locate the following address to download the report:

http://www.aaa.com/13800000000_20061201_fee.xls

The name of the report can also be such as 13800000000_fee.xls, indicating that the report contains all the charges incurred by 13800000000 users in the last year; such as 13800000000_20061201.xls indicates that the report contains all the charges incurred by 13800000000 users in November 2006 (including Call charges and SMS charges, etc.); for example, 13800000000.xls indicates that all expenses incurred by 13800000000 users in the latest year are stored in the report.

The execution order of step 306 and step 307 can be interchanged.

Step 308: The database system stores the report in the report storage module 105. A preferred manner is that the report storage module 105 is independent of the database system, which can reduce the load on the database system. The database system can also periodically update, modify, and delete the reports stored in the report storage module 105 according to the report business rules.

In order to more specifically describe the method for generating a report provided by this embodiment, the call charge statistics service is taken as an example below for further description.

According to the call charge statistics service, it is stipulated that the database system will count the call charges of the previous month on the 1st of each month. At this time, the user can only know the call charges of the previous month. In order to facilitate the user's query, on the 2nd, the database system needs Make statistics on the calls incurred on the 1st day and generate a report. On the 3rd day, you need to make statistics on the calls incurred on the 1st and 2nd day and generate a report to replace the report generated on the 1st day, and so on. In this way, the user can inquire about the call charges incurred this month.

The above process is illustrated by taking the method that the database system periodically reads and counts the original data according to the report business rules to generate reports as an example. The database system can also directly generate reports after the source code of the original data and report templates according to the report business rules. Referring to Fig. 4, the method flow of directly generating reports according to report business rules and raw data in this embodiment is as follows:

Step 401: The database system saves the received original data locally.

Step 402: The database system regularly searches the local database for corresponding original data according to the report business rules and time parameters.

Step 403: the database system judges whether there is corresponding original data in the database, if yes, proceed to step 405, otherwise proceed to step 404.

Step 404: The database system generates an empty report according to the report business rules and the source code of the locally stored report template. The report does not contain information related to user call records, or the report generated by the database system contains information such as "no data retrieved", continue Step 406, or do not generate a report.

Step 405: The database system obtains the corresponding original data from the database according to the report business rules, and combines the locally stored report template source code to generate the corresponding file type source code to form a report, and proceed to step 406.

For example, the source code of the report template is <td class=xl26 width=85 style=″width:64pt″x:num>%column1</td>, put the data 1000 in the position of %column1 to replace %column1, and generate the file The source code of the type is <td class=xl26 width=85 style=″width:64pt″x:num>1000</td>. Insert multiple pieces of data through loop processing.

The file type can be .xls, .txt and/or .html, etc., and the file type of the generated report can be specified through the configuration file.

Step 406: The database system names the report according to the naming specification, and stores it in the report storage module 105 .

In step 404 and step 405, the database system may store the results obtained by querying the database in a data table, and then generate a report according to the data in the data table. For specific implementation, refer to the descriptions in step 304 and step 305 .

Referring to FIG. 5 , the system for obtaining reports in this embodiment includes a database system 501 , a report storage module 502 , a download module 503 and a user module 504 . This system structure is a better way, which improves the security of the system for obtaining reports. The download module 503 may also belong to the user module 504 .

The database system 501 periodically reads the corresponding original data from the database according to the local predefined report business rules, generates a report according to the original data and the source code of the report template, and stores the report in the report storage module 502 . The report storage module 502 stores the reports generated by the database system 501. A preferred method is that the report storage module 502 and the database system 501 are independent of each other, and the report storage module 502 and the database system 501 can belong to the same equipment entity or different equipment entities . The download module 503 downloads required reports from the report storage module 502 according to the request sent by the user module 504 . The user module 504 provides an operating platform for the user to query the report, and downloads the corresponding report from the report storage module 502 through the download module 503 according to the user's needs, and then outputs the report to the user.

Referring to FIG. 6, the method flow for obtaining reports when the user module 504 and the download module 503 belong to the same system in this embodiment is as follows:

Step 601: The database system 501 periodically counts the corresponding raw data according to the report business rules, stores the statistical results in the corresponding data table, and then obtains the corresponding data from the data table according to the preset time parameters and report business rules. Data, combined with the locally stored report template source code to generate reports.

Step 602: The database system 501 names the report according to a predefined naming convention, and stores it in the report storage module 502.

Step 603: The user requests to obtain the report form through the user module 504 where it is located. For example, the user wishes to know the total amount of calls in November, and the relevant information of the user in the request includes the user's identification such as 13800000000, the destination time such as November 2006 and Service type such as call charges.

Step 604: the user module 504 notifies the local download module 503 that there is a request for obtaining the report, and the download module 503 obtains the required information (13800000000, 2006.11 and fee) from the request according to the predefined naming convention to generate a report identification, such as 13800000000_20061201_fee, according to the The report identifier searches http://www.aaa.com/ of the report storage module 502 for a report name that exactly matches 13800000000_20061201_fee.

When the report storage module 502 belongs to the database system 501 , the download module 503 needs to access the report storage module 502 through the database system 501 .

Step 605: The download module 503 completely matches the generated report identifier with the report identifier in the report storage module 502, and judges whether there is a report whose report identifier is 13800000000_20061201_fee in the report storage module 502, if so, continue to step 607, otherwise proceed to Step 606.

Step 606: The download module 503 obtains the result of the unqueried report. The user module 504 obtains the result through the download module 503, and outputs the result to the user, such as "data not retrieved".

Step 607: The downloading module 503 downloads the report corresponding to the successfully matched report name from the report storage module 502. The user module 504 obtains the report through the download module 503, and outputs it to the user. The obtained report example is as follows:

Table 7

account number 13812345678 Name Zhang San time November Total call charges 20 yuan

When the request only includes the user ID, or further includes the destination time or business type, the report ID generated in step 604 is 13800000000, 13800000000_20061201 or 13800000000_fee. Taking 13800000000_20061201 as an example, in step 605, according to the report identifier 13800000000_20061201, go to the report storage module 502 to find the report name that exactly matches it. If the search fails, continue to search for the report name that partially matches it. When multiple reports are found, for example, find To 13800000000_20061201_fee.xls and 13800000000_20061201_message.xls, the download module 503 downloads these two reports, and the user module 504 outputs the contents in the two reports.

The report identifier generated in step 604 may further include a file type, such as .xls, .txt or .html, etc., specify a file type as required, and download a matching report from the report storage module 502 . If the file type of the downloaded report is not specified, the default file type, such as .xls, is used.

The above method is the case that the download module 503 belongs to the user module 504, so the user module 504 can download the required reports from the report storage module 502 according to the known naming convention. A method for the user to obtain reports when the download module 503 and the user module 504 belong to different systems is introduced below.

Referring to FIG. 7 , the flow of the method for obtaining reports when the user module 504 and the download module 503 belong to different systems in this embodiment is as follows:

Step 701: The database system 501 periodically counts the corresponding raw data according to the report business rules, stores the statistical results in the corresponding data table, and then obtains the corresponding data from the data table according to the preset time parameters and report business rules. Data, combined with the locally stored report template source code to generate reports.

Step 702: The database system 501 names the report according to a predefined naming convention, and stores it in the report storage module 502.

Step 703: The user logs in to the download module 503 through the user module 504 where the user is located, and sends a request to the download module 503 to obtain a statement. For example, the user wishes to know the total amount of his phone charges in November.

Step 704: The download module 503 obtains the required relevant information and the user's login information (including account name and password, etc.) from the request for obtaining the report according to the predefined naming convention, and generates a report identifier, and stores the report according to the report identifier. The corresponding report is searched on the module 502, and the generated report identifier such as 13800000000_20061201_fee.xls is fully or partially matched with the report name of the report in the report storage module 502.

Step 705: The download module 503 judges whether there is a report corresponding to the successfully matched report name stored in the report storage module 502, if yes, proceed to step 707, otherwise proceed to step 706.

Step 706: The download module 503 obtains the result of the report not found, such as "no data retrieved", and returns the result to the user module 504, and continues to step 708.

Step 707: The download module 503 downloads the report corresponding to the successfully matched report name from the report storage module 502 according to the predefined naming specification, and sends the report to the user module 504, and continues to step 708.

Step 708: The user module 504 outputs the obtained report or information such as "no data retrieved" to the user.

In this embodiment, the report is generated by the database system, and the report generation software is no longer needed in the report generation process, which saves the cost of report generation. Moreover, the database system stores the generated reports in the report storage module. The report storage module is independent of the database system. When the user obtains the report, the report generation software is no longer needed, and the user directly obtains the report from the report storage module without accessing the database. system, greatly reducing the load on the database system, greatly improving the system's operational capabilities, and avoiding system interface congestion and system failure caused by sudden large-scale access. At the same time, the database system can also perform operations such as updating or deleting the content in each storage module, which can not only meet the query needs of users, but also save storage space.

This embodiment also provides a download module, the user logs in to the download module and obtains a report through the download module, which enhances the security of the entire system. In addition, this embodiment provides multiple types of report files, and has flexible report customization capabilities, and users can choose according to needs.

Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and equivalent technologies, the present invention also intends to include these modifications and variations.

Claims (18)

1. A method for generating a report, comprising the following steps: The database system searches the database for the original data needed to generate the report according to the predefined report business rules; The database system executes the report template source code to generate a report, and inserts the relevant data of the original data into the report during the report generation process, and the report template source code and original data exist in the database system; A database system stores the generated reports. 2. The method for generating a report according to claim 1, wherein the relevant data of the original data refers to the original data obtained by searching the database; or, The relevant data of the original data refers to the statistical result obtained after the database system performs corresponding statistics on the original data according to the report business rules. 3. The method for generating a report as claimed in claim 1, wherein the database system stores relevant data of the original data in a data table, and reads all data from the data table in the process of generating a report. related data. 4. The method for generating reports as claimed in claim 3, wherein the database system deletes the data in the data table after reading the data table; or, the database system stores the relevant data of the original data in the Delete existing data in the data table. 5. The method for generating a report according to claim 1, wherein the database system generates a report identifier according to relevant information and a naming specification. 6. The method for generating a report according to claim 5, wherein the relevant information includes a user ID. 7. The method for generating a report according to claim 5, wherein the relevant information includes user identification, time or/and business type. 8. The method for generating a report according to claim 1, wherein the database system further updates, modifies or deletes the generated report according to the report business rules. 9. The method for generating reports according to any one of claims 1 to 8, characterized in that, the database system stores the generated reports in a device other than the database system; or, the database system stores the generated Reports are stored inside it. 10. A method for obtaining a report, comprising the following steps: Receive requests to obtain reports; Obtain relevant information from the request according to the predefined report naming rules to generate a report identifier, and query the corresponding report according to the report identifier, wherein the database system obtains the original data required for generating the report according to the predefined report business rules The data is saved after being generated by executing the source code of the report template, and the source code and original data of the report template exist in the database system; Output query results. 11. The method for obtaining reports according to claim 10, wherein the first system receives the request for obtaining reports, and sends the request to the second system when logging in to the second system; The second system generates a report identifier and a query report, and returns the query result to the first system, and the first system outputs the query result. 12. The method for obtaining a report according to claim 11, wherein the relevant information includes a user ID. 13. The method for obtaining a report according to claim 11, wherein the relevant information includes user identification, time and/or business type. 14. The method for obtaining a report according to claim 10, characterized in that the generated report identifier is matched with the existing report identifier, and the report corresponding to the successfully matched report identifier is regarded as the queried report . 15. The method for obtaining a report according to any one of claims 10 to 14, wherein the database system stores the generated report in a device other than the database system; Alternatively, the database system stores the generated reports internally. 16. A system for generating reports, comprising: Storage module, used to store raw data and report template source code; The preprocessing module is used to search the storage module for the raw data needed to generate the report according to the predefined report business rules; The report generation module is used to execute the source code of the report template to generate the report, and insert the relevant data of the original data into the report during the report generation process, and the source code and the original data of the report template exist in the database system; The report storage module is used for storing reports generated by the database system. 17. A system for obtaining reports, comprising: The database system is used to obtain the original data needed to generate the report according to the predefined report business rules and store it in the storage device after the report is generated by executing the source code of the report template, and the source code and original data of the report template exist in the database system; a storage device for storing the reports generated by the database system; The user device is used to send a request for obtaining the report and output the result of the request; The downloading device is used to receive the request sent by the user device, obtain relevant information from the request according to the predefined report naming rules to generate a report identifier, and query the corresponding report in the storage device according to the report identifier. 18. The system for obtaining reports according to claim 17, wherein the database system comprises: Storage module, used to store raw data and report template source code; The preprocessing module is used to search the storage module for the raw data needed to generate the report according to the predefined report business rules; The report generation module is used to execute the source code of the report template to generate the report, and insert the relevant data of the original data into the report during the report generation process.

Images