用集算器准备非常规格式报表的数据

  有些非常规的格式很难用报表工具提供的功能直接实现，但如果准备出合适的数据源，就能大大降低报表设计的难度。

  使用免费的集算器可以弥补这一不足。集算器提供了集合运算，支持动态脚本执行，还提供了简单易用的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传回报表工具。