如何實現報表數據的動態層次鉆?。ㄒ唬?/h1>

發布時間：2020-08-10 15:07:03 來源：ITPUB博客 閱讀：196 作者：rqgxy 欄目：編程語言

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

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

  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