CN118940734A

CN118940734A - A report logic capture and report generation method, system and application

Info

Publication number: CN118940734A
Application number: CN202410847112.4A
Authority: CN
Inventors: 张吉林; 邹涛
Original assignee: Yuejin Digital Technology Shanghai Co ltd
Current assignee: Yuejin Digital Technology Shanghai Co ltd
Priority date: 2024-06-27
Filing date: 2024-06-27
Publication date: 2024-11-12

Abstract

The invention discloses a report logic grabbing and report generating method which is characterized by comprising the following steps: step one, designing a calculation function for configuring a report, configuring a report template and a data source, and establishing a frame and a data structure of the report; writing report logic by using natural language, analyzing the report logic and configuring an automatic extraction task to acquire corresponding data from a database; and thirdly, generating report data according to logic defined by a user, verifying and adjusting the data by the user through a report filling function, auditing and summarizing after the data is completed, and exporting the report in a required format. The invention also discloses a system for realizing the method and application of the method or the system, and the method or the system has wide application value.

Description

Report logic grabbing and report generating method, system and application

Technical Field

The invention belongs to the field of report supervision and report, and relates to a report logic grabbing and report generating method, a system and application.

Background

In conventional financial statement generation processes, complex formulas and functions often need to be written to capture and calculate specified statement data from raw data.

There are the following limitations to this approach:

the writing of formulas and functions is complicated and easy to make mistakes: the formulas and functions involved in financial statements tend to be very complex, requiring the handling of a large number of data sources and logical relationships. Manually writing these formulas and functions is not only time consuming, but also prone to human error. Once an error occurs, a great deal of time is often spent for checking and correcting, and the working efficiency and the report accuracy are affected.

Support for new report types or requirements is not flexible enough: the report tool in the prior art has fixed functions and is difficult to flexibly adapt to the continuously-changing report requirements. When new report types are required to be generated or new supervision requirements are met, the IT departments are generally required to be relied on for custom development and configuration, so that the development period is long, the response speed is low, the requirements of business departments cannot be met rapidly, and the operation efficiency and the flexibility of enterprises are affected.

Not friendly to non-professional use: the use threshold of the existing report tool is high, and a user needs to have a certain technical background and professional knowledge to correctly write and configure report logic. This is a significant challenge for financial staff and other business sector staff, increasing their workload and limiting the wide use of reporting tools in enterprises.

The efficiency and security of data processing are not sufficient: when a large amount of data is processed, the traditional report generation mode often needs to rely on manual operation, has low efficiency and is easy to miss and error. Meanwhile, the lack of effective safety measures in the process of data transmission and storage has the risk of data leakage, and particularly when sensitive financial data are processed, the risk is more serious, and enterprises can be penalized in legal and regulatory aspects.

Lack of intelligent data analysis and decision support: the traditional report generation mode is mainly focused on data grabbing and displaying, and lacks deep analysis and mining of data, and cannot provide intelligent decision support. When the management layer views the report, data analysis and interpretation are needed additionally, so that the difficulty and time cost of decision making are increased, market change and business requirements cannot be responded in time, and the competitiveness of enterprises is affected.

In summary, the conventional financial report generation method has significant shortcomings in efficiency, flexibility, usability, safety and intelligence, and is difficult to meet the high requirements of modern enterprises on report generation and data analysis, and a more efficient, flexible, safe and intelligent report generation method and system are needed.

Disclosure of Invention

In order to solve the defects existing in the prior art, the invention aims to provide a report logic grabbing and report generating method, a report logic grabbing and report generating system and application. The invention uses natural language to realize the function of report logic grabbing, so as to simplify the financial report generation process, reduce the workload of users and improve the flexibility and maintainability of report logic.

The invention provides a report logic grabbing and report generating method, which comprises the following steps:

Step one, designing a calculation function for configuring a report, configuring a report template and a data source, and establishing a frame and a data structure of the report;

Writing report logic by using natural language, analyzing the report logic and configuring an automatic extraction task to acquire corresponding data from a database;

and thirdly, generating report data according to logic defined by a user, verifying and adjusting the data by the user through a report filling function, auditing and summarizing after the data is completed, and exporting the report in a required format.

Step one, designing a calculation function (such as adding up, taking the end of the month, average of the month and the like) for a report, and realizing a corresponding Java method to generate SQL sentences adapting to different databases; configuring a data table to generate a mapping relation of an entity table and fields thereof as a natural language dictionary item; importing a report template through a template management module and configuring relevant parameters of the report template;

The Java method corresponding to the realization refers to writing Java codes to realize a pre-designed built-in calculation function, so that the built-in calculation function can interact with different database systems, the built-in calculation function is integrated into a report system, a user can conveniently call when defining report logic, and the method is used for processing the report logic;

According to report logic defined by a user, the method is compatible with different database systems by utilizing a dynamic SQL generating technology, and SQL query sentences suitable for the different database systems are automatically generated; the query statement includes paging, join, aggregate functions, and the like.

The built-in computing functions involved in the present invention include, but are not limited to, those shown in Table 1 below:

Table 1 built-in calculation function table

Step two, a user writes the logic of the report in the report logic management module, and uses natural language to write calculation rules and data screening conditions through NLP technology; the user configures the automatic extraction task of the report; after the automatic extraction timing task is triggered, the system circularly traverses the report needing to be extracted, analyzes the logic of each cell through keyword matching, logic relation and context dependency understanding, converts through the mapping relation in the natural language dictionary table, invokes a calculation method and accesses a database;

Vocabulary and grammar models are pre-built for user input to match the mappings of computational logic and database queries.

The construction of the mapping relation comprises the following steps:

step i, establishing a corresponding relation between a table (containing a field) in a physical database and a logic table of a service logic layer; a table in the physical database designates a user name and assigns a label to each field in the table;

step ii, defining calculation logic for the value type field according to the selected value type field;

step iii, identifying non-numeric or date type fields in a database table, setting default screening conditions and allowing a user to dynamically input screening values during operation;

step iv, in the logic table configuration, identifying a field as a screening condition, and defining an optional value range or default screening logic of the field;

and v, automatically generating a series of dictionary items.

Step three, saving the calculation result of the cell in a REPORT DATA table T_REPORT_DATA; the user acquires data in a report form filling function, performs verification and adjustment, submits auditing and confirms, and then enters a state to be summarized/exported; the user with report summarizing authority gathers the data to be summarized by the subordinate mechanism, and then performs verification, submission, auditing and rechecking; and the user with report export authority exports and rechecks the file in the format required by the supervision and management mechanism in the report export function for reporting.

The report logic grabbing and report generating implementation process is applied to the following technical means, and comprises the following steps:

Dynamic SQL generation and database adaptation layer: the Java method implementation mentioned in step one is not limited to basic computing functions, but also relates to dynamic SQL generation techniques. The technique allows for the automatic generation of SQL query statements that accommodate different database systems, including but not limited to complex operations such as paging, join, aggregate functions, etc., based on user-defined reporting logic. By JDBC (Java Database Connectivity) or other ORM (Object-Relational Mapping) frameworks, such as Hibernate or MyBatis, seamless interfacing with databases such as MySQL, oracle, SQL SERVER and the like is ensured, and flexibility and expandability of the system are improved.

Natural Language Processing (NLP) and logic parsing engine: in the second step, advanced natural language processing technology, such as BERT model or transducer architecture, is introduced to realize deep understanding of report logic described by natural language of the user. This includes not only simple keyword matching, but also complex logical relationships and context dependencies, such as "if the revenue of sector a exceeds sector B. By building domain-specific vocabulary and grammar models, the system is able to accurately map user inputs into background computing logic and database queries.

Intelligent verification and data quality assurance: in the third step, a machine learning algorithm is introduced to perform intelligent verification on the filled data, for example, an anomaly detection model based on historical data learning, so that possible input errors or anomaly values can be automatically marked. And combining with a business rule engine, ensuring the accuracy and consistency of data. In addition, the source, conversion process and responsible person of each data are recorded by using the data blood-edge tracking technology, so that transparency and auditing capability are enhanced.

Data stream processing and microservice architecture: the whole report generation flow adopts a micro-service architecture, each step (logic analysis, data capture, calculation processing, audit summarization and the like) is used as independent service, and high-efficiency and loose coupling communication is realized through a message queue (such as Kafka, rabbitMQ) or API GATEWAY. This enables the system to be extended horizontally, coping with high concurrency requests, while simplifying fault isolation and upgrade maintenance.

Visualization and interaction design: the report design stage integrates advanced visualization technology and interactive design concept, and utilizes the front-end libraries of D3.js, ECharts and the like, so that not only is a static report generated, but also an interactive instrument board is provided, and a user can dynamically adjust views and drill down analysis. The design allows for responsive layout to ensure good presentation on different devices. Innovative embodiment

Automated logic generation of fusion NLP: the natural language processing technology is deeply integrated into the report logic configuration, so that the technical threshold of report design is greatly reduced, and non-technicians can define complex report requirements through daily language, which is the capability not possessed by the traditional report tool.

Flexible adaptation across databases with computational function integration: through integration of dynamic SQL generation and Java methods, wide compatibility of different database systems is achieved, meanwhile, a built-in high-performance calculation function library can efficiently process complex financial calculations, such as depreciation calculation, financial ratio analysis and the like, and the report processing speed and accuracy are remarkably improved.

End-to-end intelligent report lifecycle management: from logic definition, data capture, processing and verification to final export, the whole process is highly automatic and intelligent, reduces human intervention, improves the efficiency and quality of report processing, and is particularly suitable for complex report compiling scenes of large enterprises and across-country organizations.

The invention also provides a report processing system for realizing the report logic grabbing and report generating method, which comprises the following steps: the system comprises a report configuration module, a verification management module, a data management module, an automatic report management module and a report processing module;

The report configuration module further comprises a template importing module and a template management module; importing an external report form into a system based on a template, and managing the report form template;

One or more report templates are configured in the report configuration module, and standard Excel reports required by a supervision organization can be imported through the report templates;

the report template comprises report frequency, caliber, currency unit, version number and other information;

the verification management module can carry out verification formula configuration on the report, and is used for in-table verification and inter-table verification;

the check formula comprises a supervision check formula and a custom check formula; the in-table verification means that the data to be verified comes from the same report, and the inter-table verification means that the data to be verified comes from different reports and is verified across tables;

The data management module further comprises a database management module and a data source management module, and is used for configuring database connection and creating mapping relations of fields and condition fields in a data table;

The fields comprise numeric class fields for subsequent report logic calculation; the condition field is used for screening data in report logic;

the automated report management module further comprises a report logic management module and a report extraction management module;

The report logic management module is used for writing and managing the data processing logic and configuring the report logic;

The report form extraction management module is used for setting a report form extraction function, and calculating logic of each cell according to the set report form extraction function and the frequency date of the report form;

The report processing module further comprises a report filling module, a report summarizing module and a report exporting module; the report data processing system is used for checking and adjusting report data, summarizing one or more report data, and generating a corresponding report for uploading or exporting.

The invention also provides a report logic grabbing and report generating method or application of the report processing system in report supervision report, enterprise financial report establishment, business performance analysis and the like.

