A multi-dimensional financial statement data extraction and report system design method

Abstract

The application relates to a multi-dimension financial statement data extraction and report system design method, which is based on a multi-section financial subject, comprises a data obtaining formula setting process and a report system design process, the data obtaining formula setting process comprises fixed format report formula setting logic and dynamic row report formula setting logic; the report system design process is based on the two kinds of formula setting logic, obtains and displays report data from a database table or view according to rules. Compared with the prior art, the application has the advantages of high efficiency, convenience and the like in satisfying the production of financial statements in different scenes.

Description

Technical Field

[0001] This invention relates to a data processing method, and more particularly to a method for extracting multi-dimensional financial statement data and designing a reporting system. Background Technology

[0002] With the development of business operations and the widespread application of information systems, the amount of business data, especially financial data, is constantly increasing. Therefore, how to effectively process financial data has become a technical problem that needs to be solved. After searching, Chinese Patent Publication No. CN114547170A discloses a method for standardized extraction and modeling of unstructured financial statement data. Specifically, it discloses storing existing Excel-formatted financial statements into a database through a computer program. The purpose is to transform unstructured data from Excel into structured data in a database table. However, it has drawbacks such as the inability to obtain report data from financial systems or third-party systems and automatically generate reports.

[0003] Meanwhile, Chinese Patent Publication No. CN114970473A discloses a self-configurable report retrieval method based on financial user reports. It focuses on the implementation of a web front-end report system and does not define the data extraction formula for report items. Its purpose is to realize online report entry and review functions and to use mobile networks to realize the cloudification of the report system. However, it has problems such as not explaining the implementation method of report data extraction, and the report data still needs to be manually entered.

[0004] Therefore, how to set up data retrieval formulas that meet the requirements of different financial statement formats, and at the same time provide a design method for the corresponding reporting system, has become a technical problem that needs to be solved. Summary of the Invention

[0005] The purpose of this invention is to overcome the shortcomings of the existing technology and provide a multi-dimensional financial statement data extraction and report system design method. This method is efficient and convenient for creating financial statements in different scenarios.

[0006] The objective of this invention can be achieved through the following technical solutions:

[0007] According to one aspect of the present invention, a method for extracting multi-dimensional financial statement data and designing a report system is provided. The method is based on multi-segment financial accounts. The method includes a data retrieval formula setting process and a report system design process. The data retrieval formula setting process includes fixed-format report formula setting logic and dynamic row report formula setting logic. The report system design process, based on the above two formula setting logics, retrieves and displays report data from database tables or views according to rules.

[0008] As a preferred technical solution, the segmented subject includes at least the following subject segments: organization segment, department segment, subject segment, sub-item segment, and inter-item segment.

[0009] As a preferred technical solution, the fixed-format report formula setting logic is to establish a report item description table, a report item mapping table, an intermediate table of data extraction results, and a result table for each report in a relational database, so as to meet the data extraction requirements when a report item corresponds to multiple dimensions.

[0010] As a preferred technical solution, the report item description table is divided into a header table and a row table, which are used to define the information of each item in a specific report;

[0011] The header table of the report item description table includes the following fields: first report code, report name and corresponding result set table, wherein the first report code is used to give different reports a unique code, and the corresponding result set table is used to store the extracted data into the corresponding result set table.

[0012] The report item description table includes the following fields: second report code, first report item code, report item name, unit, and row number. The second report code is associated with the first report code in the header table, and the report item code is a unique code assigned to each report item.

[0013] As a preferred technical solution, the report item mapping table is used to set the data retrieval subject information field corresponding to a certain report item, as well as auxiliary information fields to facilitate data retrieval. The fields include the second report item code, each subject segment field, adjustment coefficient, adjustment number, subject dimension, and the dimension to which the report item belongs. The second report item code is associated with the first report item code of the report item description table row table, and the dimension to which the report item belongs is used to determine the dimension to which this row of report items belongs.

[0014] As a preferred technical solution, the intermediate table of sampling results serves as the data source for specific reports, or as an intermediate transition table from the report item mapping table to each report result table.

