Common data comparisons in report testing
In the ERP and BI project testing process, the report data validation is very necessary, the common data comparison scene is as follows: from the system exported Excel format report data, and then give a business data source data, request to verify the correctness of the report data. The amount of data in a report is usually very large, these data are usually the result of aggregation and other logical operations, the amount of source data is also very large, the source data and report data are not necessarily equal, and the source data will often have many tables, only through the naked eye to observe the source data and report data is consistent, Can lead to a large test workload, inefficiencies, and risk is not easy to control.
Then let's explore how to use the features provided in Symphony Spreadsheet 3.0 to solve some of the problems mentioned in the above scenario.
Symphony Spreadsheet basic Knowledge
The report is the source data after the logical processing of the show, in the test analysis and design phase, get the development of the report design documents, by viewing the report data calculation logic, verify that the calculation logic conforms to the requirements of the document. To do this, you need to have a certain understanding of cell representations of spreadsheets and cell references, as shown in the following illustration.
Figure 1. Symphony Spreadsheet Foundation
Symphony Spreadsheet Example
With the basics of spreadsheets, you'll then explain how to use the Advanced data processing capabilities of Symphony spreadsheet to validate report data.
Functional 1--Subtotal
It is common to make various summary calculations of data in a report, for example: the annual sales situation analysis and evaluation of different regions to examine the sales performance, need to be in accordance with the regional summary; To analyze the sales of different products, it is necessary to classify the products according to the product number; To analyze the sales of different departments, You need to sort by department. When you encounter this type of scenario, you can use the subtotal functionality provided by Symphony spreadsheet to complete the validation of the report data.
You need to determine the following two issues before using subtotals
The classification basis of the source data
Determined by the classification basis of the measured report. Common examples are: year, region, department, supplier, etc.
Rollup object for source data
Determined by the measured report. For example: Sales amount, purchase amount, quantity and so on.
Subtotal instance: Calculates the purchase amount by year and by vendor.
In this instance, the classification is based on "year" and "Province", and you need to summarize the amount.
Figure 2. Subtotal
1st Group: The classification is based on "year" and does not need to be calculated for the year, so select "year" when "Calculate Subtotal for" is selected, and select "Average" in the appropriate use function.
Select "Province" in group by in the second set of options, select "Amount" When you select "Calculate Subtotal for", select "Sum" in the Use function, and then click OK. You can see a Purchase record table with the provinces and years nested in the following diagram, which can be directly compared to the report data.
Figure 3 Subtotal Results
Functional 2--Data Consolidation calculation
If you give more than one spreadsheet to the source data, you first need to consolidate the source data before you can compare it to the report data. For source data that needs to be merged, you must ensure that they: