基于父子关系的递归结构在公司组织结构里比较常见，基本上都是在一张表里实现的自引用关系。在报表中如果要实现这种效果，并且在这个基础上做一些数据的汇总，可以使用到下面提到的方法。
要实现的效果大致如下 -
半收起的效果 -
从 AdventureWorks2012 中抽取一些示例数据 -
USE BIWORK_SSIS
GO IF OBJECT_ID('DimEmployee','U') IS NOT NULL
DROP TABLE DimEmployee
GO IF OBJECT_ID('FactResellerSales','U') IS NOT NULL
DROP TABLE FactResellerSales
GO SELECT EmployeeKey,
ParentEmployeeKey,
EmployeeNationalIDAlternateKey,
FirstName + ' '+ LastName AS FullName,
Title
INTO DimEmployee
FROM AdventureWorksDW2012.dbo.DimEmployee SELECT ProductKey,
OrderDateKey,
EmployeeKey,
SalesOrderLineNumber,
SalesOrderNumber,
UnitPrice,
ProductStandardCost,
SalesAmount
INTO FactResellerSales
FROM AdventureWorksDW2012.dbo.FactResellerSales
我们可以做一个简单的 CTE 递归查询，可以看到这些 Employee 的级别 Level
WITH Employees AS
(
SELECT EmployeeKey,
ParentEmployeeKey,
FullName,
Title,
1 AS EmployeeLevel
FROM DimEmployee
WHERE ParentEmployeeKey IS NULL
UNION ALL
SELECT e.EmployeeKey,
e.ParentEmployeeKey,
e.FullName,
e.Title,
es.EmployeeLevel + 1 AS EmployeeLevel
FROM DimEmployee AS e
INNER JOIN Employees AS es
ON es.EmployeeKey = e.ParentEmployeeKey
)
SELECT *
FROM Employees
ORDER BY EmployeeLevel,FullName
这是我们通过 SQL 查询的方式实现级别的判断，可以看到它们的级别，那么这种级别也应该能反映到报表中。
当然在报表中不需要这么复杂的查询语句，报表中的 Dataset 只需要提供正确的父子键，也就是上面看到的 EmployeeKey 和 ParentEmployeeKey 即可。在报表中只需要简单的设置就可以实现这种父子递归汇总的效果，新建一个报表并创建好数据源，使用下面查询创建一个 Dataset - DS_ParentChild。
SELECT es.*,
fact.SalesOrderNumber,
fact.UnitPrice,
fact.SalesAmount
FROM DimEmployee AS es
LEFT JOIN FactResellerSales as fact
ON es.EmployeeKey = fact.EmployeeKey
拖放一个 Table 并填写几个基本的字段 -
选择 Group Properties 并注意这里的 Group 名称就叫做 Details。
基本行是按照 EmployeeKey 分组的。
这里选择递归的父对象是 ParentEmployeeKey 指向父级成员。
最后可以设置按照 FullName 的点击来完成收缩或者展开效果。
保存并预览报表，已经有一个雏形了。但是还有两个问题需要解决：第一个就是 Sales Amount 的聚合，这里和以往有所区别。第二个就是层次结构的缩进问题，以前的层次结构是固定的，所以我们可以手动敲空格，但是这次碰到的是一个不固定的层次结构。
在 Sales Amount 里添加表达式，注意这里的 Details 是上面 Group 的名称，有很多人效果出不来是因为习惯性的将这里写成了 Dataset 的名称 DS_ParentChild。
=SUM(Fields!SalesAmount.Value,"Details",Recursive)
缩进的问题，可以通过在 FullName 的Padding属性中通过 Level() 内置函数来定义左边的 Padding 间距: =CStr(2 + (Level()*20)) + "pt"。有兴趣的话，可以直接使用 Level() 内置函数看看它在 Group 中的级别。
当然也可以使用这个 Level() 来控制 Sales Amount 的缩进，并且应该在Group Properties 里面设置一下 Sort 排序，可以按照 Full Name 来排序，最后保存美化之后的效果就是这样的。
下次再来看在 Cube 中通过 MDX 查询来实现这种父子递归效果。
更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)
如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。