[0015] The intermediate table of sampling results includes report code, report period, organization code, report item code, report item value, report item dimension, and report item data type.

[0016] As a preferred technical solution, the result tables for each report are established for different reports. The table name is consistent with the report code in the header table of the report item description table. All result tables include at least the period, organization code, and report item code fields, and the remaining fields are defined according to the column information of the specific report.

[0017] As a preferred technical solution, the dynamic row report formula setting logic is to generate corresponding report view data from the data source table in a relational database through a view. The data source table includes tables in the database or data from a third-party system. The view data includes the following fields: organization code, the period to which the data belongs, and the corresponding column data of the specific report.

[0018] As a preferred technical solution, the reporting system includes:

[0019] The reporting system backend is used to define the mapping relationship between report items and accounts, intermediate tables, result tables, and database configurations related to the reports.

[0020] The front end of the reporting system is used to visually configure report templates, data extraction formulas, and SQL statements for querying data for specified report items, and to query and generate reports based on the configured query statements;

[0021] As a preferred technical solution, the method for obtaining the report data is as follows: define a report template in the report system, wherein the template format is consistent with the actual report, obtain and display the report data from the report result table, intermediate table or view based on the field description information defined in the report field mapping table, and according to the defined global variables;

[0022] The report field mapping table is a field definition table for the source table or view from which the report data is retrieved. The fields include: report code, report item field code of the source table or view, field description, and field data type.

[0023] Compared with the prior art, the present invention has the following advantages:

[0024] 1) This invention is based on a multi-dimensional mapping table of multi-segment financial subjects + intermediate library + result table to realize the setting of data retrieval formulas for different formats of financial statements, and at the same time provides the design method of the corresponding reporting system.

[0025] 2) This invention is based on a relational database and has independent data reading and storage logic; at the same time, in the mapping table of report items and accounts, the financial account segment can be added or removed according to the actual situation, and the data extraction formula can be modified according to the actual situation of different organizations, so as to meet the definition of the data extraction formula under different accounting methods of enterprises;

[0026] At the same time, since different reports have corresponding database result tables, report templates can be quickly defined according to actual report format requirements.

[0027] 3) This invention can acquire data from third-party systems to serve as data for a specific report in this system. Combined with other data, it can meet the enterprise's needs for different report content in a rapid response.

[0028] 4) This invention supports both fixed-format reports and dynamic row reports, meeting the reporting needs of different scenarios;

[0029] 5) This invention supports different display formats for the same data source. Attached Figure Description

[0030] Figure 1 This is a schematic diagram illustrating the data extraction process for multi-dimensional fixed-format financial statements based on multi-segment accounts.

[0031] Figure 2 This is a schematic diagram illustrating the dynamic row report data extraction process.

[0032] Figure 3 A flowchart for designing a reporting system;

[0033] Figure 4 A schematic diagram of the interface for configuring report templates in an actual reporting system;

[0034] Figure 5 Screenshot of the mapping table

[0035] Figure 6 Screenshot of the intermediate table for data extraction

[0036] Figure 7 A schematic diagram of the screen for defining the formula for retrieving numbers. Detailed Implementation

[0037] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0038] This invention utilizes a multi-dimensional mapping table of multi-segment financial accounts, an intermediate library, and a result table to enable the setting of data retrieval formulas for financial statements of different formats. It also provides a design method for the corresponding reporting system, specifically employing the following technologies:

[0039] 1) Set up a report item account mapping table, which allows you to configure the account sampling rules for a certain report item under different dimensions.

[0040] 2) Set up intermediate data extraction tables and report result tables to unify the data retrieval method of the front end of the reporting system.

[0041] 3) Data can be extracted from third-party systems into the result table of this invention, and then database view technology can be used to realize the function of reflecting the data of third-party systems in reports. At the same time, it does not affect the overall implementation logic of the reporting system.

[0042] 4) Set up report field mapping tables and global variables for the report system to unify the configuration of report templates.

