Discussion on the application of Symphony spreadsheet in the report test

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: