Calculate Growth Rate in Jasper Crosstabs

Uncategorized 1157 0

Problem source: http://community.jaspersoft.com/questions/847490/how-get-annual-growth-rate-crosstab

As every column in a crosstab is generated dynamically, you also need to reference them dynamically when performing inter-row calculations. There is some difficulty in handling this dynamic reference using a Jasper script. But the data preparation can be made easier using esProc. Let’s look at an example. 

The database table store holds sales amount of multiple products in the year 2014 and 2015. You need to display the sales amount of each product per year using a crosstab and calculate the annual growth rate of every product. Below is a selectin from original data:

esProc_report_growthrate_jasper_1

esProc code:

        

A

1

=mydb.query(“select * from store order by item,year”)

2

=A1.group(item).run(A1.record([“Growth Rate”,item,~(2).quantity/~(1).quantity-1]))

3

result A2

A1: Retrieve records from the store table.

A2: Append annual growth rate of every product to A1. group is used to group data by products; run is used to perform the required calculations by loop; and record is used to append records. ~(i) represents the ith record in the current group. Below is A2’s result:

esProc_report_growthrate_jasper_3

A3: Return A2’s result to the report. Reporting tools will identify esProc equipped with JDBC interface as a normal database.

Then you can create the simplest crosstab with Jasper:

esProc_report_growthrate_jasper_4

Below is a preview of the finished report:

esProc_report_growthrate_jasper_5

A report calls an esProc script in the same way as it calls the stored procedure. Save the above script as AnnualRate.dfx. You can invoke it with call AnnualRate () and input parameters into it from Jasper’s SQL designer.

FAVOR (0)
Leave a Reply
Cancel
Icon

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

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