[0043] 5) The reporting system of this invention is based on database tables or views, which can meet the needs of one data source and multiple display methods.

[0044] 6) This invention utilizes web front-end development technology for reporting systems, which allows for dynamic configuration of query conditions. The system automatically generates corresponding database query SQL, enabling the data retrieval and display functions of dynamic row-type reports.

[0045] 7) Supports manual creation and import of reports, enabling unified management of reports for companies within the group that have not yet implemented a financial system.

[0046] This invention is a method for setting up financial statement data retrieval formulas and designing a consolidated financial statement system based on multi-segment financial accounts. The multi-segment accounts include at least the following account segments: organization segment, department segment, account segment, sub-item segment, and accounts receivable / payable segment. Depending on the actual business situation, other segments can be added.

[0047] This invention mainly includes a method for setting data retrieval formulas and a method for designing a report system, wherein:

[0048] The data retrieval formula setting logic is divided into fixed-format report formula setting logic and dynamic row report formula setting logic. The fixed-format report formula setting logic establishes a report item description table, a report item mapping table, an intermediate table for data extraction results, and a result table for each report in a relational database. This allows for data extraction when a single report item corresponds to multiple dimensions.

[0049] The report item description table, divided into a header table and row tables, is responsible for defining the information for each item in a specific report. The main fields include:

[0050] Report Item Description Header Table: Report Code (each report has a unique code), Report Name, Corresponding Result Set Table (used to store the extracted data in the corresponding result set table);

[0051] Report Item Description Table: Report Code (linked to the header table), Report Item Code (a unique code is assigned to each report item), Report Item Name, Unit, and Row Number;

[0052] The report item mapping table is used to set the data retrieval account information field corresponding to a certain report item, as well as some auxiliary information fields to facilitate data retrieval. The fields mainly include:

[0053] The report item code (associated with the report item row table), the field of each account segment (if an account has 10 segments, 10 corresponding table fields need to be created, which must include one organization segment), adjustment factor, adjustment amount, account dimension (ending balance, current period debit amount, current period credit amount, etc.), and the dimension to which the report item belongs (used to determine the dimension to which this row of report items belongs).

[0054] The intermediate table for data extraction results can serve as the data source for specific reports, or as an intermediate transition table from the report item mapping table to the result tables of each report. It standardizes the method for initially generating the result set, and its main fields include:

[0055] Report code, report period, organization code, report item code, report item value, report item dimension, and report item data type (used to identify whether the value of this row of report items is amount, quantity, or other type).

[0056] The report results table should be created for different reports. The table name should match the report code in the header table of the report item description table. All results tables should have at least the fields for period, organization code, and report item code. Other fields should be defined according to the column information of the specific report. The results table should store not only current period data but also historical data from the corresponding report. Taking the balance sheet as an example, the results table should also include the following fields: unit of measurement, row number, beginning balance, ending balance, same period last year, increase / decrease amount, and increase / decrease rate.

[0057] In this context, dynamic row reports are generated from a data source table in a relational database using views. The data source table can be a database table or a third-party system data interface; the system retrieves the data and forms our data table. The view must include at least the following fields: organization code, data period, and the corresponding column data for the specific report.

[0058] In this context, the reporting system is designed with data retrieval logic based on the two aforementioned data extraction methods. A reporting system refers to a software system developed using computer programming technology that meets the business needs of financial reporting. Its main function is to retrieve and display report data from database tables or views according to rules. A reporting system consists of a backend and a frontend.

[0059] The backend of the reporting system is mainly used to define the mapping relationship between report items and subjects, intermediate tables, result tables, and other report-related database configurations.

[0060] The front end of the reporting system is mainly used for visually configuring report templates, extracting formulas, and specifying SQL statements for querying data for report items. Based on the configured query statements, it queries and generates reports.

[0061] The method for obtaining report data is as follows: A report template is defined in the reporting system, with the template format consistent with the actual report. Report data is obtained and displayed from the report result table, intermediate table, or view based on the information described in the report result table and intermediate table fields defined in the report field mapping table, and according to predefined global variables, including but not limited to year, month, organization code, report code, previous year, previous month, and corresponding report item code. Wherein:

