微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

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

要实现的效果大致如下 -

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

半收起的效果 -

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

从 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 查询的方式实现级别的判断,可以看到它们的级别,那么这种级别也应该能反映到报表中。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

当然在报表中不需要这么复杂的查询语句,报表中的 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 并填写几个基本的字段 -

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

选择 Group Properties 并注意这里的 Group 名称就叫做 Details。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

基本行是按照 EmployeeKey 分组的。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

这里选择递归的父对象是 ParentEmployeeKey 指向父级成员。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

最后可以设置按照 FullName 的点击来完成收缩或者展开效果。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

保存并预览报表,已经有一个雏形了。但是还有两个问题需要解决:第一个就是 Sales Amount 的聚合,这里和以往有所区别。第二个就是层次结构的缩进问题,以前的层次结构是固定的,所以我们可以手动敲空格,但是这次碰到的是一个不固定的层次结构。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

在 Sales Amount 里添加表达式,注意这里的 Details 是上面 Group 的名称,有很多人效果出不来是因为习惯性的将这里写成了 Dataset 的名称 DS_ParentChild。

 =SUM(Fields!SalesAmount.Value,"Details",Recursive)

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

缩进的问题,可以通过在 FullName 的Padding属性中通过 Level() 内置函数来定义左边的 Padding 间距: =CStr(2 + (Level()*20)) + "pt"。有兴趣的话,可以直接使用 Level() 内置函数看看它在 Group 中的级别。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

当然也可以使用这个 Level() 来控制 Sales Amount 的缩进,并且应该在Group Properties 里面设置一下 Sort 排序,可以按照 Full Name 来排序,最后保存美化之后的效果就是这样的。

微软BI 之SSRS 系列 - 使用分组 Group 属性实现基于父子递归关系的汇总报表

下次再来看在 Cube 中通过 MDX 查询来实现这种父子递归效果。


更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)

如果觉得这篇文章看了对您有帮助,请帮助推荐,以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。


上一篇:Json.Net组件指定/忽略序列化字段属性技巧知识点


下一篇:unity, public+[HideInInspector] vs private