如何实现报表数据的动态层次钻取（一）

在报表项目中有时会遇到进行动态层次钻取的需求，这种报表的开发难度一般都较大。而润乾报表的实现则相对简便很多。下面就以《各级部门 KPI 报表》为例，讲解润乾报表（需要结合集算器实现）实现此类报表的过程。

《各级部门 KPI 报表》初始状态如下图：

当前节点是根节点“河北省”，要求报表显示当前节点的下一级节点“地市”，以及汇总的 KPI 数值。Kpi 又分为普通指标和 VIP 指标两类，共四项。当点击“石家庄”进行钻取的时候，要求显示石家庄下一级（区县）的 KPI 汇总指标，如下图：

再点击“中心区”进行钻取，要求显示下一级（营业部）的 KPI 汇总指标。以此类推，直到显示到最后一级。如下图：

前四级固定是“省、地市、区县、营业部”，后边则是动态的“架构 4、架构 5、架构 6. . . 架构 13”（根节点“省”对应“架构 0”）。

这个报表对应的 oracle 数据库表有两个，Tree（树形结构维表）和 kpi（指标事实表）。其中 Tree 表的叶子节点，通过 id 字段与 kpi 表关联，数据示例如下图：

 Tree表 

 Kpi表 

这个报表的难点在于 1、动态的树形多层数据、标题；2、树形结构与事实表关联。

具体实现步骤如下：

第一步  编写集算脚本 tree.dfx，完成源数据计算。

集算脚本如下：

                                                     

	A
1	=connect(“ora”)
2	=[“省”,“地市”,“区县”,“营业部”]|to(4,13).(“架构”+string(~))
3	=A1.query(“SELECT id,name FROM tree    START WITH id = ?    CONNECT BY NOCYCLE PRIOR pid = id order by id”,id)
4	=A3.derive(A2.m(#):title)
5	>level=A3.len()
6	>xtitle=A2.m(level+1)
7	=A1.query@x(“with leaf as( SELECT tree.id id,REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(id, ‘;’),‘[^;]+’,1,2)x FROM tree where connect_by_isleaf=1 START WITH ID = ? CONNECT BY NOCYCLE PRIOR id = pid) select nvl(leaf.x,max(leaf.id)) id,’”+xtitle+“’ title,max(tree.name) name, sum(kpi.kpi1) kpi1,sum(kpi.kpi2) kpi2,sum(kpi.vipkpi1) vipkpi1,sum(kpi.vipkpi2) vipkpi2 from leaf left join kpi on leaf.id = kpi.id left join tree on leaf.x=tree.id group by x order by x”,id)
8	return A4,A7

A1：连接预先配置好的 oracle 数据库。

A2：新建一个序列，内容是“省、地市、区县、营业部、架构 4、架构 5、架构 6. . . 架构 13”。

A3：使用 oracle 数据库提供的 connect by 语句编写 sql，从数据库中取出指定 id（节点编号）的所有父节点 id、name。id 是外部参数“当前节点”，如果传进来的值是 104020，那么 A3 的计算结果是：

A4：为 A3 增加一个字段 title，按照顺序，对应 A2 中的层级。结果是：

A5：计算变量 level，A3 序表的长度，也就是当前节点“104020”的层级号“4”（“省”为第一级）。

A6：计算输入节点“104020”的下一级对应的层级名称“架构 4”，赋值给变量 xtitle。

A7：执行 sql 并关闭连接。从 tree 表中取出当前节点“104020”的所有叶子节点，并用 sys_connect_by_path 进行计算，得到当前节点的下一级节点，形成临时表 leaf。leaf 再与 kpi 表进行关联分组汇总。为了能够得到当前节点的下一级节点的 name，leaf 还需要与 tree 关联一次。需要注意的是，如果当前节点号本身就是叶子节点，结果中的 name 将为空。完整的 sql 如下：

with leaf as(

SELECT tree.id id,REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(id, ‘;’),‘[^;]+’,1,2) x FROM tree

where connect_by_isleaf=1

START WITH ID = ?

CONNECT BY NOCYCLE PRIOR id = pid

)

select nvl(leaf.x,max(leaf.id)) id,‘“+xtitle+”’ title,max(tree.name) name, sum(kpi.kpi1) kpi1,sum(kpi.kpi2) kpi2,sum(kpi.vipkpi1) vipkpi1,sum(kpi.vipkpi2) vipkpi2

from leaf left join kpi on leaf.id = kpi.id left join tree on leaf.x=tree.id

group by leaf.x order by leaf.x

计算的结果是：

A8：向报表返回 A4、A7 两个结果集。

第二步  在报表设计器中定义报表参数和集算数据集，调用 tree.dfx。如下图：

定义报表参数“id”

定义集算数据集（其中的参数名“id”是集算脚本的输入参数名，参数值“id”是报表参数。

第三步  设计报表如下图：

A 列是报表的左半部分，是当前节点（例如：“104020”）的所有父节点和它本身，横向扩展，有几级就扩展几列。A1 的值是 id，显示的是 title。A3 显示的是 name。

B 列是报表的中间部分，是当前节点的下一级子节点，纵向扩展。在 B3 格中设置隐藏列的条件是 value()==null，如果输入节点本省就是叶子节点，那么 name==null，B 列就会隐藏不显示了。

C 列到 F 列是报表的右半部分，显示的是 ds2.name 对应的 kpi 统计值。

为了实现报表的格式需要，A3 单元格需要将左主格设置为 B3。

为了实现钻取功能，需要：

1、将 A3 单元格的超链接属性定义为表达式：

“/reportJsp/showReport.jsp?rpx=r4.rpx&id=”+ds1.ID。超链接指向本报表自身，报表参数是当前列对应的 ds1.ID。

2、将 B3 单元格的超链接属性定义为表达式：

“/reportJsp/showReport.jsp?rpx=r4.rpx&id=”+ds2.ID。超链接指向本报表自身，报表参数是当前行对应的 ds2.ID。

第四步：发布报表并运行。实际的超链值如下图：

在上述实现步骤中，难度最大的是 tree.dfx 中用到的 A7 单元格的复杂 SQL。应该说，oracle 数据库的树形递归查询还是比较丰富的，如果用的是其他数据库（比如 MySQL,PostgreSQL 及 Greenplum 等）就很难实现 A7 中的 SQL。那么有什么办法可以让这些数据库也能实现上述复杂的树形结构计算呢？

在后续的文章《如何实现报表数据的动态层次钻取（二）》中，我们将介绍如何利用润乾报表内置集算引擎的强大计算能力来解决这个问题。