[0062] The report field mapping table defines the fields from the source tables or views used to retrieve data for reports. The fields primarily include: report code, report item field code from the source table or view, field description, and field data type. Users can visually select data sources at the front end of the reporting system, ultimately generating the query SQL.

[0063] Figure 1 The data extraction process for a multi-dimensional fixed-format financial statement based on multi-segment accounts is as follows:

[0064] Step 1: Based on the configuration information of data retrieval subjects, auxiliary dimensions, and subject dimensions for each organization in the report item mapping table model, the system retrieves the corresponding amount and quantity (if any) data from the financial system, and adjusts and corrects the data according to the adjustment coefficient and adjustment amount.

[0065] Step 2: Based on the field information of the intermediate table model, use the corrected data as the report item value, add auxiliary dimension information, and store it in the intermediate table.

[0066] Step 3: Based on the report code and other information of the intermediate table, as well as the specific report display requirements, store the results in the corresponding tables of each report in the format of "row-report item, column-auxiliary dimension".

[0067] Step 4: As needed, a database view can be generated in the form of "row-auxiliary dimension, column-report item" for data retrieval in the report.

[0068] Figure 2 The process for extracting data from a dynamic row report is as follows:

[0069] Step 1: If a third-party system is involved, use its provided interfaces, views, etc., to obtain and store the data.

[0070] Step 2: Create a database query view based on the actual reporting requirements.

[0071] Step 3: Maintain the view information in the report field mapping table.

[0072] Figure 3 The following is a design method for a reporting system:

[0073] Step 1: The backend of the reporting system generates an intermediate table of data extraction results based on the report item description table and the report subject mapping table, along with key report information such as organization and period.

[0074] Step 2: The reporting system backend automatically generates the result table data based on the intermediate table data and the structure of the report result table.

[0075] Step 3: In the front end of the reporting system, create a report template based on the specific report. If it is a dynamic row report, the template needs to be set to dynamic row type.

[0076] Step 4: In the front end of the reporting system, select the corresponding report name and required fields in the report field mapping table, and use global variables and report item codes to configure the data extraction formula for the corresponding period of the report item. The system will automatically generate the corresponding SQL statement to retrieve current and historical data from the database.

[0077] Step 5: In the front end of the reporting system, each organization can visually modify the account mapping configuration of report items according to its own actual situation.

[0078] Step 6: Save the report in the front end of the reporting system and store it in the corresponding result table in the back end of the reporting system to form the organization's historical data for that period.

[0079] Step 7: The front end of the reporting system supports manual data entry and Excel import for data entry.

[0080] The data extraction logic and implementation method of the entire reporting system of this invention are as follows:

[0081] S1: The data source system for the report can be an ERP system, a financial accounting system, or other third-party systems. The financial accounts in the system can be multi-segmented.

[0082] S2: Data extraction request. The user initiates a data extraction request in the reporting system. The system runs the corresponding data extraction program according to different data scenarios. The data extraction logic of the data extraction program is executed according to the definition in S3.

[0083] S3: Report item mapping and data processing. The account mapping table and auxiliary mapping table define the data retrieval account corresponding to each report item in the report. The data retrieval account can be specific to a company, a certain (category) account, or different dimensions. At the same time, the calculation method (addition, subtraction, multiplication, and division) and calculation constants are defined to complete the conversion from raw account data to specific report data.

[0084] At the same time, data can be obtained from third-party systems and formed into data tables. Combined with other data tables, any data required for a specific report can be generated.

[0085] S4: The data extraction intermediate table and the report result table. The intermediate table stores the data extraction results, recording the data for a specific dimension of a specific report item for a specific company during a specific accounting period. Simultaneously, the system automatically synchronizes the data from the intermediate table to the corresponding report result table.

[0086] At the same time, database views can be used to combine data from third-party systems with data from other tables to form a data source that can be used in reports.

