报表开发实例——动态多层次KPI钻取报表（I）
在报表项目中有时会有动态层次报表，而且还需要层次钻取的场景，开发难度较大。这里记录了使润乾集算报表开发《各级部门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数据库提供的connectby语句编写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
whereconnect_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.idleft 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列是报表的右半部分，显示的是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。
第四步：发布报表并运行。实际的超链值如下图：
数据库的树形递归查询还是比较丰富的，用其他数据库（比如MySQL,PostgreSQL及Greenplum等）可能就很难实现A7中的SQL。那么有什么其他方案，可以让这些数据库也能实现上述复杂的树形结构计算呢？