多数据集实现层式报表

1.1 版本

1.2 问题描述

若报表使用的数据量是上百万条的话，觉得报表展现的速度慢，可以使用层式报表来提高报表展现速度。

但由层式报表章节可以得知，层式报表必须是单数据集，若是多数据集的模板且数据量又很大，想要提高报表的查询速度要如何实现呢？

1.3 解决思路

在定义数据集时通过使用数据库本身的行序号或者使用数据库函数生成行序号（即行号）并且在 where 条件中通过页码参数使得行号在一定范围内显示，点击自定义的上一页下一页按钮时重新传入页码参数取出相应的数据。

注：SQL Server2000 无法生成行号，因此需要定义存储过程实现分页查询，以下具体介绍 Access 实现分页的步骤，SQL Server2005 以及 Oracle 数据库实现分页的步骤有一点区别，以下会详细介绍。

如果用户所在场景行式引擎无法支持，则可以试用 新计算引擎功能 ，实现多数据源情况下的分页查询功能，用户无需再写复杂的分页 SQL 或 JS 按钮，就可以让报表快速完成首页加载。
注：该功能支持 Oracle、SQL Server、MySQL、HANA、PostgreSQL、Impala、DB2 等大部分主流 JDBC 数据源。

2.1 新建模板

新建模板 mutipage.cpt。为了加快展示速度，我们可以使用分页查询，获取每个产品的详细信息并计算产品对应的订单中的应付金额，因此添加数据集 ds1 时查询 SQL 语句为 

select 产品.产品ID,产品名称, 供应商ID , 类别ID, 单位数量, 产品.单价, 产品.库存量, 产品.订购量,sum(应付金额) as 应付款项 from 产品,订单,订单明细 where 产品.产品ID=订单明细.产品ID and 订单明细.订单ID=订单.订单ID and 产品.产品ID between 10*(${page}-1)+1 and 10*${page} group by 产品.产品ID,产品名称, 供应商ID , 类别ID, 单位数量, 产品.单价, 产品.库存量, 产品.订购量 order by 产品.产品ID

设置参数 page 的默认值为 1，首次看到的是第一页，只查询出第 1~20 条记录；若 page 参数为 2 时，查询出第 21~40 条记录，即第二页内容。

注：这边定义报表每页显示 10 条数据，即 ds1 的查询 SQL 一次只取 10 条数据，从而加快报表展示速度。

2.2 自定义上一页、下一页按钮

使用分页查询后，报表需要根据 page 参数查询出行号在一定范围内的数据，当点击下一页时，page 需要加 1 并传入报表查询出后10条记录；点击上一页时，page 需要减 1 并传入报表查询出前 10 条记录。而报表内置的上一页下一页按钮无法做这些操作，因此需要自定义上一页下一页按钮。

1）在单元格中求出上一页、下一页页码的值

在工具栏中无法直接获取 page 参数的值，因此先在单元格中求出上一页下一页的页码值，然后再在工具栏按钮中获取单元格的值。

2）自定义上一页下一页按钮

点击「模板>模板 Web 属性>分页预览设置」，选择为该模板单独设置，在工具栏中增加两个自定义按钮分别命名为上一页，下一页，使工具栏上只剩下如下图所示几个按钮。

2.3 第一页与最后一页的处理

首次访问报表时，默认显示第一页 page=1，此时上一页按钮应该是无效的，否则点击上一页按钮时（页码为 0），此时查询行号在 -9 到 0 之间的记录将会出错；同样，显示到最后一页时需下一页按钮无效。即当上一页页码page-1=0时，上一页无效；当下一页页码 page>总页数 时，下一页无效。
求出总页数

根据总记录数及每页显示条数，求出总页数。新增数据集 ds2，SQL 语句为：SELECT count(*) as 总数 FROM 产品，查询出产品表总条数，拖入单元格，如下：

双击总数所在单元格弹出数据列设置对话框，选择「高级>自定义显示」，在值中填入公式：roundup($$$/10,0) 求出总页数。

将第一行的行高设置为 0，或者是隐藏：