[0087] S5: A reporting system developed based on a web platform, featuring functions such as report template definition, global variable definition, account mapping maintenance, data retrieval formula definition, data auditing, and data modification. Users can modify the account mapping of a specific report item according to their company's actual accounting practices. During template definition, the data source for a report item is visually defined, and can be an intermediate table, report result table, or other views. The storage range and fields for report content can be set. Data can be manually modified, retrieved, stored, and then the report result table data is updated.

[0088] Figure 4 Configure report template interface for actual reporting system: In the figure, the left part is the report template, and the right part is the report cell data retrieval formula setting interface. When retrieving data, the system will generate database SQL according to the configuration information and query and calculate the data.

[0089] S6: Reports in various formats. Since data retrieval formulas can be defined for specific report items, it can meet the display needs of reports in different formats. Specific Implementation

[0091] Based on this invention, a financial reporting system that meets the actual business needs was developed, which can extract relevant data from the Shanghai Electric Power ERP system and generate specific financial reports for each unit.

[0092] 1) Define the report item mapping table: Shanghai Electric Power Finance can have ten segments, namely organization, department, account, sub-item, product, project, customer / supplier, and reserve segment. A general data extraction account mapping relationship is set up, and personalized account mapping relationships are also set up for the special circumstances of certain units. In addition to the account segment, the mapping table also includes report item dimension auxiliary segments, data dimension segments, and other information. Figure 5 The image shows a screenshot of the mapping table. In the image, Report_name is the report code, Segment* is the specific subject segment data, CRP_ACC is the report item code, FZ_TYPE1 is the dimension auxiliary segment, DATA_TYPE is the subject data dimension, and SIGN is the calculation coefficient (e.g., 0.5, which means multiplying the data by 0.5).

[0093] 2) When a user manually triggers a data extraction request and selects an accounting period, the system can find the account mapping information of all report items in the corresponding scenario based on the data extraction scenario. Based on this information, the system retrieves the corresponding account data from the ERP system, multiplies it by a coefficient, and saves it to an intermediate table. At the same time, depending on the specific needs, the system decides whether to generate the corresponding result table data. Figure 6 This is the intermediate table for data extraction. report_code is the report code, period_name is the accounting period, entity is the organization code, line_code is the report item code, line_name is the report item description, customer1 is the auxiliary dimension, vdata1 is the specific data, and currency_code is the amount / quantity (if an item has both amount and quantity, the system will retrieve both data at the same time, and the report front-end will retrieve the value according to the requirements).

[0094] 3) In the front end of the reporting system, define the report template and the data retrieval formula. The system will generate the corresponding database query SQL based on the configured data retrieval formula and retrieve the corresponding data from the intermediate table or the result table. Figure 7 The screen for defining data retrieval formulas allows you to set the formula for a specific cell. You can use the data model on the right to select an intermediate table, report result table, or global variable to set the data source for the cell.

[0095] 4) Issue report templates, select the accounting period, and issue the report templates to the designated organizations.

[0096] 5) When a user clicks "Retrieve Data" on the issued report, the system generates a database query SQL based on the data retrieval logic of each cell, obtains the results, saves them, and stores them in the corresponding report result table.

[0097] This invention can also be implemented in the following ways:

[0098] 1. The data source table is not only a table in the database, but can also be a data interface of a third-party system: the third-party system can also provide database read permissions, or other data acquisition methods.

[0099] 2. The database tables listed are the minimum database tables required to implement the present invention. Other auxiliary tables, such as global variable configuration tables, historical data import tables, and report definition tables, may be added according to the specific system design.

[0100] 3. The report result table is not fixed and depends on the specific number and format of the reports.

[0101] 4. The front end of the reporting system also has functions such as template definition, template distribution (when distributing templates, you can select the organization and accounting period as parameters in global variables), and template withdrawal.

[0102] 5. Intermediate tables are designed for standard financial statements. For other scenarios, other forms of intermediate tables or views can be designed and defined in the report field mapping table.

[0103] The technical terms are explained below:

