JasperReport报表开发之转置交叉表
使用Jasper或BIRT等报表工具时,常会碰到一些很规的统计,用报表工具本身或SQL都难以处理,比方源数据不符合交叉表的要求,须要转置后再呈现。
集算器具有结构化强计算引擎,集成简单。能够协助报表工具方便地实现此类需求。以下通过一个样例来说明转置交叉表的实现过程。
数据库表booking汇总着各年度商品的预定数据。有四个字段,包含年份和三种预定状态,部分数据例如以下:
报表要求呈现指定年份及上一年的预定情况。当中行组有三项,即三种预定状态,列组是年份。測度是当年的预定数据。此外要汇总出指定年份各预定状态的增长率。表例子如以下:
能够看到,这张报表的难点是:源数据无法直接用于交叉表,汇总列要用相对位置来动态计算。
假设能将源数据转置。并将汇总列事先计算出来,则会显著减少难度,比方以下这样:
以下用集算器准备报表所需的数据,代码例如以下:
A1=yearBegin=yearEnd-1
yearEnd是来自报表的參数,表示用户指定的年份,比方2014。A1中的代码用来计算上一年(比方2013),为了方便引用,上一年定义为yearBegin。
A2=myDB1.query(“select * from booking where year between ? and ?
order by year desc”,yearBegin,yearEnd)
这句代码用来从数据库查出指定年份及上一年的数据。myDB1是数据源名,指向MYSQL。函数query可运行SQL语句,也能够接受參数。如果yearEnd=2014,则A2的计算例如以下:
A3=create(row,col,value)
这句代码用来新建序表A3。
A3有三个字段:row、col、value,将来可存储整理之后的数据。新建后的A3例如以下:
须要注意的是。序表类似数据库结果集,也是结构化二维表。但序表是泛型的,同一个字段能够存储不同的数据类型,序表也是有序的,能够按序号訪问数据。
利用序表的这些特点能够方便地实现本案例。
A4: for ["visits","bookings","successfulbookings"]
这句代码对集合["visits","bookings","successfulbookings"]进行循环訪问,在循环中向A3追加数据。终于准备出报表须要的数据。
for语句的作用范围是B4-C7,用自然的缩进就能够表示。无需括号或begin/end等标记。
在作用范围里能够用A4来引用循环变量,即for语句所在单元格的格名。比方进行第一遍循环时。A4的值等于”visits”。
以下看循环体中的代码。
B4=endValue=eval(“A2(1).”+A4)
这句代码可从A2动态地取出第一条记录的预定状态数据。
函数eval可将字符串解析为表达式,比方第一遍循环时。“A2(1).”+A4会被解析为A2(1).visits,计算结果是500。当中“A2(1)”表示第一条记录,“.visits”表示取出该记录的visits字段。即下图红框处:
C4=beginValue=eval(“A2(2).”+A4)
和endValue类似,beginValue从A2中动态地取出第二条记录的预定状态数据。第一遍循环时,endValue等于400。
B5=A3.insert(0,A4,A2(1).year,endValue)
C5=A3.insert(0,A4,A2(2).year,beginValue)
这两句代码向A3追加记录。函数insert能够向序表插入记录。第一个參数能够指定插入的位置,假设这个參数为0,则表示在最后追加记录。
比方第一遍循环时,B5追加的记录是:”visits”、2014、500,C5追加的记录是”visits”、2013,400。追加后A3例如以下:
B6=endValue/beginValue-1
这句代码计算指定年份的增长率,比方第一遍循环时,B6=500/400-1=0.25。
C6=if(B6>0:”+”,B6<0:”-”)+string(B6,”#%”)
这句代码用来格式化B6。算法是:假设B6大于0,则在百分数前面加“+”号,假设小于0,则加“-”号。比方第一遍循环时,C6=”+25%”。注意:格式化数据适合用报表实现,所以本步骤并不是必须。
B7=A3.insert(0,A4,string(yearEnd)+”/”+string(yearBegin),C6)
这句代码向A3追加新记录,比方第一遍循环时,插入的记录是”visits”,”2014/2013”,”+25%”。例如以下图:
值得注意的是。这次插入的记录都是字符串。和之前的类型不同。
整个循环运行后,报表须要的数据就会所有追加在A3中,例如以下:
result A3
这句代码将A3返回给报表工具。集算器对外提供JDBC接口。报表工具会将集算器识别为普通数据库,集成方案请參考相关文档。
接下来以JasperReport为例设计一张简单的交叉表。模板例如以下:
报表中须要定义一个參数pyearEnd,用来相应集算器中的參数。
预览后能够看到报表结果:
报表调用集算器的方法和调用存储过程一样。比方将本脚本保存为booking.dfx,则在JasperReport的SQL设计器中能够用booking $P{pendYear}来调用。