The invention also provides a hardware system for realizing the report logic grabbing and report generating method, which comprises the following steps: a memory and a processor; the memory has stored thereon a computer program which, when executed by the processor, implements the method described above.

The invention also provides a computer readable storage medium having stored thereon a computer program which, when executed by a processor, implements the above-mentioned method.

The invention has the advantages that compared with the prior art, the invention has the advantages that the efficiency, the flexibility and the accuracy of report processing are obviously improved, and the invention is particularly embodied in:

1. Significantly improving the working efficiency and reducing the error rate: the traditional report generation mode relies on manual data arrangement and calculation, so that the time consumption is long, and errors are easy to occur. According to the invention, the report making period is greatly shortened by automatically grabbing data and applying the preset report logic, and meanwhile, the built-in data checking mechanism can effectively avoid human errors and ensure the accuracy of the data. This not only reduces the workload of financial staff and analysts, but also improves the overall operational efficiency of the organization.

2. Flexibility and customization ability of the report are enhanced: report tools in the prior art are often fixed in function, and are difficult to adapt to the diversified report requirements of enterprises. The invention supports the user to customize the report template and logic, so that an enterprise can flexibly design the report structure and calculation rule according to the actual business requirement, does not need to rely on an IT department for custom development, reduces the cost and accelerates the report innovation iteration process.

3. Enhancing data security and compliance: when sensitive data is processed, the invention integrates advanced data encryption technology and authority management system, ensures the safety of the data in the transmission and storage process, and accords with international data protection regulations such as GDPR, HIPAA and the like. Meanwhile, aiming at the supervision report of a specific industry, the system can automatically follow the corresponding format and the verification specification, ensure that the report output is completely compliant, and reduce risks and fines caused by non-compliance.

4. Decision-making intelligence and data driving are facilitated: by integrating the intelligent analysis function, the invention not only generates the report, but also can perform preliminary data analysis such as trend prediction, anomaly detection and the like, and provides immediate business insight for a management layer. The decision support based on the data is helpful for enterprises to quickly respond to market changes, optimize resource allocation and promote competitiveness.

5. Easy maintenance and upgrade: compared with the traditional report system, the invention adopts a modularized design, and each component is loosely coupled, so that the subsequent system maintenance and function upgrading are convenient. With the change of technical development and business requirements, the system can be quickly adapted, continuously provides the latest functional support, and ensures the long-term return on investment. In summary, the invention solves the limitation of the prior art in report processing through a series of innovation points, brings remarkable benefits to enterprises, and promotes the depth and breadth of digital transformation.

Drawings

In order to more clearly illustrate the embodiments of the invention or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.

FIG. 1 is a schematic diagram of the data processing of the various data layers of the present invention implementing automation.

FIG. 2 is a logical schematic of the invention in one implementation scenario, consisting of natural language. After entering the system, the SQL script is disassembled one by one into languages recognized by a computer, and then the SQL script which can be recognized by a database is generated by combining related business scenes.

FIG. 3 is a flow chart of the present invention for decomposing the report logic grabbing and report generating functions to the system modules.

Detailed Description

The present invention will be described in further detail with reference to the following specific examples and drawings. The procedures, conditions, experimental methods, etc. for carrying out the present invention are common knowledge and common knowledge in the art, except for the following specific references, and the present invention is not particularly limited.

The invention provides a report logic grabbing and report generating method which is characterized by comprising the following steps:

Step one, designing a calculation function for configuring a report, configuring a report template and a data source, and establishing a frame and a data structure of the report; writing report logic by using natural language, analyzing the report logic and configuring an automatic extraction task to acquire corresponding data from a database; and thirdly, generating report data according to logic defined by a user, verifying and adjusting the data by the user through a report filling function, auditing and summarizing after the data is completed, and exporting the report in a required format. The invention also provides a system for realizing the method and application of the method or the system, and the method or the system has wide application value.

Step one, a set of mature platform and environment system are built to support a report logic grabbing and report generating method, and the following detailed description is carried out with reference to fig. 1:

1. After the reporting requirements of the financial institutions are fully understood, a set of complete index library is designed on the whole product to provide a data source for the subsequent logic grabbing; the mode can be further widened and applied to various subsystems under the product, such as AML back money laundering, PBOC, CBRC, EAST, automatic report forms and the like, and the subsystems are managed by a unified authority platform

2. At the data storage layer, support is such as: SQL SERVER, ORACLE, MYSQL, DB2, etc. most of the databases of the current mainstream

3. Data acquisition layer: using a self-lapping ETL tool, the following procedure was followed: data extraction, data filtering, data merging, data distribution, data loading and the like for collecting data of DM layer to service data layer

4. Business data layer: this layer stores the source data supporting different types pushed over the ETL process, including but not limited to the following types: TXT format source data, CSV format source data, EXCEL format source data, XML format source data, and direct web Service

5. Finally, these source data are supplied to the following business systems for application by report logic grabbing and report generating methods

Step two, writing report logic by using natural language, analyzing the report logic and configuring an automatic extraction task to obtain corresponding data from a database, and carrying out the following detailed description with reference to fig. 2:

1. the user writes the logic of the natural language,

2. Importing logic into a system that verifies the logic and format of the natural language

3. If verification fails, a result excel is generated, including reasons and details of failure, so that a user can modify natural language logic conveniently to conduct next logic importing

4. After verification is successful, the system decomposes natural language logic, analyzes the natural language logic piece by piece and converts the natural language logic into sql statements

5. Generating business detail data by manual extraction/automated task extraction to invoke execution of corresponding sql

6. The business detail data can process the data to obtain the final result by realizing related function functions (such as summation, month average and the like) through Java language, and can also be used as detail display of the final result at the later stage

Generating report data according to the logic defined by the user, verifying and adjusting the data by the user through a report filling function, auditing and summarizing the report after the data is completed, exporting the report in a required format, and carrying out the following detailed description with reference to the accompanying figure 3:

1. the acquired report data is concentrated in the state to be complemented by report import or logic grabbing

2. The user stores and checks the report data (corresponding check formulas support the check function), submits, audits, exports and other flow operations, and each flow is finished and then goes to the next flow state

3. After the verification/audit is failed, the failed reasons need to be filled in, and the report data state can be directly returned to the state to be complemented

4. After the export process, the data report of the export state will enter the exported state and be locked and not modified any more,

The data report form state can be returned to the state to be complemented by the unlocking function

The invention also provides a system for realizing the method, which comprises the following steps: the system comprises a report configuration module, a verification management module, a data management module, an automatic report management module and a report processing module;

The check formula comprises a supervision check formula and a custom check formula;

The concrete explanation is as follows:

1. The report configuration module further comprises a template importing module and a template management module, and the template importing module and the template management module are used for realizing functions of template importing, template management and the like.

A. The template is imported, the report template is the basis of the whole set of system, all subsequent functions are based on the specific report template, the function enables a user to import a standard Excel report required by a monitoring mechanism, also enables a user to import a custom Excel report, the user only needs to configure some parameters on a relevant page, some of the parameters have unique verification, the user custom Excel report can be directly used as a template for subsequent filling, and when the user subsequently grabs data, the user can match the report template in the system according to the partial parameters, so that the relevant data can be imported into a proper report template

B. template management, after template import, report templates can be managed, including addition, deletion, modification, inquiry and the like of the report templates, and information such as frequency, caliber, currency unit, version number and the like of the report templates can be configured.

2. And the verification management module comprises a verification configuration function.

The verification configuration can be used for configuring a verification formula required by each report, wherein the verification formula sources comprise a supervision verification formula and a custom verification formula, the supervision verification formula is a verification formula required by a supervision organization, and a system can preset the verification formula of the supervision organization in the system; the user can configure the custom check rules according to the data quality of the user. The check type comprises an intra-table check and an inter-table check, wherein the intra-table check is that data checked by the check formula are all from the same report; the inter-table check is that the check formula is from different reports, and the check is needed to be carried out across tables.

3. And the data management module comprises database management and data source management.

A. And (3) database management, namely configuring database connection, so that a system can access a specific database, and the database management is the basis of subsequent data source management.

B. Data source management, which can create mapping relation of data table used in report logic:

b.1. Mapping from raw data sources to logical tables:

The objective is to establish a correspondence between the tables (containing fields) in the physical database and the "logical tables" of the business logical layer. Logical tables are an abstraction of physical tables that facilitate business understanding and use.

Calculation by selecting a particular table in the physical database at the data source management interface and assigning it an easily understood chinese name. Meanwhile, chinese labels are allocated to each field in the table, so that the service meaning is clear.

B.2. calculating a field mapping:

mesh defining which fields will participate in the calculation (e.g., sum, average, maximum, etc.) in the report.

Calculation the user selects the value type fields in the database table and defines the calculation logic for these fields. For example, a field may be designated as sales and a summary calculation of total sales performed in reporting logic.

B.3. Condition field mapping:

it is an objective to determine which fields are to be used for data screening to support dynamic query and filter functions.

Calculation to identify non-numeric or date type fields in the database table that are typically used as screening conditions (e.g., timeframe, region, product category, etc.). When the user configures these fields, default filter conditions may be set or the user may be allowed to dynamically enter filter values at run-time.

Implementation steps

B.3.1. Database connection configuration-first, the database connection information, including database type, address, port, user name and password, etc., is configured in the system background so that the system can access the actual data source.

B.3.2. And mapping the data table, namely selecting a specific table in the connected database through interface operation, and naming and defining Chinese interpretation of fields for the specific table. This step creates a mapping of physical tables to logical tables.

B.3.3. And setting calculation fields, namely marking which fields are to participate in numerical calculation of the report form on a configuration interface of the logic table, and designating a calculation mode (if the system supports a self-defined formula, the calculation formula also needs to be input).

B.4. Condition field configuration also in the logic table configuration, fields that can be used as screening conditions are identified and their selectable value ranges or default screening logic is defined.

B.5. Generating dictionary items after the configuration is completed, the system automatically generates a series of dictionary items which are used as the basis of natural language processing, so that the system can understand report requests which are made by a user through natural language, such as 'sales in the current year according to regions'.

B.6. And (3) carrying out preliminary verification by the system according to the mapping relation and the configuration information before the report logic is configured by the user, so that the selected field type is ensured to be compatible with the preset calculation or screening operation, and errors such as mathematical operation on non-numerical fields are avoided. Through the series of steps, the data source management not only establishes a bridge between physical data and business logic, but also ensures the validity and accuracy of report logic configuration, and improves the user experience and the stability of the system.

Accessing a specific database through database connection configured in database management, selecting a database table required to be used in report logic later, configuring Chinese names of the database table and Chinese names of fields, and finally generating dictionary items for natural language use by the mapping relations; configuring a field for calculation in a database table for a subsequent report logic module, which is generally a field of a numerical value class; the condition field in the configuration database table for screening data is used for the subsequent report logic module. The configuration of the information is to make preliminary verification on report logic configured by a user later, so that the problem of calculating non-numeric fields is prevented.

4. The automated report management module further comprises a report logic management module and a report extraction management module which are used for realizing the functions of report logic management, report extraction management and the like,

A. Report logic management is carried out by virtue of a template management function:

