A non-embedded comprehensive budget query method and system

Abstract

The present invention relates to a non-embedded comprehensive budget query method and system, which belongs to the field of information management technology and solves the problem that the existing budget management is complicated and the data query is inconvenient. It includes receiving a command of comprehensive budget, obtaining parameter information, opening a WPS client and displaying a button corresponding to the user authority in the parameter information in the comprehensive budget function area; receiving a command of a report library in a button, obtaining a report list according to user authority, and loading any report selected in the report list; each report corresponds to a layout template information; receiving a command of refreshing data in a button, checking whether the actual layout data of the current report is consistent with the layout template information, if consistent, obtaining report data, and batch assigning report data in the data area of ​​the current report; receiving a command of data drill-down in a button, obtaining and displaying the drill-down data corresponding to the selected cell in the data area according to the selected drill-down level. Flexible budget query is realized.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a non-embedded comprehensive budget query method and system. Background Art

[0002] As budget report styles become more and more complex and budget management involves more and more organizations and businesses, it is becoming increasingly difficult to meet the needs of various companies for budget report design and query.

[0003] Currently, packaged software can be used for budget management. Companies with R&D centers often choose to develop their own budget query systems. The implementation of budget reports can be based on open source reporting components, or mature reporting products can be purchased to prepare complex budget reports.

[0004] However, mature packaged software and reporting products are difficult to expand and develop in line with changes in business needs. Usually, custom function development is used to replace the original software functions, which faces difficulties in integration and maintenance, and it is difficult to unify the interface style. Moreover, for enterprises with a high level of confidentiality, there are also great restrictions on the choice of packaged software and reporting software under the requirement of localization. In addition, the use of open source reporting components for self-development requires a lot of time and manpower to learn and fix the technical defects, and at the same time, it is necessary to solve the support issues of various browsers, which has a long development cycle. System users also need to learn how to operate the constantly upgraded reporting functions. The existing data drill-down is based on the preset drill-down rules, and the drill-down level of each dimension cannot be configured, making it inconvenient to query detailed data. Summary of the invention

[0005] In view of the above analysis, the embodiments of the present invention aim to provide a non-embedded comprehensive budget query method and system to solve the problems of complex existing budget management and inconvenient data query.

[0006] On the one hand, an embodiment of the present invention provides a non-embedded comprehensive budget query method, comprising the following steps:

[0007] Receive a comprehensive budget command, obtain parameter information, open a WPS client and display a button corresponding to the user authority in the parameter information in the comprehensive budget function area;

[0008] Receive the command of the report library in the button, obtain the report list according to the user's permissions, and load any report selected in the report list in the WPS client; each report corresponds to a layout template information;

[0009] Receive the command to refresh data in the button, verify whether the actual layout data of the current report is consistent with the layout template information, if they are consistent, obtain the report data based on the layout template information, and batch assign the report data to the data area of ​​the current report;

[0010] Receive the data drill-down command in the button, obtain the drill-down data corresponding to the selected cell in the data area and display it according to the selected drill-down level.

[0011] Based on the further improvement of the above method, the comprehensive budget function area is a custom function area extended by integrating the WPS add-in corresponding to the comprehensive budget query, and the buttons include: new report, save report, edit report, report library, refresh data, save data and data drill down.

[0012] Based on the further improvement of the above method, the WPS client is opened and a button corresponding to the user authority in the parameter information is displayed in the comprehensive budget function area thereof, including:

[0013] Start the local WPS client, read the publish.xml file of the local WPS client, load all WPS add-ins, and obtain the button of the comprehensive budget function area;

[0014] Call the add-in function in the parameter information, set the corresponding button in the comprehensive budget function area to be available according to the budget button permission granted to the user role in the user permissions, and save the user permissions to the storage object of WPS.

[0015] Based on the further improvement of the above method, loading any report selected from the report list in the WPS client includes: obtaining the report download address according to the report ID of any selected report, downloading the current report and loading it into the WPS client.

[0016] Based on the further improvement of the above method, the layout template information includes the report ID, the data cube ID, the page axis starting position, the column axis starting position, the dimensions of the page axis, the column axis and the row axis and the members of each dimension;

[0017] Verify whether the actual layout data of the current report is consistent with the layout template information, including:

[0018] According to the starting position of the page axis and the starting position of the column axis, and in accordance with the filling direction of each dimension, the value of the cell where each dimension member is located is obtained in turn until the value of the cell is empty, and the obtained cell value is used as the parameter value of each dimension member;

[0019] Remove duplicate parameter values ​​of members of each dimension to obtain the actual parameter values ​​of each dimension;

[0020] Verify that the number of actual parameter values ​​for each dimension is consistent with the number of members for each dimension in the layout template information, and,

[0021] Check the actual parameter value of each dimension to see whether it exists in each dimension member in the layout template information.

[0022] Based on the further improvement of the above method, according to the starting position of the page axis and the starting position of the column axis, and in accordance with the filling direction of each dimension, the values ​​of the cells where the members of each dimension are located are obtained in turn, including:

[0023] Move the cell one column to the right from the starting position of the page axis to fill the starting position of the dimension member of the page axis, and obtain the values ​​of the cells where the dimension members of the page axis are located one by one;

[0024] Take the starting position of the column axis as the starting point, and move down the cells that are equal to the number of column axis dimensions as the starting position of the dimension member filling of the row axis. Move rightward in turn as the filling columns of each row axis dimension, and get the value of the cell where the dimension member is located one by one for each column.

[0025] Starting from the starting position of the column axis, the cells obtained by moving right by the same number of cells as the row axis dimensions are used as the starting position for filling the dimension members of the column axis. The cells are then moved downward in sequence as the filling rows for each column axis dimension, and the values ​​of the cells where the dimension members are located are obtained one by one from the right side for each row.

[0026] Based on the further improvement of the above method, the report data is obtained based on the layout template information, and the report data is batch assigned to the data area of ​​the current report, including:

[0027] Based on the data cube in the layout template information, query the row axis dimension members of each row, the column axis dimension members of each column, and the parameter values ​​of the dimension members of the page axis in the actual layout data of the current report as the screening conditions of each cell, and use the query results as the data of the cell to obtain the report data;

[0028] According to the starting position of the dimension member filling of the column axis and the number of rows and columns of the report data, the data area of ​​the report is obtained, and the report data is assigned in batches.

[0029] Based on the further improvement of the above method, according to the selected drill-down level, the drill-down data corresponding to the selected cell in the data area is obtained and displayed, including:

[0030] According to the cell selected in the data area, obtain the corresponding dimension members of the page axis, column axis and row axis, and put them into the initial member;

[0031] According to the drill-down level selected for the dimensions of the page axis, column axis, and row axis, the drill-down members of the initial members of each dimension are obtained, and the drill-down members are combined to obtain the drill-down query conditions;

[0032] According to the drill-down query conditions, the drill-down data is obtained and displayed.

[0033] Based on further improvements of the above method, the drill-down levels include: members only, members and sub-members, sub-members, members and descendants, members and leaf level, descendants, and leaf level;

[0034] According to the drill-down level selected for the dimensions of the page axis, column axis, and row axis, the drill-down members of the initial members of each dimension are obtained, and the drill-down members are combined to obtain the drill-down query conditions, including:

[0035] Identify the drill-down level of each dimension in turn. If the drill-down level of the current dimension is "members only" or empty, the initial member of the current dimension is used as the drill-down member. If the drill-down level of the current dimension is not "members only" and is not empty, obtain the detailed information of the initial member of the current dimension, and then query the drill-down members of each initial member of the current dimension according to the drill-down level. Combine the drill-down members obtained from each dimension to obtain the drill-down query condition.

[0036] On the other hand, an embodiment of the present invention provides a non-embedded comprehensive budget query system, including: a comprehensive budget startup module, which is used to receive a comprehensive budget command, obtain parameter information, open a WPS client and display a button corresponding to the user authority in the parameter information in the comprehensive budget function area therein;

[0037] The report library module is used to receive the command of the report library in the button, obtain the report list according to the user's authority, and load any report selected in the report list in the WPS client; each report corresponds to a layout template information;

[0038] The report data query module receives the command to refresh data in the button, verifies whether the actual layout data of the current report is consistent with the layout template information, and if they are consistent, obtains the report data based on the layout template information, and assigns the report data in batches to the data area of ​​the current report;

[0039] The report data drill-down module is used to receive the data drill-down command in the button, and obtain and display the drill-down data corresponding to the selected cell in the data area according to the selected drill-down level.

[0040] Compared with the prior art, the present invention can achieve at least one of the following beneficial effects:

[0041] 1. Allow users to complete the layout design of budget reports and fill in budget data in a familiar spreadsheet operation mode, seamlessly integrate the text format and shortcut key processing of EXCEL, expand the comprehensive budget query function area through WPS add-ins, and integrate it into the menu bar of the WPS client. There is no need to pay attention to browser compatibility issues, and only training on the extended functions is required, which greatly reduces training time and reduces implementation difficulty;

[0042] 2. By configuring the page axis, row axis, and column axis, the dimensions and members of the data cube are rendered in the report, which allows users to configure the report layout and display the actual data in one report file, improving the efficiency of configuration and adjustment. By extending the WPS API, the report can be rendered quickly.

[0043] 3. Through the configuration of multiple drill-down levels, any summary value in the report data can be drilled down to multiple layers of detailed data, which improves the convenience of budget data query.

[0044] In the present invention, the above-mentioned technical solutions can also be combined with each other to achieve more preferred combination solutions. Other features and advantages of the present invention will be described in the subsequent description, and some advantages can become obvious from the description, or can be understood by practicing the present invention. The purpose and other advantages of the present invention can be realized and obtained through the contents particularly pointed out in the description and the drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] The drawings are only for the purpose of illustrating particular embodiments and are not to be considered limiting of the present invention. Like reference symbols denote like components throughout the drawings.

[0046] Figure 1 This is a flow chart of a non-embedded comprehensive budget query method in Embodiment 1 of the present invention;

[0047] Figure 2 This is an example diagram of report layout design in Example 1 of the present invention;

[0048] Figure 3 This is an example diagram of the data drill-down level selection interface in Example 1 of the present invention. DETAILED DESCRIPTION

[0049] The preferred embodiments of the present invention are described in detail below in conjunction with the accompanying drawings, wherein the accompanying drawings constitute a part of this application and are used together with the embodiments of the present invention to illustrate the principles of the present invention, but are not used to limit the scope of the present invention.

[0050] Example 1

[0051] A specific embodiment of the present invention discloses a non-embedded comprehensive budget query method, which integrates the comprehensive budget function in the local WPS client, so that users can complete the layout design of the budget report and fill in the budget data in a familiar electronic spreadsheet operation mode. It makes full use of the characteristics of the electronic spreadsheet to quickly fill in data. Figure 1 As shown, the method comprises the following steps:

[0052] S11: receiving a comprehensive budget command, obtaining parameter information, opening a WPS client and displaying a button corresponding to the user authority in the parameter information in the comprehensive budget function area.

[0053] It should be noted that before conducting a comprehensive budget, it is necessary to develop and deploy a WPS add-in corresponding to the comprehensive budget query and integrate it into the WPS client.

[0054] Specifically, by customizing the WPS add-in, a WPS add-in for comprehensive budget query is developed, and the comprehensive budget function area is expanded, the buttons of which include: new report, save report, edit report, report library, refresh data, save data and data drill down.

[0055] For example, in the ribbon.xml file of WPS, the general CustomUI standard is adopted, the custom ribbon is set to "comprehensive budget" with the tab tag, each button therein is set with the button tag, and the trigger event of javascript is called through the onAction attribute of the button to implement the corresponding button function.

[0056] Through the wpsjs publish command of the wpsjs toolkit, the WPS add-in directory of the developed comprehensive budget query is packaged, and all files in the generated folder are deployed to the server address filled in during packaging. The corresponding publish.html address is provided to the user, and the user opens the publish.html file in the browser, which displays the add-in name and add-in type of the comprehensive budget, installs and enables the WPS add-in of the comprehensive budget query, and automatically generates the publish.xml file in the local WPS client directory to complete the integration of the comprehensive budget function with the WPS client. In this embodiment, the add-in type of the comprehensive budget is a spreadsheet.

[0057] When conducting comprehensive budget management or inquiry, the user logs in to the system that can perform budget management or inquiry. After successful login, the user's basic information such as user ID and user name is obtained, and the user's position and role, as well as the accessible permissions of each role are obtained based on the user ID, and placed in the user permission information in the cache.

[0058] When a comprehensive budget is required, a request is sent from the currently logged-in system through the HTTP protocol to call the WpsInvoke.InvokeAsHttp interface method of WPS. The parameter information passed in includes: add-in type, add-in name, add-in function, and parameters required by the add-in function. Among them, the add-in type and add-in name are consistent with the type and name specified when deploying the add-in, and the parameters required by the add-in function are the user permission information in the cache.

[0059] Then, open the WPS client and display the buttons corresponding to the user permissions in the parameter information in the comprehensive budget function area, including:

[0060] Start the local WPS client, read the publish.xml file of the local WPS client, load all WPS add-ins, and obtain the button of the comprehensive budget function area;

[0061] Call the add-in function in the parameter information, set the corresponding button in the comprehensive budget function area to be available according to the budget button permission granted to the user role in the user permissions, and save the user permissions to the storage object of WPS.

[0062] For example, some users have the design role, so after logging in, the buttons for creating new reports, saving reports, editing reports and report library are available in the comprehensive budget function area of ​​the opened WPS client; some users have the budget role, so the buttons for report library, refreshing data, saving data and data drill-down are available.

[0063] It should be noted that the storage object of WPS is the PluginStorage object. Each WPS add-in has only one PluginStorage instance object. User permissions are stored in the PluginStorage in the form of key-value pairs, which facilitates sharing of user permission information between the web pages of each button in the comprehensive budget function area.

[0064] Preferably, the silent time of the WPS add-in for comprehensive budget query is detected by a timer. If it reaches 30 minutes, the user permission information stored in PluginStorage is cleared, and the button in the comprehensive budget function area is grayed out and unavailable. The user needs to log in to the original system again and then open the WPS client from the original system to operate the comprehensive budget function, thereby improving the safety of the operation.

[0065] S12: receiving a command from the report library in the button, obtaining a report list according to user permissions, and loading any report selected from the report list in the WPS client; each report corresponds to a layout template information.

[0066] It should be noted that after completing the creation of a new report, the reports in the report library are saved by clicking the Save Report button. The WPS client receives the command to save the report in the button, uploads the report of the current report, uses the report ID as the report file name, and uses xls as the report suffix, and stores it in the server preset directory.

[0067] The command of the report library in the button is received, the report library interface is popped up, and the report list is obtained and displayed according to the user's authority. Preferably, the report list is displayed in categories according to the report grouping.

[0068] Loading any report selected from the report list in the WPS client includes: obtaining the report download address according to the report ID of any selected report, downloading the current report and loading it into the WPS client.

[0069] Exemplarily, according to the server access address where the report file is uploaded, the report ID is concatenated to obtain the download address corresponding to the report file, which is then passed into the wps.EtApplication().Workbooks.openFromUrl() method to load the downloaded report file into the WPS client.

[0070] It should be noted that each report corresponds to a layout template information. The layout template information is generated according to the report layout configuration when a new report is created. The layout template information includes the report ID, data cube ID, page axis starting position, column axis starting position, page axis, column axis and row axis dimensions and each dimension member.

[0071] Next, based on the process of creating a new report, the generation of layout template information and the location and function of each attribute in the report are explained in detail.

[0072] When the command to create a new report in the button is received, the report layout configuration interface pops up, fill in the report name, select the report group, and obtain the data cube with permission to operate as the selection list based on the position in the user's permissions. If the user's position is not granted with a data cube, obtain all data cubes as the selection list, and select 1 data cube from the selection list to associate with the current new report.

[0073] It should be noted that report grouping is the classification of reports from a business perspective; a data cube includes multiple dimensions of data, each dimension includes one or more dimension members, and the value of a fixed field in each data cube can be queried based on one or more dimension members. For example, if the fixed field is value, the value of the value field can be uniformly queried based on each dimension member.

[0074] Preferably, the data cube is stored in a multidimensional database, which is more convenient for querying multi-layer data.

[0075] After selecting the data cube, the dimensions of the data cube and the members under each dimension are displayed in a list. Select the dimensions and members to add to the page axis, row axis, and column axis. It should be noted that all dimensions of the data cube need to be selected, but the members under each dimension can be selected according to business needs.

[0076] Exemplarily, the dimension of the row axis is configured as account, and its members are the account items corresponding to the current report requirements; the dimensions of the column axis are configured as audit clues and time, the members of the audit clue dimension are actual amount and budget amount, and the members of the time dimension are 2020 and 2021; the other dimensions of the data cube are configured as page axes, and 1 member is selected for each page axis dimension.

[0077] At the same time, in the report layout configuration interface, configure the page axis starting position and the column axis starting position. Exemplarily, the page axis starting position is B2, and the column axis starting position is E21.

[0078] After the configuration is completed, the generated layout template information is stored in the database, including: unique report ID, data cube ID, report group ID, page axis starting position, column axis starting position, page axis, column axis and row axis dimensions and each dimension member.

[0079] According to the layout template information, get the filling starting position of each dimension, including:

[0080] The cell at the start of the page axis is used as the dimension name of the page axis to fill the start position; the cell one column to the right of the start of the page axis is used as the dimension member of the page axis to fill the start position;

[0081] Starting from the start position of the column axis, the cells obtained by moving down the same number of cells as the column axis dimensions are used as dimension members of the row axis to fill the start position;

[0082] Starting from the starting position of the column axis, the cells obtained by moving right by the same number of cells as the row axis dimensions are used as dimension members of the column axis to fill the starting position.

[0083] For example, the configured page axis starting position is B2, the column axis starting position is E21, 2 dimensions are selected from the data cube as column axis dimensions, and 1 dimension is selected as row axis dimension. Then the starting position for filling dimension names on the page axis is B2, the starting position for filling dimension members on the page axis is C2, the starting position for filling dimension members on the row axis is E23, and the starting position for filling dimension members on the column axis is F21.

[0084] Fill the starting position according to the obtained dimensions and fill the newly created report, including:

[0085] ① Fill in the report ID and data cube ID according to the preset cell positions respectively;

[0086] Exemplarily, through wps.EtApplication().ActiveSheet.Range("A1").Value, the data cube ID is filled in the A1 cell of the current report, and similarly, the report ID is filled in the B1 cell.

[0087] ② Fill the starting position and the number of dimensions of the page axis according to the dimension name of the page axis, fill the dimension name of the page axis one by one, fill the starting position according to the dimension member of the page axis, and fill the dimension members of the page axis dimension one by one in the format of [dimension ID of the page axis].[dimension member ID of the page axis];

[0088] For example, the starting position of the page axis is B2, and the number of page axis dimensions is 10. Then, the dimension names of the page axis are filled from B2 to B11, and the dimension members of the page axis dimensions are filled from C2 to C11.

[0089] ③ According to the starting position of the row axis dimension member filling, the filling columns of each row axis dimension are filled to the right in sequence. Each column is filled with the dimension members of the current row axis dimension one by one in the format of [row axis dimension ID].[row axis dimension dimension member ID];

[0090] For example, the starting position for filling the dimension members of the row axis is E23, the number of dimensions of the row axis is 1, and the number of dimension members of each row axis dimension is 20. Then, starting from E23 and going down to E42, 20 dimension members of the row axis dimensions are filled one by one:

[99793] .[CA365001],

[99793] .[CA365014],

[99793] .[CA365015], ...,

[99793] .[CA333004].

[0091] ④ Combine the dimension members of each column axis dimension to obtain multiple groups of different dimension member combinations as the dimension members to be filled. According to the starting position of the dimension members of the column axis, fill them downward in sequence as the filling rows of each column axis dimension. Each row is filled to the right one by one with the dimension members to be filled of the current column axis dimension in the format of [column axis dimension ID].[column axis dimension ID of the dimension member to be filled]. Finally, each column corresponds to a dimension member combination.

[0092] It should be noted that, according to the configured column axis dimensions and dimension members, multiple groups of different dimension member combinations are combined with each other, so that the dimension members of each column correspond to different combinations after filling. As shown in Table 1, the dimensions of the column axis are audit clues (dimension ID is

[82491] ) and time (dimension ID is

[29458] ), which are divided into two rows for filling, where the members of the audit clue dimension are actual amount (dimension member ID is [PFJE]) and budget amount (dimension member ID is [BUDGET]), and the members of the time dimension are 2020 (dimension member ID is [2020.INP]) and 2021 (dimension member ID is [2021.INP]), and then 4 columns are combined, which are filled one by one to the right in the row where each column axis is located.

[0093] Table 1 Example table of column axis dimensions and dimension members to be filled

[0094]

[82491] .[PFJE]

[82491] .[PFJE]

[82491] .[BUDGET]

[82491] .[BUDGET]

[29458] .[2020.INP]

[29458] .[2021.INP]

[29458] .[2020.INP]

[29458] .[2021.INP]

[0095] For example, the starting position for filling dimension members of the column axis is F21, the number of column axis dimensions is 2, and the dimension members of each column axis dimension are 2. Then, the dimension members of the first column axis dimension are filled from F21 to the right to I21, and the dimension members of the second column axis dimension are filled from F22 to the right to I22.

[0096] Preferably, in the report data, in addition to displaying the numerical value below the dimension member of the column axis, the Chinese information of the dimension member of the row axis will also be displayed on the left side of the numerical value. Therefore, according to the number of configured row axis dimensions, when filling the dimension member to be filled in each column axis dimension, a corresponding column will be reserved for displaying the Chinese information of the dimension member of the row axis. This embodiment does not limit the specific implementation method. When obtaining the dimension member to be filled in each column axis dimension, a combination can be added, in which the preset column axis dimension member ID indicates that the column corresponds to the Chinese information of the dimension member of the row axis. The corresponding column can also be moved right before filling.

[0097] After the newly created report is automatically filled, the user can add static text information and styles such as titles, tables, and headers, and can also adjust the position of the automatically filled row and column axis dimension member cells. It should be noted that if you want to adjust the position of the page axis, column axis, or row axis as a whole, you need to use the edit report function to modify the page axis start position and column axis start position in the layout configuration interface, and then refill to avoid inconsistency between the page axis start position and column axis start position in the actual report layout and the layout template information.

