JasperReport报表开发之转置交叉表

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}来调⽤。