有些非常规的格式很难用报表工具提供的功能直接实现,但如果准备出合适的数据源,就能大大降低报表设计的难度。
使用免费的集算器可以弥补这一不足。集算器提供了集合运算,支持动态脚本执行,还提供了简单易用的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传回报表工具。
相关推荐
- 提供了丰富的编辑工具,包括撤销、重复、剪切、复制、粘贴等常见功能,以及特定于报表设计的功能,如清除格式、检查非线性等。 **6.4 属性** - **行属性**、**列属性**、**边框设置**、**数据类型**等选项,允许...
1. 数据接口层:通过集算器,可以实现数据脱敏、算法封装和报表协助等功能,优化数据准备和ETL处理。 2. 库外运算:减少中间表的使用,降低对存储过程的依赖,提升执行效率。 3. 结构化文本和非关系数据源处理:集算...
目 录译者序前言第一部分 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基础第1章 Access 2000的新增功能 11.1 ...354第15章 高级报表 35515.1 从头开始创建报表 35515.2 报表数据的分组与排序 35515.2.1 分组数据 ...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
Access 2000数据库系统设计(PDF)---002目 录译者序前言第一部分 Access基础第1章 Access 2000的新增功能 11.1 ...354第15章 高级报表 35515.1 从头开始创建报表 35515.2 报表数据的分组与排序 35515.2.1 分组数据 ...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
Access 2000数据库系统设计(PDF)---009目 录译者序前言第一部分 Access基础第1章 Access 2000的新增功能 11.1 ...354第15章 高级报表 35515.1 从头开始创建报表 35515.2 报表数据的分组与排序 35515.2.1 分组数据 ...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
21510.2.1 创建传统的单列等值连接 21610.2.2 为查询结果集指定排序次序 21810.2.3 使用表之间的间接关系创建查询 21910.2.4 创建多列等值连接和选择唯一值 22010.3 使用表中的查找字段 22210.3.1 用查阅向导添加...
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 ...
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 性能分析...
- **分组与排序**:介绍如何对报表数据进行分组和排序。 - **完成报表**:总结创建报表的最后步骤。 - **高级用户快速开始**: - **选择报表类型及数据源**:让高级用户能够快速确定所需创建的报表类型及对应的...
10.2.2 使用筛选器发现资源冲突 10.3 解决冲突 10.3.1 更改资源分配 10.3.2 安排加班时间 10.3.3 重新定义资源日历 10.3.4 分配兼职工作 10.3.5 控制资源开始执行任务的时间 ...
10.2.2 使用筛选器发现资源冲突 10.3 解决冲突 10.3.1 更改资源分配 10.3.2 安排加班时间 10.3.3 重新定义资源日历 10.3.4 分配兼职工作 10.3.5 控制资源开始执行任务的时间 ...