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_report_inter_row_1

esProc code for handling it:

esProc_report_inter_row_2

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:

esProc_report_inter_row_3

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:

esProc_report_inter_row_4

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:

esProc_report_inter_row_5

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:

esProc_report_inter_row_6

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

esProc_report_inter_row_7

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.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Application, Reporting tool and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s