jasper 中如何将数据拆成多行并跨行累计
【问题】
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..
【回答】
根据贷款额计算贷款分期时需要进行循环计算和跨行计算，用存储过程或Scriptlets实现的难度较大，这种情况用SPL协助Jasper更合适：
A |
|
1 |
=myDB1.query("select * from loan") |
2 |
=A1.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment) |
3 |
=A2.news((t=A2.LoanAmt,Term);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) |
A1：sql取数
A2：增加两个计算列mRate和mPayment
A3：创建由LoanID，LoanAmt，payment，Term，Rate，interest，principal，principlebalance组成的新序表，并根据A2每条记录的Term值，将A2的每条记录拆分成Term条新记录插入到创建的新序表中
结果如下：
以A2中L01这条记录为例：Term=5，该条记录被拆分为5条记录插入到新序表中，通过t=t-principal，t被重新赋值，因此这5条记录中，interest，principal，principlebalance列都在逐条变化。
这个脚本如何嵌入到Jasper里执行，具体可参考Java 如何调用 SPL 脚本
