分组聚合的展开和收起效果在SSRS Report中非常常用,并且有时还要处理一些比较特别的情况。比如分组合并时有的层次结构是不规则的,有的组有两层,遇到这种情况应该如何处理?
注意到下面的这个需求,如果 France 下面没有其它的子层级,就不显示 + 号,如果 United States - Utah/Minnesota 州没有城市的子层次那么它们也不显示 + 号.
这样的需求在 SSRS Report 中会偶尔碰到,可以理解为如何处理非对称层次结构中的显示和隐藏问题.
下面展示的技巧先回顾了如何垂直分组,使用的是Table这个控件,而没有使用Tablix. 最后展现了在没有子层次结构的时候如何不显示 + 号,以免给用户以误导做出无谓的点击。
使用的Demo数据库是 AdventureWorks2012, 为了展示不规则层次带来的问题,把Dataset中的SQL给故意写成这样-
SELECT CountryRegionName,
CASE WHEN CountryRegionName = 'France' THEN ''
ELSE StateProvinceName END AS StateProvinceName,
CASE WHEN CountryRegionName = 'France' THEN ''
WHEN City = 'Nevada' THEN ''
WHEN City = 'Duluth' THEN ''
ELSE City END AS City,
SalesQuota,
SalesYTD,
SalesLastYear
FROM Sales.vSalesPerson
WHERE CountryRegionName IN ('United States','United Kingdom','France')
下面开始先创建一个报表,以及添加DataSource, DataSet和 Table控件并填充数据
预览一下看看,France 这个国家下面没有身份和城市信息, United States 的 Utah 和 Minnesota 州下面没有 City 信息, 我们要基于 国家,州和城市来分组聚合.
选中行,右键来为这一行添加一个 Parent Group
虽然,先按城市分组 (最后这个分组会被去掉的,目前只是为了实现这个效果),添加一个 Group Header
添加完之后,多出一行,是以 City 分组之后的组行. 继续基于这个组添加新的 Group.
城市上面的分组是州,添加 Group Header
同样的给州组添加一个Group-国家, 添加完之后可以看到 -
上左部分是基于原始行添加的 Group 分组, 上右部分是原始行的信息,最下方也显示了它们之间的层次结构, 但这样展示出来的数据是不好看的,需要调整.
调整的方法就是将左边 Group 显示的内容添加到与原始行数据垂直的位置,这里选择的是 City, 那么分组后的层次结构显示的就是一个垂直的层次结构.
其它的列都可以删除了
删除之后简洁很多
下面要做的事情就是分组的展开和收起效果了,选中分组的 City, 注意最后一行 =[City] 它们只是原始行,这里要选中的是组 City.
右键 Row Visibility, 默认这个组隐藏,并且由 StateProvinceName2 来控制显示或隐藏.
在 SSRS Report 中, 只要设置了这个属性,那么控件分组组件(例如这里的StateProvinceName2)就会在它自己前面显示 + 或者 - 符号.
同样选中 StateProvinceName 行,修改它的 Row Visibility, 它受上一层单元格控件.
因为有时同样的名字出现了很多次,所以要知道如何找到对应的单元格-选中单元格看属性.
这里要注意了,等到各个分组的 Row Visibility 设置完了之后才可以做这一步的操作,否则效果不出来. (在这里要多说一句,可能不同的版本之间操作顺序稍微有一点区别,我记得之前用其它的版本感觉可以先删除后修改 Row Visibility,但现在无法一一验证了。我目前使用的版本是 VS2012 + SSDT 插件,关于如何集成 SSDT 插件到 VS2012中,请参考我的另一篇文章 - 在 Visual Studio 2012 开发 SSIS,SSAS,SSRS BI 项目)。
删除的时候会提示“删除组和关系”? 删除!
删除完成后,原始行信息其实包含在 City 组内了。
调整一下格式,并添加要聚合的数据以及修改一下显示的数据格式。
预览一下,感觉还不错。
展开之后,其实就可以看到我们已经实现了分层浏览的效果,如果每一个层次都有数据的话,其实这个显示和隐藏的效果就算是完成了。
在这里可以看到,France 下面并没有其它的层级,那么就不应该显示 + 或者 - 号。
可能有这样的一个思路,判断一下下面有没有数据,如果没有数据就不展开,这个可以实现,但是 + 号是无法去掉的。数据可以不展示,但是+仍然存在。
由于基于这个例子来演示如何控制不显示 + 的步骤,所以顺着这个例子要做一些修改,不重头开始做。
先重新添加一个组,因为要先基于组来修改 Rows Visibility 后删除行.
把 City 组给添加回来,即 Group By City.
由于 Rows Visibility 里有一个特性,就是这一组数据被哪一个元素来控制,那么那个元素就会显示 + 或 - ,这样的话我们在第一列之前添加一个空白列。
修改第一行,取一个名字叫做 A,第二行叫做 B,后面的显示和隐藏是通过 A 和 B 来实现的。
这样重新来修改 City 组的显示和隐藏属性,注意是基于组。
City 组基于 B 显示或隐藏。
StateProvinceName 基于 A 显示或隐藏。
等这一步操作完了之后,接着又是像前面的操作一样,删除细节行,注意不是删除组行。
既然显示和隐藏都设置完了,所以这里的组和行的关系可以一起删除了。
删除之后的效果
预览一下,发现还是这样的显示,原因是因为还需要判断子层次有没有数据。
分别修改 A 和 B 的属性。
如果是 A 的话,就要判断 State 州元素存在不存在,如果是 B 的话,就要判断 City 有没有。
=IIF(Fields!StateProvinceName.Value="",True,False) 这是A显示或隐藏的判断。
这时看能不能明白为什么要单独加一个列来控制显示和隐藏,因为如果把这个判断写在数据列的话,那么当子层没有数据的时候,它自身就隐藏了,那么就丢数据了。
预览一下,已经实现了。
调整一下,把第一列的颜色给拿掉,让显示更好看一些,这样就实现了我们要的功能。
OK!写完了!可以睡觉了!
更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)
如果觉得这篇文章看了对您有帮助,请帮助推荐,以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。