【Question】

I have a query that returns some summary records, loan amount, loan term, interest rate, for instance. Then I want to have a second row that builds out the detailed payment schedule. The report would look like this:

Loan Amt      Term      Rate

100,000      05months      4.75

payment      interest      principal      principlebalance

20,238.13     395.83      19,842.30     80,157.70

20,238.13     317.29      19,920.84     60,236.86

20,238.13     238.44      19,999.69     40,237.17

20,238.13     159.27      20,078.86     20,158.31

20,238.10     79.79       20,158.31     0

20,000       2months      5

payment      interest      principal      remaining

10,062.55     83.33       9,979.22      10,020.78

10,062.53     41.75       10,020.78     0

As you can see, for each loan the amortization table can be calculated solely from the 3 values supplied. But my question is, how do I write a for loop under detail1? For the record the loan fields are detail 2 and each amortization section is the detail2 band. I came to know that I can implement this using a table/subdataset. But I'm new to Jasper and haven’t gotten to know how to use a table/subdataset. Any help with sample code is highly appreciated and of great help. Thanks in advance.

【Answer】

A loop operation and inter-row calculations are involved to calculate monthly payment according to the total loan amount. It’s difficult to achieve that using a stored procedure or the Scriptlets. It’s much easier to get this done for Jasper in SPL (Structured Process Language):

A1: Retrieve data using a SQL-like statement.

A2: Add two computed columns: mRate and mPayment.

A3: Create a new table sequence made up of LoanID, LoanAmt, payment, Term, Rate, interest, principal and principlebalance; then split each of A2’s record into N records (N is Term value) and insert them into the table sequence.

Here’s the final result:

Take A2’s first record as an example, the Term value is 5, so it is split into 5 records to be populated into A3’s new table sequence. variable t is assigned a new value through t=t-principal, so values of interest, principal and principlebalance vary among the 5 records.

The SPL script can be embedded into the JasperReprot to execute. Details can be found in How to Call an SPL Script in Java.