esProc Assists Jasper in Calculating Loan Payments

Problem source: http://community.jaspersoft.com/questions/851148/loop-jasper

The calculation of loan payments according to loan amount will involve loop operation and inter-row operation. It is difficult to write code for it using stored procedure or Scriptlets. Yet with the help of esProc, the Jasper calculation will become easier. Here is an example.

Database table loan contains loan data including loan amount, terms by the month and yearly interest rate. You need to create a Jasper grouped report in which, under each sum of loan amount, payment details per term such as payment, interest rate, principal and principal balance are listed. Below is a selection from the loan table:

The following esProc code is used for preparing the data:

A1: Execute SQL to retrieve records from loan.

A2: Add two computed columns – mRate (monthly rate) and mPayment (monthly payment) to A1. Below is the result:

A3: Calculate payment details for each term based on loan information. Loop function A2.() calculates A2’s members sequentially, uses “()” to perform computation step by step(steps are separated by commas), and returns the computed result of the expression in the last step. new function is used to create a two-dimensional table. A3’s result is cascade data, as shown below:

A4: Combine the cascade data to create a two-dimensional table containing payment details for each term of every loan amount.

A5: Return A4’s result to the report. A reporting tool will identify esProc with JDBC interface as a normal database.

Then create a simple grouped table with Jasper in the following template:

Below is a preview of the finished report:

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

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

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

Below is a preview of the finished report:

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.