用集算器准备非常规格式报表的数据
有些非常规的格式很难用报表工具提供的功能直接实现，但如果准备出合适的数据源，就能大大降低报表设计的难度。
使用免费的集算器可以弥补这一不足。集算器提供了集合运算，支持动态脚本执行，还提供了简单易用的JDBC接口，报表工具可将集算器脚本文件当做数据库存储过程执行，传入参数并用JDBC获得返回结果。
集算器与报表工具的集成结构如下：
下面举例说明集算器实现横向分栏的过程：
报表工具大多有纵向分栏的功能，但很难实现记录横向摆放并分栏的布局。比如库表emp有3个字段，部分数据如下：
|
EId |
Name |
Dept |
|
4 |
Emily |
HR |
|
5 |
Ashley |
R&D |
|
6 |
Matthew |
Sales |
|
7 |
Alexis |
Sales |
|
8 |
Megan |
Marketing |
|
9 |
Victoria |
HR |
|
10 |
Ryan |
R&D |
|
11 |
Jacob |
Sales |
期望的报表布局是记录先横后纵摆放，且横向分为3栏，如下：
如果用集算器将原来的3字段数据转为9字段，就可以用报表工具实现横向分栏了，代码如下：
|
|
A |
B |
C |
|
1 |
=myDB1.query(“select EId,Name,Dept from emp where EId>=? and EId<=? order by EId “,begin,end) | ||
|
2 |
=A1.step(3,1) | =A1.step(3,2)|[null] | =A1.step(3,3)|[null] |
|
3 |
=A2.derive(B2(#).EId:EId2,B2(#).Name:Name2,B2(#).Dept:Dept2,C2(#).EId:EId3,C2(#).Name:Name3,C2(#).Dept:Dept3) | ||
|
4 |
result A3 |
A1:执行SQL，其中begin、end是报表参数。
A2：每隔3条记录从A1取出第1条，组成新的二维表，B2、C2以此类推。由于B2、C2可能比A2少一条记录，因此需要在末尾追加一条空记录。运算符“|”表示将两个集合纵向合并。结果如下：
A3：将B2、C2横向拼接在A2上。B2(#).EId是第1个新列，表示取出B2中第#条记录的EId字段，符号“#”表示A2中每条记录的行号。A3就是报表需要的最终数据，如下：
报表工具可用JDBC的方式调用集算器脚本，就像调用普通数据库中的存储过程，形如：call 脚本文件名(参数1…参数N)。集算器的返回结果会以普通数据集的形式参与报表计算。具体用法可参考集算器集成与应用之JasperReport集成和集算器集成与应用之BIRT集成。
作为专业的报表数据源工具，集算器还可以实现更多的计算，下面分别举例。
错行分栏
将库表emp横向分为两栏，每行的第2栏和下一行的第1栏相同，报表布局如下：
集算器代码：
|
|
A |
B |
|
|
1 |
=myDB1.query(“select EId,Name,Dept from emp where EId>=? and EId<=? order by EId “,begin,end) | ||
|
2 |
=A1.conj([~[-1],~]).to(3,) | ||
|
3 |
=A2.step(2,1) | =A2.step(2,2)|[null] | |
|
4 |
=A3.derive(B3(#).EId:EId2,B3(#).Name:Name2,B3(#).Dept:Dept2) | ||
A2:错位拼接，将上一条记录和当前记录依次拼接起来，从第3行开始取拼接结果。结果如下：
A3、B3、A4：按照前面的例子横向分两栏。
单字段纵向动态分栏
按照先纵后横的布局将单字段结果集展现在报表中，行数和列数都是参数。源数据如下：
| sOrder
26 33 84 133 32 39 43 71 99 20 83 97 107 135 142 1 |
集算器代码：
|
|
A |
|
1 |
=myDB1.query(“select orderID from sorder”).(orderID) |
|
2 |
=Row.new(${Col.(“:c”+string(~)).string()}) |
|
3 |
=Col.(A2.field(~,to((~ -1)*Row+1,~*Row).(A1.m(~)))) |
|
4 |
result A2 |
A1：取出单字段数据，转换为序列（有序集合）
A2：生成与报表布局相同的二维表，初始值为空。参数Row和Col来自报表。
A3:向A2追加数据，最终结果如下：
简单行列转置
数据库表SALES存储着订单数据，部分数据如下：
| OrderID | Client | SellerId | Amount | OrderDate |
|
1 |
DSG |
13 |
19480 |
2014-06-20 00:00 |
|
2 |
ERN |
18 |
979 |
2014-06-13 00:00 |
|
3 |
JFE |
19 |
28972 |
2014-12-11 00:00 |
|
4 |
OFS |
21 |
4829 |
2014-02-24 00:00 |
|
5 |
ERN |
22 |
21392 |
2014-02-01 00:00 |
现在要计算出2013年各月份订单的总金额、最大订单金额、最小订单金额，以及总订单数，并将数据转置成13列4行，即：四种算法是第一列，列名为subtotal，每个月占一列，列名分别是1、2、3、4…其中前五个字段如下：
| subtotal |
1 |
2 |
3 |
4 |
… |
| OSum |
4176606 |
3645032 |
4361721 |
4670117 |
… |
| OMAX |
27997 |
28021 |
27936 |
28022 |
… |
| OMIN |
116 |
232 |
272 |
104 |
… |
| OCount |
295 |
265 |
322 |
324 |
… |
集算器代码：
|
|
A |
|
1 |
=db.query(“select month(ORDERDATE) as MONTH,sum(AMOUNT) as OSum,max(AMOUNT) as OMAX, min(AMOUNT) as OMIN ,count(ORDERID) as OCount from sales where year(ORDERDATE)=2013 group by MONTH order by MONTH”) |
|
2 |
=["OSum","OMAX","OMIN","OCount"].new(~:subtotal,${to(A1.len()).string()}) |
|
3 |
=A1.run(A2.field(#+1,OSum|OMAX|OMIN|OCount)) |
|
4 |
result A2 |
A1：执行SQL，过滤出2013年的数据，并按月份分组汇总，结果如下：
A2：新建空序表，带默认值，用来存储最终结果。共13列，前几列如下：
A4:遍历A1，每次取1条记录，并从第2列开始填充A2。A2 最终如下：
A4:将A2返回给报表工具。
动态定位行列转置
在库表tb中，userid相同的3条记录是一组数据，报表需要将组记录转为行记录。库表tb部分数据如下：
|
userid |
type |
descr |
|
scooby |
dog |
dog |
|
scooby |
weight |
50 |
|
scooby |
hair |
long |
|
mickey |
mouse |
mouse |
|
mickey |
hair |
|
|
mickey |
weight |
2 |
理想的表样：
|
userid |
type |
hair |
weight |
|
mickey |
mouse |
2 |
|
|
scooby |
dog |
long |
50 |
集算器代码：
|
|
A |
|
1 |
=$select * from tb |
|
2 |
=A1.group(userid).new( userid,(t=~.align@n([‘hair’,’weight’],type).(descr))(3):type,t(1):hair,t(2):weight) |
A1:查询数据库；
A2:分组，并将每组数据转为一条记录。函数group可对数据分组，函数new新建二维表，“~”表示分组后的每组数据。函数align可将数据按某集合成员（[‘hair’,’weight’]）对齐，@n表示将无法对齐的数据单列一行。结果如下：
多层转置
库表kpi中，f_site相同的4条记录是一组数据，部分数据如下
|
dataset_date |
f_site |
ioh_kpi |
idh_kpi |
iol_kpi |
|
2015/04/21 13:15 |
X6SF_SARF1 |
1 |
2 |
3 |
|
2015/04/21 13:30 |
X6SF_SARF1 |
9 |
1 |
2 |
|
2015/04/21 13:45 |
X6SF_SARF1 |
8 |
9 |
1 |
|
2015/04/21 14:00 |
X6SF_SARF1 |
7 |
8 |
9 |
|
2015/04/21 13:15 |
XC_01 |
2 |
3 |
4 |
|
2015/04/21 13:30 |
XC_01 |
11 |
12 |
13 |
|
2015/04/21 13:45 |
XC_01 |
21 |
22 |
23 |
|
2015/04/21 14:00 |
XC_01 |
31 |
32 |
33 |
现在要求对每组数据进行行列转置，按f_site分组，理想的表样如下：
|
site |
KPI Name |
2015/04/21 13:15 |
2015/04/21 13:30 |
2015/04/21 13:45 |
2015/04/21 14:00 |
|
X6SF_SARF1 |
ioh_kpi |
1 |
9 |
8 |
7 |
|
idh_kpi |
2 |
1 |
9 |
8 |
|
|
iol_kpi |
3 |
2 |
1 |
9 |
|
|
XC_01 |
ioh_kpi |
2 |
11 |
21 |
31 |
|
idh_kpi |
3 |
12 |
22 |
32 |
|
|
iol_kpi |
4 |
13 |
23 |
33 |
集算器代码：
|
|
A |
B |
|
1 |
=myDB1.query(“select * from kpi order by f_site,dataset_date”) | |
|
2 |
=A1.id(dataset_date) | =A1.fname().to(3,) |
|
3 |
=create(site,’KPI Name’,${A2.string()}) | |
|
4 |
for A1.group(f_site) | = A4.align(A2,dataset_date) |
|
5 |
>B2.run(A3.record([A4.f_site,~]|B4.field(~))) | |
|
6 |
result A3 |
A2:取得A1中不重复的dataset_date，即["2015-04-21 13:15","2015-04-21 13:30","2015-04-21 13:45","2015-04-21 14:00"] ；
B2:取得A1中字段名，从第3个开始，即["ioh_kpi","idh_kpi","iol_kpi"]；
A3:新建空序表，用来存储最终结果。字段名分别为site,KPI Name,”2015-04-21 13:15″,”2015-04-21 13:30″,”2015-04-21 13:45″,”2015-04-21 14:00″ ；
A4:按f_site分组，并循环每组数据。B4将A4按照A2对齐，数据不全则自动补位。B5用来向A3动态追加记录。
A3是最终结果，A6会将A3返回报表工具，结果如下：
复制行
按次序将记录复制3份，并用报表展现。
集算器代码：
|
|
A |
|
1 |
=myDB1.query(“select * from sOrder”) |
|
2 |
=A1.conj([~]*3) |
A2: []表示序列（有序集合），[~]表示将A1当前记录作为单成员序列，[~]*3可将当前记录复制3份，函数conj对A1每个记录执行计算，最后进行合并。计算结果如下：
用条件控制分组表
展现一张分组表，数据来自库表sOrder，分组字段是Seller，明细字段是Client和Amount。特殊要求是：
1.每组明细中，从第2条直到结束需要显示“+”号，第1条不显示。
2.如果每组明细多于1条，则在该组最后显示对Amount的汇总求和，明细只有1条时不显示汇总。表样示意如下：
集算器代码：
|
|
A |
B |
C |
|
1 |
=myDB1.query(“select Seller,Client,Amount from sOrder where Amount>?”,arg) | ||
|
2 |
=create(item,value) | ||
|
3 |
for A1.group(SellerId) | >A2.insert(0:A3,if(#>1,”+”)+Client,Amount) | |
|
4 |
if A3.len()>1 | >A2.insert(0,A3.SellerId+”subtotal:”,A3.sum(Amount)) | |
|
5 |
result A2 |
A1:SQL查询。
A2:创建空二维表，存放最终结果。
A3:按Seller分组，并循环每组数据。循环作用范围即缩进的B3-C4，循环中可用A3引用循环变量
B3：向A2追加当前组明细，如果序号#大于1，则在Client之前加“+”。
B4-C5：如果当前组记录数大于1，则向A2追加subtotal。A2最终结果如下：
A5：将A2通过jdbc传回报表工具。