Report Calculator for Desktop BI

Report calculator is a tool to calculate and further process on the report results. The report calculator allows business personnel to process the report result independently so as to tap the potential value of reports, and make the report in rather rigid formats become versatile to confront the changing demand rapidly.

Report calculator for desktop BI should support the export formats generally supported by the reporting tools, for example, the text file and Excel spreadsheet. It should be able to support various intuitive data manipulation methods, allowing users to process the data conveniently and rapidly. Plus, such tools are expected to provide business-personnel-oriented UI, making it possible for users to complete the computation independently without having to rely on technicians. esCalc is just such a report calculator.

The application scenarios of report calculator are given in the below example. In a certain business report system, the query and presentation of orders in a given period is provided. The reporting results include Order number, Order date, Sales person, Clients, and Order value. Now, it is time to make statistics on the big clients accounting for 60% of sales in the recent half year. Because such calculation result is not provided in the report directly, and it is too pressing to request the developers to calculate in this case, business personnel will have to calculate all by themselves.

Business personnel will firstly query on the order data of the recent half year in the reporting system; secondly, export report results and import them to esCalc; thirdly, with several simple steps, achieve the computational goal, and print it out with the pie chart.

esCalc is also fit to calculate the below example:

Based on the reporting result in an Excel spreadsheet, calculate the increment of the year-on- year monthly sales.

Based on the text file exported from the sales report, calculate the sales persons whose monthly sales are ranking the top 3.

Through the table of website visitor volume, calculate the channels whose visitor volumes are among the top 10 in the consecutive 3 weeks.

Based on the report results of a telecommunications company, calculate the changing tendency of the average contribution of each customer during the Christmas season.

As the report calculator for business personnel, esCalc empowers the users with the below features:

1.Support the typical formats of report results

esCalc supports the tabular data import of Excel, including the Excel 97 version to the latest Excel 2010 version. Still the above example, esCalc users can export the order report from business system as Excel, and then import into esCalc, as the shown in the below screenshot:

esCalc also supports the tabular data import from text file, as shown in the below figure:

The text file and Excel spreadsheet are the commonest file formats. Almost all reporting tools support these two patterns, and most reporting results are the tabular data. Therefore, esCalc can handle the various reporting tools that the business personnel encountered in their daily work.

2.Support the calculator style operation

esCalc supports the calculator-style operation, which means that users can monitor the current data, perform an action according to the data characteristics, monitor the execution results, and then perform the next actions according to the current result. Unlike programmers who prefer to solve problems with VBA and development tools alike, business personnel prefer the simple and easy calculator-style operation to solve their problems

Still the above case, to make statistics on the big clients accounting for 60% of total sales for the company in the recent half year, users can calculate the sales achieved from each client, and then sort the sales data descendingly. The steps of this part are shown below:

Firstly, group by client. Right click on any cell (except for the header) of the Client column and then select Group. The result is shown in the below figure:

Secondly, calculate the sales achieved from each client. Enter the formula in the summary section of each group data. For example, when entering the formula ={E3}.sum in E2, other corresponding cells with the same business senses (called as homocells) will be generated and filled to E6, E8, and other cells automatically, as shown in the below figure:

Thirdly, fold the bands to the summary row. Because the detailed data is not currently needed, users can click the level number to hide them to have a clear view of summarized data, as shown in the below figure

Fourthly, sort. In any cell of column E (except for the column header), select Desc on the right-click menu and the data will be sorted descendingly, as shown in the below figure.

Each step described above can be completed in one action. In each step, users can monitor the result of the previous step, based on which, the computation can be carried on. This operation mode is designed on the basis of the findings on business personnel operation habits through a great many of studies, and is referred as Calculator-style Operation.

The Calculator-style Operation allows users to calculate independently without having to resort to technicians. Users can thus consider the problem according to the natural train of thoughts, and decompose a complex problem into several simple steps.

3.Support the arbitrary data processing

Considering the arbitrarily data processing, first and foremost, the processing method must be easy-to-use and strong. Users should be able to perform any processing on the data, and required to perform the minimized types of operations. Through a huge volume of demonstrations and tests, esCalc implements this functions through visualizing and proceduralizing the SQL syntax, including sorting, grouping, query, distinct filtering, and vertical or horizontal merges. The greatest difference from SQL is that the data is processed through the detailed and visualized menu and operations. The complex and hard-to-understand scripts language is not a must any longer.

The arbitrary data processing also refers to free transition between various processing methods. For example, for Excel, the level of difficulties between sorting-before-grouping and grouping-before-sorting vary greatly, whereas they are both simple for esCalc. Similarly, esCalc allows users to add the calculated column to the grouped data, or conduct the further summarizing. In addition, esCalc also supports the filtering, sorting, and other operations on the grouped data arbitrarily.

In the above case, clients are ranked by its contribution to the total sales descendingly. To compute the great client who contributes 60% of the total sales for the company, esCalc users only need to summarize the sales top-down and compare it with the 60% of the total sales. The steps are as follows:

Step 1: Calculate the cumulative sales for each client. Users only need to enter the formula into the column F for once, for example, enter this in F5: =F2+E5. Then, the correspondent formulas will be generated automatically in other homocells, as shown in the below figure:

From this step, we can see that esCalc not only supports the grouping, computational column, inter-row computation, but also the arbitrary inter-row computations on the grouped summary value, or adding the computational columns.

In the step 2, calculate the standard for comparison: the 60% of total sales. In this case, you are only required to enter the formula ={E2}.sum()*0.6, as shown in the below figure:

As can be seen from the above steps, esCalc supports not only the summarization expression, but also the further summarizing on the grouped summery value arbitrarily.

Step 3: Lastly, with the filter criteria just calculated, filter the cumulative value. To do this, right-click on any cell (except for the header) of column F and choose Filter, with the filter criteria @

From this step, we can see that esCalc not only supports the filtering, but also supports the further filtering on the summery value after grouping.

For esCalc, it adopts the more advanced tabular computational module, which enables the business personnel without technical background to leverage the data processing capabilities as strong as SQL. In addition, it guarantees the smooth transition between any operating steps. The arbitrary data processing allows business personnel to operate on the data freely in a natural brain of thoughts. This greatly strengthens the analysis ability of business personnel to confront to the complex and pressing computational task.

4.Support Business-personnel-oriented UI

As the report calculator tailored for business personnel, esCalc has investigated the use habit of many business personnel and provided the similar UI of Excel style. The comparison chart of Excel and esCalc is as shown below:

As can be seen from the above figure, esCalc has the column named after letters and the row named after numbers. Every cell has a unique cell name by which they can be referenced to each other. The formula syntax is similar to that of Excel. When constructing the levels, the cellset can be folded to the left or expanded. One thing to note is that the direct calculation of the summary value of column F is not supported in Excel, so that there is no column F in the Excel sheet shown above. Excel users will have to copy and paste it to another sheet to calculate over again.

Just like Excel, esCalc also allows the style settings, format painter, statistics charts, and print and export. Besides the similar appearances, esCalc also supports the normal and special copies & pastes of formulas like Excel. The only difference is that esCalc additionally supports the auto formula copy and intelligent formula adaptive adjustment, as shown in the below figure: