esProc Assists Report Development – Inter-row Calculation

Unconventional statistical tasks are not uncommon in creating reports with reporting tools like Jasper and BIRT. One of the cases is to display the result of certain comparisons between the current record and the next/previous record. It is difficult to handle it using only the reporting tool or the SQL. Yet esProc can assist the handling thanks to its powerful computing engine for processing structured data and the feature of being integration-friendly. An example will be cited to explain the esProc method of doing it.

Table salesAll holds orders of per seller, per day. In the report, we need to show sales amount of every month of a certain year according to the ranking order, the differences between neighboring rankings and the growth rate between the current month and the previous one. Some of the source data are as follows:

esProc code for handling it:

A1: Execute the SQL statement to calculate sales amount of each month in a certain year and to sort them by the month. theYear is a parameter passed from the report. The result is as follows:

A2:=A1.derive((amount-amount[-1])/amount[-1]:LRR)

Append a field to A1’s table and compute the link relative ratio for the current month. esProc uses amount[-1] to reference the previous record. The result is as follows:

A3=A2.sort(-amount)

This line of code sorts A2’s table by amount field in descending order.

A4=A3.derive(amount-amount[1]:DIFF)

This line calculates differences between rankings. esProc uses amount[1] to reference the next record. A4’s result is what the report needs:

result A4

This line returns A4’s table to the reporting tool. esProc provides JDBC interface for integrating with the reporting tool that will identify esProc as a database. See related documents for the integration solution.

Then create a simple report with, for instance, JasperReport. The template is as follows

Define a parameter – pthisYear – in the report to correspond to its counterpart in the esProc script. Click on Preview to see the final report:

The reporting tool calls the esProc script in the same way as that in which it calls the stored procedure. Save the esProc script as, say crossrow.dfx, to be called by call crossrow($P{pthisYear}) in JasperReports SQL designer.