上一页按钮设置：选中上一页自定义按钮，点击自定义事件设置回调函数，在 JS 中填入：

var page = $("tr[tridx=0]", "div.content-container").children().eq(0).html();
//如果报表显示第一页，则上一页不可用 
if (page == 0) {
	this.setEnable(false);
} else {
	window.location.href = "${servletURL}?reportlet=doc/Advanced/multipage.cpt&page=" + page;
}

注：第一句是获取上一页页码（A1 单元格）的值，其中最后的 html() 可以用 Text() 代替；第二句是重新加载报表并给 page 参数赋值。

注：若获取的页码时合并了单元格，那么需要注意获取的单元格。举个例子，如果需要获取D1单元格中的值，那么在不合并单元格的情况下，eq 后面的列号应该写 3；如果 ABC 列单元格合并了，那么他们其实是作为了一个整体算列数，这时获取 D1 的值，eq 后面的列数就需要写 1。

上一页按钮设置：下一页按钮设置与上一页的 JS 差不多，只需要获取 B1 的值就可以了，所以在 JS 中填入：

var page = $("tr[tridx=0]", "div.content-container").children().eq(1).html();
var total = $("tr[tridx=0]", "div.content-container").children().eq(2).html();
//如果报表显示最后一页，则下一页不可用 
if (parseInt(page) > parseInt(total)) {
		this.setEnable(false);
	} else {
		window.location.href = "${servletURL}?reportlet=doc/Advanced/multipage.cpt&page=" + page;
	}

注：首次打开报表的时候，由于 page 参数是在数据集中定义的，数据集参数的默认值在第一个次打开报表时没办法传到单元格中，所以需要定义一个完全一样的模板参数 page，默认值设为 1。

分页预览，即可查看效果。

2.4 已完成模板

点击下载模板：multipage.cpt

注：设置参数page的默认值为 1

方案一：使用 ROW_NUMBER() OVER (ORDER BY 主键字段) AS rowno 生成行号。

数据集 ds1 查询 SQL 语句：

select * from (SELECT *,ROW_NUMBER() OVER (ORDER BY 产品id) AS rowno FROM 产品) as b where b.rowno between 20*(${page}-1)+1 and 20*${page}

方案二：使用 NOT IN/TOP

数据集 ds1 查询 SQL 语句：

select top 20 * from 产品 where 产品id not in (select top (20*(${page}-1)) 产品id from 产品 order by 产品id) order by 产品id

方案三：使用 NOT EXIST

数据集 ds1 查询 SQL 语句：

select top 20 * from 产品 where not exists (select 1 from (select top (20*(${page}-1)) 产品id from 产品 order by 产品id)a where a.产品id=产品.产品id) order by 产品id

注：推荐使用方案一的分页方法。ROW_NUMBER() 只支持 SQL2005 及以上版本，NOT EXISTS 执行效率比 NOT IN 强一点。

Oracle 数据库中本身有行序号 ROWNUM，因此只需要将上例 ds1 数据集修改成如下：SELECT * FROM (select A.*,ROWNUM rn from (select * from 产品) A where ROWNUM <=${page}*20) where RN >${page-1}*20即可。

注：ROWNUM 只支持小于，大于是不支持的，因此要做如上定义。

注：设置参数 page 的默认值为 1

MySQL 数据库分页首选 LIMIT 语法，因此只需要将上例 ds1 数据集修改成如下：

方案一：LIMIT

数据集 ds1 查询 SQL 语句：

SELECT * FROM 产品 ORDER BY 产品id LIMIT ${t},20

并给参数t赋值公式：20*($page-1) ，如下图所示：

注：MySQL 数据库中 SQL 语句不能进行直接运算，因此将运算公式赋值给变量 t。

方案二：limit+子查询

数据集 ds1 查询 SQL 语句：

SELECT * FROM 产品 WHERE  产品id >=(SELECT 产品id FROM 产品 ORDER BY 产品id LIMIT ${t}, 1) LIMIT 20

并给参数t赋值为公式：20*($page-1) 

注：LIMIT 接受一个或两个数字参数，参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0。