Calculate Growth Rate in Jasper Crosstabs

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_jasper_crosstab_1

esProc code:

esProc_report_jasper_crosstab_2

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_jasper_crosstab_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_jasper_crosstab_4

Below is a preview of the finished report:

esProc_report_jasper_crosstab_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.

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 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