解决Birt等报表工具制作报表的几个难题

在上一篇《Birt 如何实现不规则月份统计》中，我们讲解了如何帮助 Birt 制作这种报表的详细过程，在本文中我们再继续讨论几个类似的制作难题，并把说明集中在如何编写集算器 SPL 脚本上，不再赘述如何在 Birt 中引入 SPL 的步骤了。

1.        组内跨行计算

组内跨行计算是指在计算一行中某个计算列的值时，需要引用到组内其它行的数据来进行计算。比如下面这个例子：

库表 sample 有三个字段，其中 id 是分组字段。需要设计一张分组表，使用 id 分组，明细字段是 v1,v2 以及计算列 crossline, 其中 crossline 的算法是本条记录 v1、v2 之和加上本组上一条记录的 v1、v2 之和。示例源数据如下：

id	v1	v2
1	1	2
1	2	3
2	1	1
2	2	2
3	3	3

最后要展现的报表结果如下图所示：

编写集算器 SPL 代码如下：

	A
1	=connect("demo")
2	=A1.query("select   *, 0 as crossline from sample")
3	>A2.group(id).run(~.run(v1+v2+v1[-1]+v2[-1]:crossline))
4	>A1.close()
5	return   A2

 

A1   连接数据库

A2   查询数据库，同时多产生一列常数备用。

A3   按 id 分组，并在每组数据中修改计算列 crossline，最后合并，其中 v1[-1]、v2[-1] 是集算器特有的定位上一行记录中字段的写法。

A4   关闭数据库

A5   将 A2 中的计算结果数据集返回给报表工具

 

2.        跨库数据源

制作报表的数据往往来自于多种数据源，比如不同的物理数据库、文本文件、Excel 文件等，这些数据在报表中往往还需要相互关联进行运算。

报表工具本身能实现从多数据源取数，但进行关联运算会有一定的困难，或者运算性能非常差。而由开发者自己编程去做关联运算，工作量一般又会非常大。而集算器 SPL 恰恰能在这一点帮上大忙。

下面这个例子中，订单表 orders 和订单明细表 orderDetail 数据分别来自两个不同的数据库，二者之间要做 join 运算。两表数据如下：

 

最后想要展现的报表结果如下：

 

编写集算器 SPL 代码如下：

	A
1	=connect("db1")
2	=connect("db2")
3	=A1.query("select orderID,customer,orderDate   from orders")
4	=A2.query("select orderID,productID,price,mount   from orderDetail order by orderID")
5	>A1.close()
6	>A2.close()
7	=join@1(A3:orderID,A4:orderID)
8	=A7.new(#1.orderID,#1.customer,#1.orderDate,#2.productID,#2.price,#2.mount)
9	return A8

 

A1   连接数据库 1

A2   连接数据库 2

A3   查询订单表数据

A4   查询订单明细表数据

A5A6   关闭数据库连接

A7   以 A3 的 orderID 和 A4 的 orderID 为主键进行 left join，连接后的结果集有两个字段，第一个字段是 A3 的记录，第二个字段是 A4 的记录。

A8   以 A7 中两个字段的字段形成新的数据集，也就是需要的结果

A9   将 A8 的数据集返回给报表工具

本例只是演示了两个数据源的 left join，其实 SPL 能做关系数据库能完成的任何数据运算，比如各种 join、union、过滤、分组、排序等。

3.        字段拆分成记录

在本例中，数据库表 data 有两个字段，其中 ANOMOALIES 字段是用空格分隔的多个字符串，我们需要把 ANOMOALIES 按空格拆分为多个字符串，并用每个字符串和原 ID 字段形成新的记录。源数据如下：

ID	ANOMALIES
3903	B1 D1 CAT1
3904	D7 D2 B1 CAD4

最后想要展现的报表结果如下：

编写集算器 SPL 代码如下：

	A
1	=connect("db")
2	=A1.query("select ID,ANOMALIES from   data")
3	=A2.conj(ANOMALIES.array(" ").new(A2.ID:ID,~:ANOMALIES))
4	>A1.close()
5	return A3

 

A1   连接数据库 1

A2   查询 data 表数据

A3   将ANOMALIES字段值按空格拆分，并与原ID形成新的记录

A4   关闭数据库连接

A5   将 A3 形成的数据集返回给报表工具

 

4.        主表中动态插入子表字段

在本例中，数据库表 dColThread 是主表，主键是 tID。dColQuestion 是子表，外键是 tID，如下：

dColThread

tID	ApplicationName	User	Phone	Decline
A01	mfc	Bill	+70000000	1
A02	mfc	John	+18761221	2
A03	java	Jack	+8014001231	6
A04	mfc	Tim	+008613133123	4
A05	db	John	+18761221	8

dColQuestion

qID	tID	status
1	A01	yes
2	A01	no
3	A01	yes
4	A02	yes
5	A03	no
6	A04	no
7	A04	no
8	A05	yes

报表需要根据 ApplicationName 查询主表并以列表的形式展现数据。可以看到，在子表中，主表每条记录对应的 status 字段值有多个，但不超过 5 个。我们需要把子表中的这些记录横向排列后插入主表的 Phone、Decline 字段之间，依次命名为 QuestionNo1、QuestionNo2…QuestionNo5。同时，如果某列数据都为空，则这一列不显示。最后的表样形如下图：

用集算器准备数据，SPL 代码如下：

	A	B
1	=connect("db")
2	=A1.   query("select * from dColThread t,dColQuestion q where t.tID=q.tID and   t.ApplicationName=?",arg1)
3	>A1.close()
4	=A2.group(tID)
5	=create(ApplicationName,User,Phone,QuestionNo1,QuestionNo2,QuestionNo3,QuestionNo4,QuestionNo5,Decline)
6	for A4	=A6.(status)|["","","","",""]
7		= A5.record(A6.ApplicationName|A6.User|A6.Phone|B6.to(5)|A6.Decline)
8	return A5

 

A1   连接数据库

A2   执行 SQL，取出主子表关联数据。arg1 是来自报表参数。假如 arg1= "mfc "，则 A1 的计算结果如下：

A4   按照 tID 分组，每组是一条主表记录及其对应的子表记录，如下图：

A5   按照报表中列表的结构新建空二维表。

A6   循环 A4 中的组，每次向 A5 插入一条记录。循环体中可用 A6 引用循环变量，用 #A6 来引用循环计数。

B6   取当前组中 status 的字段值，并补足至少 5 条记录。

B7   向 A5 追加新记录。循环结束后 A5 如下：

A8：返回结果给报表。

 

隐藏空列的工作交给 BIRT，设计 list 表，模板如下：

如果 QuestionNo 列为空则应当隐藏。动态隐藏的方法有很多，这里介绍其中一种。对于 QuestionNo5（其他列类似），可以先在 dataSet 的 onFetch 方法中使用如下脚本：

if(reportContext.getGlobalVariable("t5")==null){

    reportContext.setGlobalVariable("t5",row.QuestionNo5)

}else{

reportContext.setGlobalVariable("t5",reportContext.getGlobalVariable("t5")+row.QuestionNo5)

}

再在 QustionNo5 列的 Visibility 属性中使用如下表达式：BirtStr.trim(reportContext.getGlobalVariable("t5"))==""

预览后可以看到报表结果：

 

5.        小结

通过以上例子可以看出，报表制作时经常遇到不好处理的数据准备工作，而在集算器的帮助下都可以得到完美的解决。这是因为集算器提供了完备的数据源连接功能，能连接市面上常见的各种数据源并从中取数。同时，SPL 还提供了丰富的函数库，能在库外轻松进行数据的各种关联运算。