`
datamachine
  • 浏览: 163015 次
社区版块
存档分类
最新评论

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

阅读更多

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

  使用免费的集算器可以弥补这一不足。集算器提供了集合运算,支持动态脚本执行,还提供了简单易用的JDBC接口,报表工具可将集算器脚本文件当做数据库存储过程执行,传入参数并用JDBC获得返回结果。

  集算器与报表工具的集成结构如下:

esProc_report_unconvent_layout_1

  下面举例说明集算器实现横向分栏的过程:

  报表工具大多有纵向分栏的功能,但很难实现记录横向摆放并分栏的布局。比如库表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栏,如下:

esProc_report_unconvent_layout_2

 

  如果用集算器将原来的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少一条记录,因此需要在末尾追加一条空记录。运算符“|”表示将两个集合纵向合并。结果如下:

esProc_report_unconvent_layout_4

  A3:将B2、C2横向拼接在A2上。B2(#).EId是第1个新列,表示取出B2中第#条记录的EId字段,符号“#”表示A2中每条记录的行号。A3就是报表需要的最终数据,如下:

esProc_report_unconvent_layout_5

  报表工具可用JDBC的方式调用集算器脚本,就像调用普通数据库中的存储过程,形如:call 脚本文件名(参数1…参数N)。集算器的返回结果会以普通数据集的形式参与报表计算。具体用法可参考集算器集成与应用之JasperReport集成集算器集成与应用之BIRT集成

  作为专业的报表数据源工具,集算器还可以实现更多的计算,下面分别举例。

错行分栏

  将库表emp横向分为两栏,每行的第2栏和下一行的第1栏相同,报表布局如下:

esProc_report_unconvent_layout_6

 

  集算器代码:

 

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行开始取拼接结果。结果如下:

esProc_report_unconvent_layout_8

 

  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来自报表。

esProc_report_unconvent_layout_10

 

  A3:向A2追加数据,最终结果如下:

esProc_report_unconvent_layout_11

 

简单行列转置

  数据库表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年的数据,并按月份分组汇总,结果如下:

esProc_report_unconvent_layout_13

 

  A2:新建空序表,带默认值,用来存储最终结果。共13列,前几列如下:

esProc_report_unconvent_layout_14

 

  A4:遍历A1,每次取1条记录,并从第2列开始填充A2。A2 最终如下:

esProc_report_unconvent_layout_15

 

  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表示将无法对齐的数据单列一行。结果如下:

esProc_report_unconvent_layout_17

 

多层转置

  库表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返回报表工具,结果如下:

esProc_report_unconvent_layout_19

 

复制行
  按次序将记录复制3份,并用报表展现。

  集算器代码:

 

A

1

=myDB1.query(“select * from sOrder”)

2

=A1.conj([~]*3)

 

  A2: []表示序列(有序集合),[~]表示将A1当前记录作为单成员序列,[~]*3可将当前记录复制3份,函数conj对A1每个记录执行计算,最后进行合并。计算结果如下:

esProc_report_unconvent_layout_21

 

用条件控制分组表

  展现一张分组表,数据来自库表sOrder,分组字段是Seller,明细字段是Client和Amount。特殊要求是:
1.每组明细中,从第2条直到结束需要显示“+”号,第1条不显示。
2.如果每组明细多于1条,则在该组最后显示对Amount的汇总求和,明细只有1条时不显示汇总。表样示意如下:

esProc_report_unconvent_layout_22

 

 

 

 

 

 

 

 

  集算器代码:

 

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最终结果如下:

esProc_report_unconvent_layout_25

  A5:将A2通过jdbc传回报表工具。

1
1
分享到:
评论
Global site tag (gtag.js) - Google Analytics