The template management function plays a role of a core pillar in report logic management, provides a standardized basic framework for report design and data presentation, and realizes high efficiency and flexibility of report logic management. Specifically:

Template management function:

a.1. And (3) standardized design: a series of predefined report layouts, style templates, and data presentation components, such as tables, charts, index cards, etc., are provided. These templates follow the enterprise visual recognition system (VIS), ensuring consistency and expertise of all reports, and reducing design workload.

A.2. Multiplexing logic fragments: allowing the user to create and save commonly used data processing logic, filtering conditions, and computational formulas as templates. Therefore, when a new report is designed, the logic fragments can be directly referenced, similar SQL query or data processing codes do not need to be repeatedly written, and the development efficiency is greatly improved.

A.3. Dynamic parameter configuration: the parameterized query and the dynamic fields are supported to be defined in the templates, so that the same report template can display different data views according to different parameter inputs, and the adaptability and the interactivity of the report are enhanced.

A.4. rights and version control: template management also involves rights allocation, ensuring that only authorized users can edit a particular template while maintaining a historical version of the template, facilitating tracking changes and rollbacks, which is critical to team collaboration and version management of reporting logic.

Report logic management is realized:

Integrate logic and show: template management is achieved by tightly combining data logic (e.g., SQL queries, aggregate function applications) with front-end presentation logic, so that report logic management is not just a background operation of processing data, but is the whole process from data extraction to final presentation throughout the entire report.

Simplify complex logic processing: the complex report logic, such as multi-table joint check, grouping summarization, condition filtering and the like, can be preset through templates, and the user can rapidly realize the deployment of the complex data processing logic by only selecting the corresponding templates and configuring a small number of parameters when creating a new report, thereby reducing the technical threshold of report development.

Promote maintenance efficiency: when the data source structure is changed or the business requirement is adjusted, only the relevant template or logic fragment is required to be modified, and all reports depending on the template are automatically inherited and updated, so that the maintenance efficiency and the response speed of the report system are greatly improved.

Facilitating knowledge sharing: the template becomes a mode for sharing report design experience and best practice in an organization, and new members can quickly start by learning and multiplexing the existing template, so that best practice of knowledge inheritance and report logic management in a team is promoted. In summary, the template management function not only simplifies the design flow of the report, but also realizes the effective management of the report logic by providing a series of preset building modules, logic fragments and configuration options, thereby ensuring the high efficiency, consistency and flexibility of report development

The system provides two modes for configuring report logic, one is logic for writing each cell on a webpage in an online excel mode through SpreadJS plug-ins; the other is to upload the report template uploaded before downloading, edit the logic of each cell after opening through Excel, and upload the logic function to the system after editing. Regardless of the way to edit the logic of the report, the logic edited by the user is checked and prompted.

The process of converting natural language into SQL sentences involves multiple technical fields such as Natural Language Processing (NLP), grammar rule parsing, domain knowledge model construction, etc. Taking the common computing functions in the financial report as, for example, summing (sum), averaging (avg), taking the end of the month (last day ofprevious month), etc., the following briefly describes how to implement this conversion process by using Java, and mainly involves the following technologies and steps:

1. Word segmentation and parsing

Techniques to word and part-of-speech markup of an input natural language using an open-source NLP library, such as Stanford NLP, apache OpenNLP, or spaCy (if a language other than Java, such as Python).

The process of word segmentation of the input sentence, recognition of the nameword phrases, verbs, adjectives, etc., which facilitate understanding of sentence structure and intent at subsequent steps.

2. Grammar parsing and intent recognition

Technology of recognizing principal components (subject, predicate, object, etc.) of a sentence and their relationships by using semantic parsing technology such as dependency syntax analysis.

A process of identifying the operation the user wants to perform (e.g., sum up, average) and the object it acts on (e.g., "sales", "balance at last month") by analyzing the dependency between words.

3. Domain-specific language conversion

Technology-building domain-specific vocabulary and conversion rules. For example, for the financial statement field, a mapping table is maintained that maps, for example, "sales" to specific tables and field names in the database.

And (3) searching and replacing the key words in the vocabulary into table names and column names in the database according to the result of intention recognition. Meanwhile, according to the identified operation (function), a corresponding SQL keyword or function is constructed.

SQL statement generation

Techniques to generate SQL statements using string concatenation or a template engine (e.g., apache vector, freemarker).

And (3) a process of splicing or filling templates according to SQL grammar specifications to generate complete SQL query sentences according to the results of the steps. For example, for "calculate total sales over the month," SQL might be generated as:

SELECT SUM(sales_amount)FROM sales_table WHERE sales_date BETWEEN'2023-02-01'AND'2023-02-28'；

5. Security and validity check

Techniques to use parameterized queries or pre-compiled statements to prevent SQL injection attacks and to check the grammar correctness of SQL statements before and after SQL is generated.

Security scrutiny of the generated SQL, ensuring that no user input is directly inserted into the SQL string. Meanwhile, the API provided by the database driver can be used for precompiled and parameter binding, so that the safety is improved.

In a particular implementation, a user may write logic in, for example, the following modes:

logic case one: summing (subject data sheet. Current Renminbi balance) |data source is equal to GL; RK subject equals 40090501

The sum () in the logic belongs to a preset calculation function; the content behind the vertical line is the screening condition; the subject data table is the Chinese mapping name of the T_ODS_BASE_GL table configured by the user in the data source management, and the current RMB balance is the Chinese mapping name of the CURR_RMB_BAL field of the table; the DATA SOURCE is the Chinese mapping name of the DATA_SOURCE field of the table; the RK subject is the Chinese mapped name of the RK_ GLCODE field of the table.

