解决 Jasper 等报表工具制作报表中的几个难题
在制作业务报表时，经常会遇到一些单独依靠报表工具难以解决的问题。本文将针对几个 Jasper 报表工具用户在国外论坛中提出的现实问题，介绍如何用集算器 SPL 语言加以解决的方案。这里的解决方案其实并不仅适用于 Jasper，对其它报表工具也同样适用。
1. 计算账户各期余额
数据文件 data.csv 存储着某账户各期资金存取情况，在已知账户初始余额为 43 的情况下，需要根据该文件计算出各期余额，部分源数据如下：
Inputs,Outputs 0,10 15,0 22,0 0,33 0,15 0,14 36,0 0,69 2,0 18,0 |
想要得到的报表结果如下：
根据存取金额计算各期余额时需要进行跨行计算，可以用 Jasper 表达式实现，但实现步骤复杂且有一定难度，而通过集算器协助 Jasper 则可以轻松实现。具体的集算器 SPL 代码如下：
A |
|
1 |
=file("data.csv").import@tc() |
2 |
return (t=43,A1.derive(t=t+Inputs-Outputs:Balance)) |
A1 读入文件，选项 @tc 表示文件有标题行、以逗号为分隔符。
A2 计算各期余额并将计算结果返回给报表。其中，设置账户初值 t 为 43，然后在 A1 序表中增加一个计算列，其值为上期余额 + 本期流入 - 本期流出。显然这里的计算结果就是希望报表呈现的内容。
将以上 SPL 代码存为文件 balance.dfx。
为了在报表中呈现计算结果，可以利用集算器对外提供 JDBC 接口。在报表工具中通过建立 JDBC 数据源引入集算器脚本，而报表调用集算器定义的方法和调用存储过程一样，在 Jasper 的 SQL 设计器中可以用 call balance() 来调用并传入参数。详细步骤请参看《JasperReport 调用 SPL 脚本》。
然后，在 Jasper 中设计最简单的 list 表，模板如下：
预览后可以看到报表结果，和我们要求的一样：
2. 计算贷款分期
数据库表 loan 存储着贷款信息，包括贷款总额、按月分期数、年利率。要求实现一张分组表，在每条贷款信息下列出各期明细，包括：当期还款额、当期利息、当期本金、剩余本金。
表 loan 的部分数据如下：
LoanID |
LoanAmt |
Term |
Rate |
L01 |
100000 |
5 |
4.75 |
L02 |
20000 |
2 |
5 |
L03 |
500000 |
12 |
4.5 |
想要得到的报表结果如下图：
根据贷款额计算贷款分期时需要进行循环计算和跨行计算，用存储过程或 Scriptlets 实现的难度较大，而同样使用集算器协助 Jasper 就可以轻松实现。编写集算器 SPL 代码如下：
A |
|
1 |
=db.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.((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))) |
4 |
=A3.conj() |
5 |
return A4 |
A1 执行 SQL，取出 loan 中的记录。
A2 在序表 A1 中新增计算列月利率 mRate 和每期还款额 mPayment。结果如下：
A3 根据贷款信息计算各期明细。A2.()是循环函数，可对 A2 的成员依次进行计算，“()”内的代码将分步计算，最终返回最后一个逗号的计算结果。其中函数 new 用来生成新的二维表。A3 的计算结果是层次数据，如下：
A4 合并层次数据，形成分期明细二维表。
A5 返回结果给报表。
同样，在 Jasper 中可以设计一张简单的分组表，模板如下：
预览后就可以看到需要的的报表结果了。
3. 计算交叉表增长率
数据库表 store 存储着多种产品在 2014、2015 年的销售量，需要用交叉表呈现每种产品每年的销售量，并计算出各产品的年增长率。部分源数据如下：
Year item quantity 2014 Book 35 2015 Pencil 50 2014 Pencil 56 2015 Book 67 |
想要得到如下报表结果：
因为交叉表的每一列都是动态生成的，进行列间计算时又需要动态引用，因此用 Jasper 脚本实现这种需求有一定难度，而用集算器在数据准备阶段实现相应的计算就相对简单很多。集算器 SPL 代码如下：
A |
|
1 |
=mydb.query("select * from store order by item,year") |
2 |
=A1.group(item).run(A1.record(["Growth Rate",item,~(2).quantity/~(1).quantity-1])) |
3 |
return A1 |
A1 取出 store 表的记录。
A2 追加各产品的年增长率。其中，group 按产品分组，run 对每组数据依次计算，record 追加记录，~(i)表示当前组中的第 i 条记录。
A3 将计算结果返回给报表。
在 Jasper 中设计最简单的交叉表，模板如下：
预览后可以看到报表结果：
