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

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

阅读更多

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

  使用免费的集算器可以弥补这一不足。集算器提供了集合运算,支持动态脚本执行,还提供了简单易用的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
分享到:
评论

相关推荐

    润乾报表4.0用户手册.pdf

    - 提供了丰富的编辑工具,包括撤销、重复、剪切、复制、粘贴等常见功能,以及特定于报表设计的功能,如清除格式、检查非线性等。 **6.4 属性** - **行属性**、**列属性**、**边框设置**、**数据类型**等选项,允许...

    新一代数据处理引擎解决方案.pptx

    1. 数据接口层:通过集算器,可以实现数据脱敏、算法封装和报表协助等功能,优化数据准备和ETL处理。 2. 库外运算:减少中间表的使用,降低对存储过程的依赖,提升执行效率。 3. 结构化文本和非关系数据源处理:集算...

    Access 2000数据库系统设计(PDF)---001

    目 录译者序前言第一部分 Access基础第1章 Access 2000的新增功能 11.1 Access 2000...354第15章 高级报表 35515.1 从头开始创建报表 35515.2 报表数据的分组与排序 35515.2.1 分组数据 35615.2.2 数据分组排序 35815.3...

    Access 2000数据库系统设计(PDF)---002

    Access 2000数据库系统设计(PDF)---002目 录译者序前言第一部分 Access基础第1章 Access 2000的新增功能 11.1 ...354第15章 高级报表 35515.1 从头开始创建报表 35515.2 报表数据的分组与排序 35515.2.1 分组数据 ...

    Access 2000数据库系统设计(PDF)---018

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---003

    Access 2000数据库系统设计(PDF)---002目 录译者序前言第一部分 Access基础第1章 Access 2000的新增功能 11.1 ...354第15章 高级报表 35515.1 从头开始创建报表 35515.2 报表数据的分组与排序 35515.2.1 分组数据 ...

    Access 2000数据库系统设计(PDF)---011

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---020

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---009

    Access 2000数据库系统设计(PDF)---009目 录译者序前言第一部分 Access基础第1章 Access 2000的新增功能 11.1 ...354第15章 高级报表 35515.1 从头开始创建报表 35515.2 报表数据的分组与排序 35515.2.1 分组数据 ...

    Access 2000数据库系统设计(PDF)---015

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---012

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---027

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---025

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---026

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access 2000数据库系统设计(PDF)---029

    21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...

    Access+2000中文版高级编程

    8.11 使用非常规的优化技术 232 8.11.1 性能调整的陷阱 232 8.11.2 诊断慢速查询 232 8.11.3 用查询设计网格以解决模糊字段的引用问题 233 8.12 使用分析器向导 233 8.12.1 表分析器向导 234 8.12.2 ...

    Access 2000中文版高级编程(part1)

    8.11 使用非常规的优化技术 232 8.11.1 性能调整的陷阱 232 8.11.2 诊断慢速查询 232 8.11.3 用查询设计网格以解决模糊字段的引用问题 233 8.12 使用分析器向导 233 8.12.1 表分析器向导 234 8.12.2 性能分析...

    Crystal Reports 2008 SP1 用户指南

    - **分组与排序**:介绍如何对报表数据进行分组和排序。 - **完成报表**:总结创建报表的最后步骤。 - **高级用户快速开始**: - **选择报表类型及数据源**:让高级用户能够快速确定所需创建的报表类型及对应的...

    PROJECT 2007宝典 9/9

     10.2.2 使用筛选器发现资源冲突   10.3 解决冲突   10.3.1 更改资源分配   10.3.2 安排加班时间   10.3.3 重新定义资源日历   10.3.4 分配兼职工作   10.3.5 控制资源开始执行任务的时间   ...

    PROJECT 2007宝典 7/9

     10.2.2 使用筛选器发现资源冲突   10.3 解决冲突   10.3.1 更改资源分配   10.3.2 安排加班时间   10.3.3 重新定义资源日历   10.3.4 分配兼职工作   10.3.5 控制资源开始执行任务的时间   ...

Global site tag (gtag.js) - Google Analytics