esProc Assists Report Development – Inter-row Calculation

Uncategorized 375 0

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.

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website