[0098] Specifically, the command to edit the report in the receiving button is displayed, and according to the layout template information of the current report, the configured page axis starting position and column axis starting position, as well as the dimensions and dimension members of the page axis, column axis and row axis are displayed in the report layout configuration interface, the layout template information is updated according to the edited information, and the current report is refilled.

[0099] Figure 2 This is a sample report file generated according to the configuration. The starting position of the page axis, the starting position of the column axis, the display area of ​​the page axis, the row axis and the column axis, and the initial data area are marked in the figure. In the dimension member combination of the first column in the column axis, [name] is used as the preset column axis dimension member ID. When obtaining report data, when [name] is identified, the Chinese information of the row axis dimension member will be obtained as the data of the column cell. Figure 2 The report title and header are manually configured, and users can also add other information using the formulas and styles provided by the WPS spreadsheet.

[0100] In this embodiment, after completing the new report, you can use the built-in saving method of the WPS spreadsheet to temporarily save the budget report locally. There is no need to interact with the database and application server throughout the process. After the preparation is completed, you can save the report to the server through the Save Report button, which improves the overall preparation efficiency.

[0101] S13: receiving a command to refresh data in the button, verifying whether the actual layout data of the current report is consistent with the layout template information, and if they are consistent, obtaining report data based on the layout template information, and batch assigning the report data to the data area of ​​the current report.

[0102] It should be noted that before obtaining report data, in order to avoid users from making erroneous modifications to members of each dimension when adjusting the report layout and style and to ensure the accuracy of data acquisition, the actual layout data of the current report will be checked to see if it is consistent with the layout template information during configuration.

[0103] Specifically, verify whether the actual layout data of the current report is consistent with the layout template information, including:

[0104] ①According to the starting position of the page axis and the starting position of the column axis, and in accordance with the filling direction of each dimension, the values ​​of the cells where the members of each dimension are located are obtained in sequence until the value of the cell is empty, and the obtained cell values ​​are used as the parameter values ​​of the members of each dimension;

[0105] It should be noted that, according to the starting position of the page axis and the starting position of the column axis, and in accordance with the filling direction of each dimension, the values ​​of the cells where the members of each dimension are located are obtained in sequence, including:

[0106] Move the cell one column to the right from the starting position of the page axis to fill the starting position of the dimension member of the page axis, and obtain the values ​​of the cells where the dimension members of the page axis are located one by one;

[0107] Take the starting position of the column axis as the starting point, and move down the cells that are equal to the number of column axis dimensions as the starting position of the dimension member filling of the row axis. Move rightward in turn as the filling columns of each row axis dimension, and get the value of the cell where the dimension member is located one by one for each column.

[0108] Starting from the starting position of the column axis, the cells obtained by moving right by the same number of cells as the row axis dimensions are used as the starting position for filling the dimension members of the column axis. The cells are then moved downward in sequence as the filling rows for each column axis dimension, and the values ​​of the cells where the dimension members are located are obtained one by one from the right side for each row.

[0109] ② Remove duplicate parameter values ​​of members in each dimension to obtain the actual parameter values ​​of each dimension;

[0110] It should be noted that, as can be seen from Table 1, when multiple column axis dimension members are configured, the parameter values ​​of the dimension members of the column axis dimension in the report are repeated due to the combination of the dimension members. Therefore, it is necessary to remove the duplicates and then compare them during verification.

[0111] ③ Check whether the number of actual parameter values ​​of each dimension is consistent with the number of members of each dimension in the layout template information, and,

[0112] ④ Check the actual parameter value of each dimension to see whether it exists in each dimension member in the layout template information.

[0113] If the verification fails, an exception reminder is returned. If the verification passes, the report data is obtained based on the data cube in the layout template information, and the report data is batch assigned to the data area of ​​the current report. Specifically, it includes:

[0114] Based on the data cube in the layout template information, query the row axis dimension members of each row, the column axis dimension members of each column, and the parameter values ​​of the dimension members of the page axis in the actual layout data of the current report as the screening conditions of each cell, and use the query results as the data of the cell to obtain the report data;

[0115] Specifically, the data of the cells corresponding to the intersection of each row axis and each column axis is queried based on the parameter values ​​of the dimension members of the page axis, plus the parameter values ​​of the dimension members of the current row axis and the parameter values ​​of the dimension members of the current column axis. The query fields correspond to the fixed fields in the data cube, and the query results are the cell data.

[0116] According to the starting position of the dimension member filling of the column axis and the number of rows and columns of the report data, the data area of ​​the report is obtained, and the report data is assigned in batches.

[0117] Compared with the prior art, in this embodiment, the dimension members of the page axis, row axis and column axis dimensions are used to query report data, and the queried report data does not cover the dimension members. Users can hide the dimensions and dimension members by hiding rows and columns. This method allows users to configure the report layout and display the actual data in one report file, which improves the configuration and adjustment efficiency.

[0118] It should be noted that based on the starting position of the dimension members of the row axis, the cell obtained by moving right according to the number of dimensions of the row axis is used as the starting position of the data area of ​​the report; according to the number of columns and rows of the report data, the starting position of the report detail data area is moved rightward and downward respectively to obtain the end position of the data area of ​​the report.

[0119] For example, the starting position of the row axis dimension member filling is E23, and the number of row axis dimensions is 1, then the starting position of the report data area is F23, the report data has 5 columns and 20 rows, and the end position of the report data area is J42. The obtained report data is assigned to wps.EtApplication().ActiveSheet.Range("F23:J42").Value to implement batch assignment.

[0120] Preferably, in order to improve the style configuration of the data area, a batch style setting formula HTCopyRange is customized. By using the batch style configuration formula in a cell, the data area of ​​the report can be quickly rendered according to the style of the area specified in the formula. Specifically, it includes:

[0121] Receive the command to refresh data in the button, obtain the formula of the cell in the preset range of the report file, and identify whether it is an HTCopyRange formula; if it is an HTCopyRange formula, after batch assignment of report data, copy the style of the area configured in the formula to the data area, otherwise, do not copy the style. Among them, the style includes font, font color, background color, border and WPS formula.

[0122] Exemplarily, the preset range of the formula is from line 2 to line 7 of the report file. The wps.EtApplication().ActiveSheet.Range().Formula method of wps is called to loop in sequence to obtain the formula of each cell. If the formula is obtained, it is identified whether it is an HTCopyRange formula. If so, it is copied. For example, the batch style configuration formula "=HTCopyRange(F3,J3)" is used in cell E3, which means that the style of the {F3:J3} area is copied to the data area. When batch assigning report data, the style of the {F3:J3} area is copied to the {F23:J42} data area by calling wps.EtApplication().ActiveSheet.Range("F3:J3").Copy(ActiveSheet.Range("F23:J42")).

[0123] After obtaining the report data, you can submit and save it by clicking the Save Data button.

[0124] Specifically, the command to save data in the receiving button is used to obtain the report data of the current report for saving. Before saving, it is also possible to verify whether the actual layout data of the current report is consistent with the layout template information. If they are consistent, the data is saved, otherwise an exception reminder is returned.

[0125] S14: receiving a data drill-down command in the button, and obtaining and displaying drill-down data corresponding to the selected cell in the data area according to the selected drill-down level.

[0126] It should be noted that when you click on data drill-down, a drill-down level selection interface pops up. According to the layout template information of the current report, the dimensions of the column axis, row axis and page axis of the current report are displayed in the drill-down level selection interface respectively, and each dimension corresponds to a drill-down level selection box.

[0127] The drill-down levels include: members only, members and sub-members, sub-members, members and descendants, members and leaf level, descendants, and leaf level. Among them, members only: query the current member; members and sub-members: query the current member and its next-level members; sub-members: query the next-level members of the current member; members and descendants: query the current member and all its subordinate members; descendants: query all subordinate members of the current member; leaf level: query the lowest level members of the current member; members and leaf level: query the current member and its lowest level members. Through the configuration of multiple drill-down levels, any summary value in the report data can be drilled down to multiple layers of detailed data, which improves the convenience of budget data query.

[0128] According to the selected drill-down level, the drill-down data corresponding to the selected cell in the data area is obtained and displayed, including:

[0129] ①According to the cell selected in the data area, obtain the corresponding dimension members of the page axis, column axis and row axis, and put them into the initial members;

[0130] It should be noted that when the data drill-down command in the button is received, the dimension members corresponding to the selected cell in the data area are obtained, put into the initial member, and passed as a parameter to the drill-down level selection interface. The method for obtaining the dimension members corresponding to the cell includes:

[0131] According to the selected cell in the data area, get the row and column of the selected cell;

[0132] Based on the row at the start position of the column axis, according to the column of the selected cell and the number of column axis dimensions, the column axis dimension member corresponding to the selected cell is obtained and placed in the initial member;

[0133] Based on the column at the starting position of the column axis, according to the row of the selected cell and the number of row axis dimensions, get the row axis dimension member corresponding to the selected cell and put it into the initial member;

[0134] Based on the starting position of the page axis and the number of page axis dimensions, get the page axis dimension member corresponding to the selected cell and put it into the initial member.

[0135] For example, based on Figure 2 , after refreshing the report data, select the cell G27, and then get the selected cell through wps.EtApplication().ActiveCell, and get the cell row 27, column G, based on the column axis starting position E21, the corresponding row 21, column E, then according to the number of column axis dimensions is 2, get the values ​​of column axis G21 and G22, and put them in the initial member; according to the number of row axis dimensions is 1, get the value of row axis 27E, and put it in the initial member; based on the page axis starting position is B2, since the column where B2 is located is the dimension name of the page axis, the page axis dimension member starting position is C2, according to the number of page axis dimensions is 10, get the values ​​of page axis C2-C11, and put them in the initial member.

[0136] ②According to the drill-down level selected for the dimensions of the page axis, column axis, and row axis, the drill-down members of the initial members of each dimension are obtained, and the drill-down members are merged to obtain the drill-down query conditions;

[0137] Specifically, it includes: identifying the drill-down level of each dimension in turn; if the drill-down level of the current dimension is "members only" or empty, the initial member of the current dimension is used as the drill-down member; if the drill-down level of the current dimension is not "members only" and is not empty, obtaining the detailed information of the initial member of the current dimension, and then querying the drill-down members of each initial member of the current dimension according to the drill-down level; merging the drill-down members obtained from each dimension to obtain the drill-down query condition.

[0138] It should be noted that the detailed information of the current initial member includes: hierarchy code, dimension member ID and parent dimension member ID. The hierarchy code is a field added when the table is created. When storing data, the hierarchy code of the current data will include the parent hierarchy code to facilitate associated queries. If the current data is the top-level node, the parent dimension member ID is null, and the hierarchy code is the current code. For example, the code of the current data is x001, and its parent hierarchy code is f001, then the hierarchy code of the current data is f001x001, so that all subordinate data can be obtained according to the parent hierarchy code.

[0139] Considering that among the above drill-down levels, only the three drill-down levels of member, member and sub-member, and sub-member involve only a single-layer association relationship, in order to improve query efficiency, when querying according to the drill-down level, the drill-down members are directly queried based on the parent dimension member ID association; for other drill-down levels, multi-layer drill-down members are fuzzily queried based on the level code.

