如何實現報表數據的動態層次鉆取（一）

在報表項目中有時會遇到進行動態層次鉆取的需求，這種報表的開發難度一般都較大。而潤乾報表的實現則相對簡便很多。下面就以《各級部門 KPI 報表》為例，講解潤乾報表（需要結合集算器實現）實現此類報表的過程。

《各級部門 KPI 報表》初始狀態如下圖：

當前節點是根節點“河北省”，要求報表顯示當前節點的下一級節點“地市”，以及匯總的 KPI 數值。Kpi 又分為普通指標和 VIP 指標兩類，共四項。當點擊“石家莊”進行鉆取的時候，要求顯示石家莊下一級（區縣）的 KPI 匯總指標，如下圖：

再點擊“中心區”進行鉆取，要求顯示下一級（營業部）的 KPI 匯總指標。以此類推，直到顯示到最后一級。如下圖：

前四級固定是“省、地市、區縣、營業部”，后邊則是動態的“架構 4、架構 5、架構 6. . . 架構 13”（根節點“省”對應“架構 0”）。

這個報表對應的 oracle 數據庫表有兩個，Tree（樹形結構維表）和 kpi（指標事實表）。其中 Tree 表的葉子節點，通過 id 字段與 kpi 表關聯，數據示例如下圖：

                           Tree表

Tree表

                           Kpi表

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 進行計算，得到當前節點的下一級節點，形成臨時表 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。那么有什么辦法可以讓這些數據庫也能實現上述復雜的樹形結構計算呢？

在后續的文章 《如何實現報表數據的動態層次鉆取（二）》 中，我們將介紹如何利用潤乾報表內置集算引擎的強大計算能力來解決這個問題。

詳情鏈接： http://c.raqsoft.com.cn/article/1554694583352?r=gxy