[0104] Financial accounts (hereinafter referred to as "accounts") are names that further classify accounting elements such as assets, liabilities, owner's equity, revenue, expenses and profits according to their economic content. They can be further refined according to the actual situation of the enterprise, such as "project", "equipment" and "transaction party".

[0105] Report item mapping: The combination of financial accounts corresponding to a certain report item. For example, the "Cash and Cash Equivalents" report item in a company's balance sheet corresponds to the sum of the current period's transactions in the company's bank deposits, cash, bills, etc.

[0106] Subject data dimensions: These refer to a specific statistical caliber in finance, typically including current period transactions, current period debit transactions, current period credit transactions, and ending balance.

[0107] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in the present invention, and these modifications or substitutions should all be covered within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for extracting multi-dimensional financial statement data and designing a reporting system, characterized in that, This method is based on multi-segment financial accounts. The method includes a data retrieval formula setting process and a report system design process. The data retrieval formula setting process includes fixed-format report formula setting logic and dynamic row report formula setting logic. The report system design process is based on the above two formula setting logics to retrieve and display report data from database tables or views according to rules. The logic for setting fixed-format report formulas involves creating a report item description table, a report item mapping table, an intermediate table for data extraction results, and a result table for each report in a relational database. This is used to meet the data extraction requirements when a report item corresponds to multiple dimensions. The report item description table is divided into a header table and a row table, which are used to define the information of each item in a specific report. The report item mapping table is used to set the data retrieval account information field corresponding to a certain report item, as well as auxiliary information fields to facilitate data retrieval. The fields include the second report item code, each account segment field, adjustment coefficient, adjustment number, account dimension, and the dimension to which the report item belongs. The second report item code is associated with the first report item code in the row table of the report item description table. The dimension to which the report item belongs is used to determine the dimension to which this row of report items belongs. The aforementioned intermediate table of sampling results serves as the data source for specific reports, or as an intermediate transition table from the report item mapping table to each report result table. The result tables for each report are established for different reports. The table name is consistent with the report code in the header table of the report item description table. All result tables include at least the period, organization code and report item code fields. The remaining fields are defined according to the column information of the specific report. The dynamic row report formula setting logic generates corresponding report view data from a data source table in a relational database using a view. The data source table includes tables in a database or data from a third-party system. The view data includes the following fields: Organizational code, data period, and corresponding column data for specific reports; The reporting system includes: The reporting system backend is used to define the mapping relationship between report items and accounts, intermediate tables, result tables, and database configurations related to the reports. The front end of the reporting system is used to visually configure report templates, data extraction formulas, and SQL statements for querying data for specified report items, and to query and generate reports based on the configured query statements.

2. The method for multi-dimensional financial statement data extraction and report system design according to claim 1, characterized in that, The multi-segment financial account includes at least the following account segments: organization segment, department segment, account segment, sub-item segment, and accounts receivable / payable segment.

3. The method for multi-dimensional financial statement data extraction and report system design according to claim 1, characterized in that, The header table of the report item description table includes the following fields: first report code, report name and corresponding result set table, wherein the first report code is used to give different reports a unique code, and the corresponding result set table is used to store the extracted data into the corresponding result set table. The report item description table includes the following fields: second report code, first report item code, report item name, unit, and row number. The second report code is associated with the first report code in the header table, and the report item code is a unique code assigned to each report item.

4. The method for multi-dimensional financial statement data extraction and report system design according to claim 1, characterized in that, The intermediate table of sampling results includes report code, report period, organization code, report item code, report item value, report item dimension, and report item data type.

5. The method for multi-dimensional financial statement data extraction and report system design according to claim 1, characterized in that, The method for obtaining the report data is as follows: a report template is defined in the report system, wherein the template format is consistent with the actual report. The report data is obtained and displayed from the report result table, intermediate table or view based on the field description information defined in the report field mapping table and the predefined global variables. The report field mapping table is a field definition table for the source table or view from which the report data is retrieved. The fields include: report code, report item field code of the source table or view, field description, and field data type.

Images