润乾集算报表实现动态层次钻取报表(一)

润乾集算报表实现动态层次钻取报表（一）
在报表项目中有时会有动态层次报表，而且还需要层次钻取的场景，开发难度较大。

这里记录了使润乾集算报表开发《各级部门KPI报表》的过程。

《各级部门KPI报表》初始状态如下图：
当前节点是根节点“河北省”，要求报表显示当前节点的下一级节点“地市”汇总的KPI 数值。

Kpi又分为普通指标和VIP指标两类，共四项。

如果点击“石家庄”来钻取的时候，要求能够将石家庄下一级的KPI汇总指标显示出来，如下图：
点击“中心区”钻取，要求能够将下一级的KPI汇总指标显示出来，以此类推，直到显示到最后一级。

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

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

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

采用润乾集算报表实现的第一步：编写集算脚本tree.dfx，完成源数据计算。

集算脚本
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字符串，得到这些叶子节点对应的输入节点“104020”的下一级节点。

形成临时表leaf与kpi表关联分组汇总。

为了能够得到输入节点“104020”的下一级节点的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() 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：关闭数据库连接。

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

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

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

第三步，设计报表如下图：
A列是报表的左半部分，是输入节点（例如：“104020”）的所有父节点和它本身，横向扩展，A1的值是id，显示的是title。

A3显示的是name。

B列是报表的中间部分，是输入节点（例如：“104020”）的下一级子节点，纵向扩展。

在B3格中设置隐藏列的条件是value()==null，如果输入节点本省就是叶子节点，那么name==null，
B列就会隐藏不显示了。

C列到F列是报表的右半部分，显示的是对应的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。

那么有什么其他方案，可以让这些数据库也能实现上述复杂的树形结构计算呢？
集算报表内置的集算引擎具备强大的计算能力，可以解决这个困难。

具体方案可参见《润乾集算报表实现动态层次钻取报表（二）》。