The strip logic ultimately generates a select sum (GL. Curr_rmb_bal) from t_ods_base_ GL GL WHERE DATA _source= 'GL' and rk_ GLCODE = '40090501', and the final result is obtained from the SQL statement into the database.

Logic case two: summing (public customer information list, credit line) data source is equal to ONLINE

<+>

Summing (private customer information list, credit line) data source is equal to ONLINE

The sum () in the logic belongs to a preset calculation function; the content behind the vertical line is the screening condition; the results of the two-piece logic can be added; the public client information table is the Chinese mapping name of the T_ODS_BASE_CUST_C table configured by a user in data source management; the CREDIT is the Chinese mapping name of the CREDIT_LIMIT field of the table; the DATA SOURCE is the Chinese mapping name of the DATA_SOURCE field of the table; the private client information table is a Chinese mapping name of a T_ODS_BASE_CUST_P table configured by a user in data source management; the CREDIT is the Chinese mapping name of the CREDIT_LIMIT field of the table; the DATA SOURCE is the Chinese mapping name of the DATA_SOURCE field of the table.

The logic ultimately generates two SQL statements

Select sum(CUST.CREDIT_LIMIT)from T_ODS_BASE_CUST_C CUST where DATA_SOURCE＝‘ONLINE’；

Select sum(CUST.CREDIT_LIMIT)from T_ODS_BASE_CUST_P CUST where DATA_SOURCE＝‘ONLINE’；

And finally, adding the results of the two SQL sentences.

B. The report is extracted and managed, the report with configured logic can be configured and automatically extracted in the function, the operation of extracting the data of each report cell is executed, the sql generated by the logic can be executed to acquire detail data, the function is realized by java codes according to the function (such as sum function), the detail data is calculated, the automatic extraction timing task can be configured according to the frequency of the report, and the task after the configuration is completed also supports manual execution. When the timing task is automatically executed, logic of each cell is calculated according to the frequency date of the report.

5. The report processing module comprises report filling, report summarizing and report exporting functions.

A. report forms are filled, data can enter the function through an automatic extraction or manual extraction function in report form extraction, a user performs checksum adjustment on the data on the page, the data is submitted to an audit/review post after confirmation, and the report form data can be summarized, generated and reported after the audit/review post is confirmed.

B. the report summarization is carried out by the user with the function, the data which is checked and passed by the subordinate mechanism can be summarized, and the function is generally endowed to the user of the top-level mechanism, so that report data which is finally required to be reported to the supervision mechanism is generated.

C. and exporting the report, wherein the function generates the report data into a file with a specific format according to the requirements of different supervision authorities, and the file is reported to the supervision authorities after being downloaded by a user.

The overall flow of the report logic grabbing and report generating in the invention is as follows:

1. The design and configuration meet the built-in calculation functions used by the financial report, such as adding, taking the last month, average month and the like, and implementing a specific java method according to the functions of the functions, wherein the functions correspond to the java method, input fields and screening conditions to be calculated, and output SQL sentences which can be adapted to different databases according to the entity table and the mapping relation of the fields.

2. The user configures the data table in the data source management, and generates the mapping relation of the entity table and the fields thereof as natural language dictionary items after the configuration is completed; and importing the template through a template management module and configuring relevant parameters of the template.

3. Logic for writing a report by a user in report logic management, such as writing a sum (subject data table. Current Renminbi balance) |data source= 'GL' in a C6 cell (cash of Renminbi) in an asset liability table (G0100); RK subject= '10010000'; the present foreign currency= '0' ", D6 cell (cash of foreign currency folding renminbi) writes" sum (subject data table. Current renminbi balance) |data source= 'GL'; RK subject= '10010000'; this foreign currency= '1' ", the logic of the other cells and so on.

4. And the user configures an automatic extraction task of the report in report extraction management.

The specific implementation of automatic extraction relates to a plurality of technical links, mainly comprising task scheduling, SQL execution, data processing and aggregation, and result storage and display. The following is one summary implementation scheme based on the Java technology stack:

4.1. Task scheduling

Technology selection using a mature task scheduling framework such as Quartz, spring Scheduler or third party scheduling services such as Apache Airflow, XXL-JOB, etc.

Procedure of creating a timed task, setting a trigger according to the update frequency of the report (e.g., daily, weekly, monthly). The task scheduler may automatically trigger the data extraction process at a predetermined time.

SQL execution and data retrieval

Techniques to execute SQL statements using JDBC (Java Database Connectivity) or other ORM (Object-Relational Mapping) frameworks such as Hibernate, myBatis.

And (3) a process of packaging SQL sentences generated in the logic into executable code blocks and executing SQL to acquire data through database connection. For the two SQL strips in this example, the result sets are executed and obtained separately.

4.3. Data processing and aggregation

Techniques collection frameworks in Java standard libraries, streams (STREAM API), or third party data processing libraries such as Apache Commons Math.

Processing the result set returned by each SQL query, and if the SQL has completed the aggregation operation (such as SUM), directly reading the aggregation value; if detail data is returned, java code is used to implement the necessary aggregation logic (e.g., traverse the result set, accumulate the value of the CREDIT_LIMIT field).

4.4. Results storage and display

Techniques storage aspects may use a database, file system, or in-memory database (e.g., redis) to temporarily store the results of the computation; the presentation layer may be a Web application, a reporting tool (e.g., jasperReports, ECharts integration).

The process of storing the processed data for subsequent quick access or presentation. If the report system is designed to be displayed in real time, the calculation result can be directly transmitted to the front-end display layer; if the report is an offline report, the result is saved, and the result is read from the storage and displayed when the report is requested by a user.

4.5. Error handling and logging

Techniques to capture and process errors that may occur during execution using the exception handling mechanism of Java, record critical information and error logs in conjunction with a log framework (e.g., log4j, slf4j+ logback).

Procedure to ensure exception capture logic in key links such as SQL execution, data processing, etc., and to record enough log information to facilitate problem troubleshooting.

5. After the automatic extraction of the timing task is triggered, traversing the report to be extracted circularly, in a single report, traversing each cell circularly, dividing the logic in the cell into two sections through a vertical line, wherein the first section is a behavior, the second section is a data screening condition, taking the logic of a C6 cell of the G01 report as an example, after the division, the first section is a sum (subject data table, current RMB balance), and the second section is a data source= 'GL'; RK subject= '10010000'; the foreign currency= '0', then the contents of the first segment and the second segment are mapped and converted respectively through the mapping relation in the natural language dictionary table, after conversion, the first segment is fun_sum (t_ods_base_gl. Curr_rmb_bal), and the second segment is t_ods_base_gl. Data_source= 'GL'; t_ods_base_gl. Rk_ GLCODE = '10010000'; t_ods_base_gl. Bkg_ind= '0', invoking the fun_sum method by java reflection, passing the computed fields and filtering conditions into the method, the return will eventually output one "select sum(GL.CURR_RMB_BAL)from T_ODS_BASE_GL GL where GL.DATA_SOURCE＝'GL'ANDGL.RK_GLCODE＝'10010000'AND BKG_IND＝'0'".

6. Invoking a calculation method, accessing a database, and obtaining "select sum(GL.CURR_RMB_BAL)from T_ODS_BASE_GL GL where GL.DATA_SOURCE＝'GL'AND GL.RK_GLCODE＝'10010000'AND BKG_IND＝'0'" results

7. Finally, the results of all the cells are stored in a REPORT DATA table T_REPORT_DATA

8. In the report form filling function, a user acquires data from a report form data table and renders a page, finally, the data of the asset liability table is displayed in an online Excel format, the user verifies the data (invokes a supervision and check formula and a custom check formula configured in the system) and submits the data to a review/check post after adjustment, the review/check post confirms the data, and the data enters a state to be summarized/exported.

9. And the user with report summarizing authority summarizes the data to be summarized by the subordinate mechanism, and then checks, submits, audits and rechecks the data.

10. The user with report export authority can export and report the file in the format required by the recheck supervision mechanism in the report export function.

Example 1

Automated financial statement generation and analysis application scenarios:

a nationwide company needs to regularly compile and combine financial reports, relates to a plurality of sub-companies and different currencies, and hopes to improve report compiling efficiency and accuracy through automated logic. The implementation process comprises the following steps:

1. data acquisition and standardization: financial data, including but not limited to revenue, cost, assets, liabilities, etc., is automatically extracted from each sub-company ERP system, ensuring data format consistency through data cleansing and normalization processes.

2. Automated report preparation: automated logic is designed to automatically complete the compilation of liability statement, profit statement, cash flow statement based on international financial reporting criteria (IFRS) or local accounting criteria. The system can process the conversion of multiple currencies and automatically adjust exchange rate difference.

3. Key index analysis: at the same time of report generation, automatically calculating financial ratio, such as Key Performance Indexes (KPIs) including gross interest rate, net profit rate, asset liability rate, flow rate and the like, and carrying out trend analysis and comparison in the same industry.

4. Visual report and early warning: an interactive financial report is generated containing charts and notes, visually demonstrating financial status and business outcome. And setting a threshold value, automatically sending out early warning to abnormal data or potential risk points, and assisting a management layer in fast decision-making.

Example 2

Automatically compiling and combining financial statement background:

one across-the-country company needs to regularly compile consolidated financial statements involving multiple sub-companies, different currencies and meeting-count criteria, and the manual process is time consuming and prone to error. The implementation process comprises the following steps:

1. design configuration stage (step one):

Report template configuration: the design team creates a standardized consolidated financial statement template that includes the liability statement, profit statement, and cash flow statement. The template design considers accounting subject differences of different subsidiaries and conversion requirements of IFRS, GAAP and other accounting criteria.

Calculate function design: the development team writes Java methods, achieves functions such as currency conversion, merging offset, financial ratio calculation and the like, ensures that the calculation logics are compatible with various databases such as Oracle, mySQL and the like, and generates corresponding SQL sentences.

Data source configuration: and configuring database connection of each subsidiary, and establishing entity table mapping, such as mapping 'Sales' into 'Sales income', as natural language dictionary items, so as to facilitate subsequent logic writing.

2. Report logic writing and data extraction (step two):

logic write: the financial analyst uses natural language definitions in the report logic management module to calculate rules such as "consolidate sales revenue for all subsidiaries and convert to dollars at the current exchange rate".

Automatic extraction settings: setting an automatic extraction task, and regularly triggering a system to extract required data from databases of all the subsidiaries, wherein the system automatically executes a corresponding calculation method and database inquiry by analyzing natural language logic.

3. Data generation and verification (step three):

Calculation and storage: and the system automatically fills the corresponding cells in the REPORT template according to the logic calculation result, and stores the calculation result in the T_REPORT_DATA table.

Data verification and adjustment: the staff of the financial department logs in the report form reporting system to check the automatically generated data, adjust the data in question, and submit the data to be checked after finishing the data verification.

Audit and summary: and finally, collecting the data of all the subsidiaries by a manager with collecting authority through a multi-layer approval process, and carrying out final check and rechecking.

Export and reporting: after rechecking, the report is exported into a format (such as Excel and PDF) appointed by a supervision organization, and contains necessary notes and notes, so that the reporting process is completed.

Example 3

Profit center performance analysis reporting background:

Periodic analysis of the performance of different profit centers is required inside the enterprise to optimize resource allocation and decision support. The implementation process comprises the following steps:

1. configuration and logic definition:

Configuring a profit center-related data sheet, and designing a calculation function to calculate key indexes such as gross profit, operating profit margin and the like.

Analysis logic is defined by natural language, such as "calculate gross profit for each profit center, month revenue minus cost, and compare the rate of change with the previous month".

2. Data grabbing and processing:

Setting an automatic task, automatically capturing data such as sales, cost and the like by a system, and calling a corresponding calculation function to perform data analysis by utilizing natural language analysis logic.

3. Report generation and decision support:

The generated report not only displays the performance data of each profit center, but also includes trend analysis and abnormal point marking, and helps a management layer to quickly identify fields with excellent performance or needing improvement.

The user can adjust the data in the report filling module, such as correcting error records, and then go through the auditing and summarizing processes as well, so as to ensure the accuracy of the data.

The final report is exported in PPT or PDF format, which is convenient for sharing discussion in management conference, and directly supports strategic decision making.

The above scenes show how to apply the invention to the actual financial statement processing, remarkably improve the efficiency and accuracy of statement preparation, and simultaneously enhance the depth and breadth of data analysis.

The protection of the present invention is not limited to the above embodiments. Variations and advantages that would occur to one skilled in the art are included within the invention without departing from the spirit and scope of the inventive concept, and the scope of the invention is defined by the appended claims.

Claims

1. A report logic capture and report generation method, characterized in that the method comprises the following steps:

Step 1: Design and configure the calculation function for the report, configure the report template and data source, and establish the report framework and data structure;

Step 2: Use natural language to write report logic, parse the report logic and configure automatic extraction tasks to obtain corresponding data from the database;

Step 3: Generate report data based on user-defined logic. Users can verify and adjust data through the report filling function. After completion, they can review and summarize the data and export the report in the required format.

2. The method as claimed in claim 1 is characterized in that in step one, a built-in calculation function for the report is designed, the corresponding Java method is implemented, and an SQL statement adapted to different databases is generated; the data table is configured to generate a mapping relationship between the entity table and its fields as a natural language dictionary item; the report template is imported and the relevant parameters of the report template are configured.

3. The method according to claim 2, characterized in that the implementation of the corresponding Java method refers to writing Java code to implement the pre-designed built-in calculation function, ensuring that the built-in calculation function can interact with different database systems, integrating the built-in calculation function into the report system, and facilitating the user to call it when defining the report logic for processing the report logic;

According to the report logic defined by the user, dynamic SQL generation technology is used to be compatible with different database systems and automatically generate SQL query statements that are suitable for different database systems; the query statements include paging, joins, and aggregation functions.

4. The method as claimed in claim 1 is characterized in that, in step 2, the user writes the logic of the report in the report logic management module, and uses natural language to write the calculation rules and data screening conditions; the user configures the automatic extraction task of the report; after the automatic extraction timed task is triggered, the system loops through the reports to be extracted, parses the logic of each cell through keyword matching and logical relationship and context dependency understanding, and converts through the mapping relationship in the natural language dictionary table, calls the calculation method and accesses the database;

Pre-built vocabulary and grammar models for mapping user input to calculation logic and database queries.

5. The method as claimed in claim 1 is characterized in that, in step three, the calculation results of the cells are saved in the report data table T_REPORT_DATA; the user obtains the data in the report filling function and verifies and adjusts it. After submitting it for review and confirmation, the data enters the state of waiting to be summarized/exported; the user with report summary authority summarizes the data to be summarized by the subordinate institutions, and then verifies, submits, reviews and verifies it; the user with report export authority exports the file in the format required by the review regulatory agency in the report export function for reporting.

6. A system for implementing the method according to any one of claims 1 to 5, characterized in that the system comprises: a report configuration module, a verification management module, a data management module, an automated report management module, and a report processing module;

The report configuration module further includes a template import module and a template management module; external reports are imported into the system based on the template, and the report templates are managed;

The report configuration module is configured with one or more report templates, and the standard Excel reports required by the regulatory agency and the user-defined Excel reports are imported through the report templates;

The verification management module can configure verification formulas for reports, which are used for verification within the report and verification between reports.

The verification formula includes a regulatory verification formula and a custom verification formula;

The data management module further includes a database management module and a data source management module, which are used to configure database connections and create mapping relationships between fields and condition fields in the data table;

The automated report management module further includes a report logic management module and a report extraction management module;

The report logic management module is used to write and manage data processing logic and configure report logic;

The report extraction management module is used to set the report extraction function, and calculate the logic of each cell according to the frequency date of the report according to the set report extraction function;

The report processing module further includes a report filling module, a report summary module, and a report export module; which are used to verify and adjust report data, summarize one or more report data, and generate corresponding reports for uploading or exporting.

7. The system according to claim 6, characterized in that, in the report configuration module, the report template contains report frequency, caliber, currency unit, and version number information;

In the verification management module, the intra-table verification refers to the data to be verified coming from the same report, and the inter-table verification refers to the data to be verified coming from different reports, and the verification is performed across tables;

In the data management module, the fields include numerical fields for subsequent report logic calculations; and the conditional fields are used for data screening in report logic.

8. Application of the method as claimed in any one of claims 1 to 5, or the system as claimed in claims 6 and 7, in report supervision submission, enterprise financial report preparation, and business performance analysis.

9. A hardware system for implementing the method as described in any one of claims 1-5, characterized in that the hardware system comprises: a memory and a processor; a computer program is stored in the memory, and when the computer program is executed by the processor, the method as described in any one of claims 1-5 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.