微软BI 之SSRS 系列

基于父子关系的递归结构在公司组织结构里比较常见，基本上都是在一张表里实现的自引用关系。在报表中如果要实现这种效果，并且在这个基础上做一些数据的汇总，可以使用到下面提到的方法。

要实现的效果大致如下 -

半收起的效果 -

从 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 博客推荐栏中快速看到这些文章。

---