報表開發執行個體——動態多層次KPI鑽研報表（I），多層次kpi

報表開發執行個體——動態多層次KPI鑽研報表（I），多層次kpi

         在報表專案中有時會有動態層次報表，而且還需要層次切入的情境，開發難度較大。這裡記錄了使潤乾集算報表開發《各級部門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 treewhereconnect_by_isleaf=1START 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) vipkpi2from leaf left join kpi on leaf.id = kpi.idleft join tree on leaf.x=tree.idgroup 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。

         第四步：發布報表並運行。實際的超鏈值如：

         從上述實現步驟可以看到，難度最大的是tree.dfx中的A7單元格的複雜SQL。應該說，oracle資料庫的樹形遞迴查詢還是比較豐富的，用其他資料庫（比如MySQL,PostgreSQL及Greenplum等）可能就很難實現A7中的SQL。那麼有什麼其他方案，可以讓這些資料庫也能實現上述複雜的樹形結構計算呢？