esProc Assists Jasper in Calculating Opening Balance

Problem source:

http://community.jaspersoft.com/questions/850400/how-create-report-opening-balance-using-jasper-reports

The calculation of opening balances according to deposit and withdraw amount involves inter-row operation. Jasper can do the calculation but the code is complicated. You can use esProc to assist the job and make it easy. The following is a simple example.

data.csv contains deposit and withdraw information of different sum of money in an account. You need to calculate the opening balance based on this file. Below is a selection from the original data:

esProc_report_opening_banlance_1

esProc code:

esProc_report_opening_banlance_2

A1: Import the file separated by commas.

A2: Calculate opening balance and return result to the report. The initial value of the account is 43. Operations using parentheses will return result of the expression after the last comma. A2’s result is as follows:

esProc_report_opening_banlance_3

esProc provides JDBC interface. Reporting tools will identify it as a normal database.

Now you can create a simple list table with Jasper in the following template:

esProc_report_opening_banlance_4

Below is a preview of the finished report:

esProc_report_opening_banlance_5

A report calls an esProc script in the same way as it calls the stored procedure. Save the above script as balance.dfx. You can invoke it with call balance() 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 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