[0140] This embodiment does not limit the selection of drill-down levels for each dimension, such as Figure 3As shown in the figure, only the "Cost Center" in the page axis is set to the "Leaf Level" drill-down level, and the drill-down levels of other dimensions are "Member Only" or empty. In this case, you only need to select the value of the dimension member corresponding to the "Cost Center" (in Figure 2 The lowest level member is queried, and then the lowest level member is combined with the values ​​of the column axis and row axis dimension members in the initial member, as well as the values ​​of other page axis members as the drill-down query conditions.

[0141] ③According to the drill-down query conditions, obtain the drill-down data and display it.

[0142] It should be noted that when obtaining drill-down data, according to the drill-down members of the page axis, row axis and column axis in the drill-down query conditions, multiple groups of dimension conditions are traversed and combined, and the corresponding drill-down data are obtained one by one, and the drill-down data and the corresponding dimension conditions are returned to the front end, among which the drill-down members of the row axis and page axis in the dimension conditions are used as the row axis dimension members of the front-end display table, and the drill-down members of the column axis are used as the column axis dimension members of the front-end display table.

[0143] Preferably, considering that the page axis dimension members whose drill-down level is "members only" or empty are already in the current report, these page axis dimension members do not need to be displayed in the front-end display table used to display the drill-down data.

[0144] For example, G27 selected in the report data area corresponds to the row axis dimension member "Labor Cost" (abbreviated as a), the column axis dimension members are "Actual Amount" (abbreviated as b) and "2020" (abbreviated as c), and the "Cost Center" query in the page axis dimension member obtains the drill-down members including Finance Department (abbreviated as e1), Development Department (abbreviated as e2) and Planning Department (abbreviated as e3). The other 9 page axis dimension members are referred to as d. According to the three groups of dimension conditions obtained by combination: a+b+c+d+e1, a+b+c+d+e2, a+b+c+d+e3, three drill-down data are queried respectively. In the display table of the drill-down data, the corresponding three drill-down data have a+e1, a+e2, a+e3 as row axis dimension members, and b+c as column axis dimension members.

[0145] Compared with the prior art, the non-embedded comprehensive budget query method provided in this embodiment enables users to complete the layout design of budget reports and filling in budget data in a familiar spreadsheet operation mode, seamlessly integrates the text format and shortcut key processing of EXCEL, expands the comprehensive budget query function area through the WPS add-in, and integrates it in the menu bar of the WPS client. There is no need to pay attention to browser compatibility issues, and only training on the extended functions is required, which greatly reduces the training time and reduces the difficulty of implementation; through the configuration of the page axis, row axis and column axis, the dimensions and members of the data cube are rendered in the report, which is convenient for users to configure the report layout and display the actual data in one report file, thereby improving the configuration and adjustment efficiency, and quickly rendering the report by extending the WPS API; through the configuration of multiple drill-down levels, any summary value in the report data can be drilled down to multiple layers of detailed data, thereby improving the convenience of budget data query.

[0146] Example 2

[0147] A specific embodiment 2 of the present invention discloses a non-embedded comprehensive budget query system, thereby realizing the comprehensive budget query method in embodiment 1. The specific implementation of each module refers to the corresponding description in embodiment 1. The system includes:

[0148] A comprehensive budget starting module, used for receiving a comprehensive budget command, obtaining parameter information, opening a WPS client and displaying a button corresponding to the user authority in the parameter information in the comprehensive budget function area therein;

[0149] The report library module is used to receive the command of the report library in the button, obtain the report list according to the user's authority, and load any report selected in the report list in the WPS client; each report corresponds to a layout template information;

[0150] The report data query module receives the command to refresh data in the button, verifies whether the actual layout data of the current report is consistent with the layout template information, and if they are consistent, obtains the report data based on the layout template information, and assigns the report data in batches to the data area of ​​the current report;

[0151] The report data drill-down module is used to receive the data drill-down command in the button, and obtain and display the drill-down data corresponding to the selected cell in the data area according to the selected drill-down level.

[0152] It should be noted that the system also includes a report layout design module. The reports in the report library module are designed and saved by the report layout design module. The report layout design module includes creating a new report, editing a report and saving a report.

[0153] Specifically, in the report layout design module, when a command to create a new report is received in the button, the configured page axis starting position and column axis starting position are obtained in the report layout configuration interface, as well as, based on the selected data cube, the configured dimensions and dimension members of the page axis, column axis and row axis, and the layout template information is generated; according to the layout template information, the filling starting position of each dimension is obtained, and the newly created report is filled; when a command to edit the report is received in the button, according to the layout template information of the current report, the configured page axis starting position and column axis starting position, as well as the dimensions and dimension members of the page axis, column axis and row axis are displayed in the report layout configuration interface, the layout template information is updated according to the edited information, and the current report is refilled; when a command to save the report is received in the button, the report file of the current report is uploaded.

[0154] Since the non-embedded comprehensive budget query system of this embodiment and the aforementioned comprehensive budget query method can refer to each other in related aspects, it is a repeated description here, so it will not be repeated here. Since the principle of this system embodiment is the same as that of the aforementioned method embodiment, this system also has the corresponding technical effects of the aforementioned method embodiment.

[0155] Those skilled in the art will appreciate that all or part of the processes of the above-mentioned embodiments can be implemented by instructing related hardware through a computer program, and the program can be stored in a computer-readable storage medium, wherein the computer-readable storage medium is a disk, an optical disk, a read-only storage memory, or a random access memory, etc.

[0156] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by any technician familiar with the technical field within the technical scope disclosed by the present invention should be covered within the protection scope of the present invention.

Claims

1. A non-embedded comprehensive budget query method, characterized in that: The steps include: Receive a comprehensive budget command, obtain parameter information, open a WPS client and display a button corresponding to the user authority in the parameter information in the comprehensive budget function area therein; Receive a command from the report library in the button, obtain a report list according to the user authority, and load any report selected from the report list in the WPS client; each report corresponds to a layout template information; Receive a command to refresh data in the button, verify whether the actual layout data of the current report is consistent with the layout template information, and if they are consistent, obtain report data based on the layout template information, and batch assign the report data to the data area of ​​the current report; A data drill-down command in the button is received, and according to the selected drill-down level, drill-down data corresponding to the cell selected in the data area is obtained and displayed.

2. The non-embedded comprehensive budget query method according to claim 1, characterized in that: The comprehensive budget function area is a custom function area extended by integrating the WPS add-in corresponding to the comprehensive budget query, wherein the buttons include: new report, save report, edit report, report library, refresh data, save data and data drill down.

3. The non-embedded comprehensive budget query method according to claim 2, characterized in that: The step of opening the WPS client and displaying a button corresponding to the user authority in the parameter information in the comprehensive budget function area thereof includes: Start the local WPS client, read the publish.xml file of the local WPS client, load all WPS add-ins, and obtain the button of the comprehensive budget function area; The add-in function in the parameter information is called, and according to the budget button authority granted to the user role in the user authority, the corresponding button in the comprehensive budget function area is set to be available, and the user authority is saved in the storage object of WPS.

4. The non-embedded comprehensive budget query method according to claim 3, characterized in that: The loading of any report selected from the report list in the WPS client includes: obtaining a report download address according to a report ID of any selected report, downloading the current report and loading it into the WPS client.

5. The non-embedded comprehensive budget query method according to claim 4, characterized in that: The layout template information includes report ID, data cube ID, page axis starting position, column axis starting position, dimensions of page axis, column axis and row axis and members of each dimension; The checking whether the actual layout data of the current report is consistent with the layout template information includes: According to the page axis starting position and the column axis starting position, and in accordance with the filling direction of each dimension, the value of the cell where each dimension member is located is obtained in sequence until the value of the cell is empty, and the obtained cell value is used as the parameter value of each dimension member; Deduplication of parameter values ​​of members of each dimension to obtain actual parameter values ​​of each dimension; Verify whether the number of actual parameter values ​​of each dimension is consistent with the number of members of each dimension in the layout template information, and, The actual parameter value of each dimension is checked respectively to see whether it exists in each dimension member in the layout template information.

6. The non-embedded comprehensive budget query method according to claim 5, characterized in that: The step of sequentially obtaining the value of the cell where each dimension member is located according to the page axis starting position and the column axis starting position and the filling direction of each dimension includes: The cell that is one column shifted to the right from the starting position of the page axis is used as the starting position of the dimension member filling of the page axis, and the values ​​of the cells where the dimension members of the page axis are located are obtained one by one downward; Take the starting position of the column axis as the starting point, and move down the cells that are equal to the number of column axis dimensions as the starting position of the dimension member filling of the row axis. Move rightward in turn as the filling columns of each row axis dimension, and get the value of the cell where the dimension member is located one by one for each column. Starting from the starting position of the column axis, the cells obtained by moving right by the same number of cells as the row axis dimensions are used as the starting position for filling the dimension members of the column axis. The cells are then moved downward in sequence as the filling rows for each column axis dimension, and the values ​​of the cells where the dimension members are located are obtained one by one from the right side for each row.

7. The non-embedded comprehensive budget query method according to claim 6, characterized in that: The step of acquiring report data based on the layout template information and assigning the report data in batches to the data area of ​​the current report includes: Based on the data cube in the layout template information, query is performed using the row axis dimension member of each row, the column axis dimension member of each column, and the parameter value of each dimension member of the page axis in the actual layout data of the current report as the screening condition of each cell, and the query result is used as the data of the cell to obtain the report data; According to the dimension member filling start position of the column axis and the number of rows and columns of the report data, the data area of ​​the report is obtained, and the report data is batch assigned.

8. The non-embedded comprehensive budget query method according to claim 6, characterized in that: The method of obtaining and displaying drill-down data corresponding to a cell selected in a data area according to the selected drill-down level includes: According to the cell selected in the data area, obtain the corresponding dimension members of the page axis, column axis and row axis, and put them into the initial member; According to the drill-down level selected for the dimensions of the page axis, column axis, and row axis, the drill-down members of the initial members of each dimension are obtained, and the drill-down members are combined to obtain the drill-down query conditions; According to the drill-down query conditions, the drill-down data is obtained and displayed.

9. The non-embedded comprehensive budget query method according to claim 8, characterized in that: The drill-down levels include: members only, members and sub-members, sub-members, members and descendants, members and leaf level, descendants, and leaf level; According to the drill-down level selected for the dimensions of the page axis, column axis, and row axis, the drill-down members of the initial members of each dimension are obtained, and the drill-down members are combined to obtain the drill-down query conditions, including: Identify the drill-down level of each dimension in turn. If the drill-down level of the current dimension is "members only" or empty, the initial member of the current dimension is used as the drill-down member. If the drill-down level of the current dimension is not "members only" and is not empty, obtain the detailed information of the initial member of the current dimension, and then query the drill-down members of each initial member of the current dimension according to the drill-down level. Combine the drill-down members obtained from each dimension to obtain the drill-down query condition.

10. A non-embedded comprehensive budget query system, characterized in that: include: A comprehensive budget starting module, used to receive a comprehensive budget command, obtain parameter information, open a WPS client and display a button corresponding to the user authority in the parameter information in the comprehensive budget function area therein; A report library module, used to receive a command of the report library in the button, obtain a report list according to the user authority, and load any report selected from the report list in the WPS client; each report corresponds to a layout template information; A report data query module receives a command to refresh data in the button, verifies whether the actual layout data of the current report is consistent with the layout template information, and if they are consistent, obtains the report data based on the layout template information, and assigns the report data in batches to the data area of ​​the current report; The report data drill-down module is used to receive the data drill-down command in the button, and obtain and display the drill-down data corresponding to the selected cell in the data area according to the selected drill-down level.

Images