业务数据中，经常遇到一类由上下级关系构成的树形结构的数据集，比如组织结构中的部门树、会计科目中的多级科目表、业务类别的多级分类层次等等。
在报表设计中，有两个问题比较典型：
（1）怎么在数据查询参数的下拉框中，以树形目录结构显示这样的层级数据？
（2）怎么在数据查询结果中，自动按照层级关系汇总业务数据？
以下以部门树为例，介绍这两个问题的处理方法。
（1）下拉框显示树形结构的数据
普通报表参数下拉框，只能以一览表的形式显示数据，对于树形结构的数据，看起来没有层次。如下图：
如果能以层次结构显示部门树，选择起来就很方便了。
原始数据表结构和数据记录如下图：
层次结构的核心，是【上级部门ID】字段，该字段引用的是本表的ID字段。
网上有关树形结构数据的处理方法，多以递归形式的CTE为主，例如：
with cte(Id,上级部门ID,部门名称,Level) as
(
select ID,上级部门ID,部门名称,0 as Level
from 部门
where id=5
union all
select h.ID,h.上级部门ID,h.部门名称,c.Level+1 as Level
from 部门 h
inner join cte c on h.上级部门ID=c.id
)
select * from cte
order by 上级部门ID
结果如下：
这个方法有两个问题：
一是需要一个查询条件，即上面SQL语句中的 where id=5，这是为了确定一个递归起点。如果存在多个平级的一级部门或分类，这会导致无法查出所有数据，除非一级部门只有一个节点，如【总公司】。
二是结果排序，依赖于ID字段值的数值大小。比如上面的结果中，【西北一部】本应跟在【西北大区】之后，但是按照上级部门ID排序，显示在【销售总部】之后了。即使按照其他字段来排序，也是无法将【西北大区】的下级部门都紧跟在其后的。
为了解决这两个问题，可以采用非递归的、多个CTE来解决。SQL语句如下：
with CTE1 as /* 这是一级部门，以此类推，下面的CTE6是六级部门 */
(
select 1 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID, ID ID1, null ID2, null ID3, null ID4, null ID5, null ID6 from 部门 T where [上级部门ID] is null
)
, CTE2 as
(
select 2 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID, CTE1.ID1, T.ID ID2, null ID3, null ID4, null ID5, null ID6 from 部门 T inner join CTE1 on T.[上级部门ID]=CTE1.ID
)
, CTE3 as
(
select 3 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID, CTE2.ID1,CTE2.ID2,T.ID ID3,null ID4,null ID5,null ID6 from 部门 T inner join CTE2 on T.[上级部门ID]=CTE2.ID
)
,CTE4 as
(
select 4 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID, CTE3.ID1,CTE3.ID2,CTE3.ID3,T.ID ID4,null ID5,null ID6 from 部门 T inner join CTE3 on T.[上级部门ID]=CTE3.ID
)
,CTE5 as
(
select 5 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID ,CTE4.ID1,CTE4.ID2,CTE4.ID3,CTE4.ID4,T.ID ID5,null ID6 from 部门 T inner join CTE4 on T.[上级部门ID]=CTE4.ID
)
,CTE6 as
(
select 6 Lvl, T.ID, T.部门编码, T.部门名称,T.上级部门ID, CTE5.ID1,CTE5.ID2,CTE5.ID3,CTE5.ID4,CTE5.ID5,T.ID ID6 from 部门 T inner join CTE5 on T.[上级部门ID]=CTE5.ID
)
,CTE_Levels as /* 这是将各个级别的数据合并成一个视图 */
(
select * from CTE1
union
select * from CTE2
union
select * from CTE3
union
select * from CTE4
union
select * from CTE5
union
select * from CTE6
)
,CTE_Path as /* 这是从上面视图得到每条记录的【全路径】ID和名称 */
(
select
L.ID1,T1.部门名称 名称1
,L.ID2,T2.部门名称 名称2
,L.ID3,T3.部门名称 名称3
,L.ID4,T4.部门名称 名称4
,L.ID5,T5.部门名称 名称5
,L.ID6,T6.部门名称 名称6
,L.ID
,L.部门名称 名称
,Lvl
,'-' + convert(nvarchar,ID1) + '-' + isnull(convert(nvarchar,ID2),'') + '-' + isnull(convert(nvarchar,ID3),'') + '-' + isnull(convert(nvarchar,ID4),'') + '-' + isnull(convert(nvarchar,ID5),'') + '-' + isnull(convert(nvarchar,ID6),'') + '-' IDPath
from CTE_Levels L
left join 部门 T1 on L.ID1=T1.ID
left join 部门 T2 on L.ID2=T2.ID
left join 部门 T3 on L.ID3=T3.ID
left join 部门 T4 on L.ID4=T4.ID
left join 部门 T5 on L.ID5=T5.ID
left join 部门 T6 on L.ID6=T6.ID
)
select *
,REPLICATE('__',lvl-1) + 名称 缩进名称
from CTE_Path
order by ID1,ID2,ID3,ID4,ID5,ID6
结果如下：
其中的IDPath是ID全路径，缩进名称是根据级别逐级缩进的名称。将缩进名称填列到报表参数下拉框中，即可实现期望的下拉框显示树形结构的效果，如下图：
（2）逐级汇总业务数据
部门或者其他树形结构的数据，往往会与某个业务数据表关联，比如销售业绩表。如下图：
其中，部门ID字段值是部门树中叶节点部门的ID，即只有最底层的部门才会出现在这个业务数据表中。但是在查询报表数据时，往往需要按照部门层级关系，层层汇总。
利用前面定义的那些CTE中的IDPath字段，可以轻松解决这个问题。
先定义一个关联部门与业绩数据的CTE：
,CTEx as
(
select T.ID,T.名称,T.IDPath,Lvl
,sum(M.销售业绩) 销售业绩
from CTE_Path T
inner join 部门业绩 M on M.部门ID in (select ID from CTE_Path where IDPath like '%-' + convert(nvarchar,T.ID) +'-%' )
group by T.ID,T.名称,T.IDPath,Lvl
)
注意：上述语句应紧跟前文定义CTE_Path的部分。
然后，查询任意一个部门ID的销售业绩：
select * from CTEx
where IDPath like ('%-' + convert(nvarchar, 7 ) +'-%') /* 其中的 7 是一个非底层部门的ID */
order by IDPath
如果去掉上述SQL语句中的where条件，将查出所有部门的销售业绩，如下图：
采用上述方法定义部门参数、查询销售业绩的统计表效果如下图：
附件是上述示例表的创建脚本（注释掉的部分）和查询语句。
