有些非常规的格式很难用报表工具提供的功能直接实现,但如果准备出合适的数据源,就能大大降低报表设计的难度。
使用免费的集算器可以弥补这一不足。集算器提供了集合运算,支持动态脚本执行,还提供了简单易用的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传回报表工具。
评论