Professional Reporting Software-Part II

2. Support for business-personnel-tailored data manipulation

esCalc is specially designed for the business personnel, and thus support for the business-personnel-tailored data manipulation. Even the business personnel without technical background can use it to prepare the report of comparatively complex style. Let’s take a further look at it with the above-mentioned “Sales Reports for Specific Products” for example.

Step 1: Retrieve data. This report is related to the 2 physical tables in the database. Technical personnel can union the 2 physical tables together through the complex SQL statements. For business personnel, they can retrieve the data without having to write the SQL statements. For example, when retrieving the Product Information table, you only need to click the selection field, as shown in the below figure:

The result is shown in below figure:

Similarly, get the “sales record chart”, as shown in the below figure:

Step 2：Arrange data. In this step, you will need to merge the 2 grids ,and only keep the specific 3 products. The period is from July to December in 2010 and the data of the same period in previous year. For the average reporting tool, they all need the complex script to arrange the data. However, esCalc does not require the script. Simple and straightforward actions are enough for esCalc users to achieve their goal.

Judging from the data of the Sales Record table, you may find that the data volume is relatively few so we can remove the unwanted data directly to only keep ApXXXX,GpXXXX,JiXXXX, as shown below:

Then, add the unit price of UnitPrice and the product name of MediName from Product Information table to the Sales Record table. To do so, you will need to set the column A of the 2 grids as the join criteria (i.e. Master cell), and perform the horizontally join, as shown in the below figure:

The result is as follows:

In the above figure, the 3 pieces of data from the Product Information table will be added and merged into multiple bands of Sales Information table. In which, the null data is unwanted and will be removed through filtering, as shown in the below figure:

The filtered result is as follows:

Then, with the similar methods, filter out the data of the same period this year and previous year. The result is shown below:

Step 3: Design table style. The goal of this step is to calculate the sales of each month by product name, year, and month.

Firstly, copy the column Name and overwrite the column MediID. Then, insert another 2 columns after the OrderDate. In C2 and D2, enter the formula respectively:=year(B2) and =month(B2).Then we have got the year and month, as shown in the below figure:

In esCalc, users only need to write the formula once, and in other related cells (also known as homo-cells), these formulas will be copied and adjusted automatically. For example, the C3 in above figure.

Then, group by products. Right-click on the column Name and select to execute Group action, as shown in the below figure:

In the above figure, Quick operation has implemented according to the default parameters of Operation, not requiring any additional settings. The result is shown below:

Similarly, group by year and month respectively. After grouping, the column OrderDate will be deleted. The result is shown in the below figure:

Then, append a column to the UnitPrice. In F5, enter another folmula =D5*E5 to calculate the sales, as shown in the below figure:

Then, calculate the subtotal of monthly sales. In F4, enter :={F5}.sum(), as shown in the below figure:

Click the group number 3 on the top left corner to collapse the data display, and delete the 2 columns of Quantity and UnitPrice to get a clearer view of these data:

Step 4: Calculate the link relative ratio and month-on-month comparisons. Append a column LRR, and enter the formula for month-on-month comparison to E31:=(D31[B3]-D4[B3])/D4[B3], in which the [B3] represents that calculations are limited to different months of a same year. Without it, in E137 (should be null), the data of July in 2010 and December in 2009 would be compared by mistake. The result is as follows:

Similarly, calculate the comparison of the same period. Append a column YOY, and enter the formula in F137:=(D137[A2]-D4[A2])/D4[A2], in which the [A2] represents that the calculation is only limited to the same product in different years. Without it, data will appear mistakenly in F247 in which there should be a null value. The result is shown in the below figure:

The above table is the final report before enhancing. Considering the procedure of reporting, although this is a relatively complex report, the procedure of making report neither needs SQL statement nor script or complex formula. Even the average business personnel can handle it well.

The difficulty in reporting is the data manipulation. The data filtering, grouping, sorting, summarizing, calculating, and other data manipulations exist all over the whole procedure of reporting. It is because the basic data manipulation methods provided by the average reporting tools are a bit too technologized that they depend heavily on the technical personnel. esCalc supports the business-personnel-tailored data manipulation to facilitate the reporting.

Related Article
Professional Reporting Software-Part III