For Loop in jasper

[harik771990]

I have a query that returns some summary records.  For instance,

loan amount, loan term, interest rate.  Then I want to have a second row that builds out the detailed payment schedule.  so 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 the for loop under the 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 table/subdataset..But i'm new to jasper not getting to know how to use table/subdataset..Any help with sample code is highly appreciated and of great help..Thanks in advance..

[hozawa]

Create group for each of loan, term, and rate

[Tom C]

It does not matter what mechanism you are using (main query, sub query, table element, list, etc), the bottom line is that user will need to provide amortization detail data for JasperReports to lay out in the report section. JasperReports will not do the looping for you. There are couples of approach that you can consider but each has its technical challenge:

1. If you are an expert in SQL query coding, you can write a complex query or a stored procedure to produce amortization detail data to feed the report;
2. If you are proficient at Java coding, you can write a Java Scriptlet to calculate amortization matrix and have JasperReports use it as resultant dataset to generate the report;
3. If you are feeling comfortable with recursive programming concept, you can use this technique to call a sub report repeatedly to process the amortization schedule until principal balance reaches zero balance.

For the first two approaches, you can review relevant technical material and/or consult with your in house exports to find a solution. As for the recursive sub report approach, you can refer to my wiki posting at the following URL for a sample report:

http://community.jaspersoft.com/wiki/sample-report-using-recursive-sub-report-calls-calculate-amortization-schedule-based-basic-loan

Using this technique, you should be able to achieve this report design requirement.

[calculate.machine]

Loop operation and inter-row operation are needed when calculating payment for each loan term according to loan amount.

But the code is difficult to write using the stored procedure or Scriptlets.

You can use esProc to assist Jasper in doing this:

A1=myDB1.query("select * from loan")

A2=A1.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment)

A3=A2.((t=LoanAmt,Term.new(A2.LoanID:LoanID, A2.LoanAmt:LoanAmt,A2.mPayment:payment, A2.Term:Term,A2.Rate:Rate, t*A2.mRate:interest, payment-interest:principal, t=t-principal:principlebalance)))

A4=result A3.conj()

Code explanation is available from http://blog.raqsoft.com/?p=3531.