微软BI 之SSRS 系列 - 使用 LookupSet 和 Adjacent Group 等高级技巧在报表中跨 Dataset 分组查询 - BIWORK

微软BI 之SSRS 系列 - 使用 LookupSet 和 Adjacent Group 等高级技巧在报表中跨 Dataset 分组查询

2014-03-24 21:28 BIWORK 阅读(4169) 评论(1) 编辑收藏举报

SSRS 报表中有一些高级的技巧，平常很少用到，下面我通过这个案例来展现一下如何在实际开发中使用它们，并且如何解决一些实际的需求。

这张报表分别统计了不同的 Product 产品在不同的月份的 Order 订单数量, Due 付款数量和 Ship 装船数量。

Start Date 和 End Date 的时间范围作为筛选，很显然第一个 Matrix 是以 Order Date 作为比较条件，第二个是以 Due Date, 第三是以 Ship Date 作为比较条件。

现在需要变成这样的一种需求 - 在一个 Matrix 中来展现按月分组之后的 Order Count, Due Count, Ship Count。

这个变化复杂在什么地方？

第一，无法确定到底是按照 Order Date, Due Date 还是 Ship Date 作为列的父分组，如果按照 Order Date 分组，那么 Due Count 和 Ship Count 下的数据又该如何统计。很显然订单日期在 2007年7月份的这笔订单，它的 Due Date 有可能落在 2007年8月份，它的 Ship Date 有可能落在 2007年9月。但是在一个 Dataset 中只能要么按照 Order Date，要么按照 Due Date 或者 Ship Date 来过滤。因此既不能按照 Order Date，也不能按照 Due Date 或者 Ship Date 来作为查询的 WHERE 条件。

第二，在月份的分组之下没有条件能够判断那些数据是属于 Order Count, Due Count 和 Ship Count。月份之下的分组没有依据，无法分组。

当然解决以上两个问题在 SQL 查询中是比较容易解决的，就是通过硬编码将三个查询出来的 Dataset 合并成一个更大的 Dataset 并设置列标识。

但是现在的问题是，这三个 Dataset 已经都存在了，并且我们假设是没有办法改变的情况下如何解决这个问题，比如数据源使用的是无法通过 SQL 查询的 Report Model 的 Entity。

三个 Dataset 的示例查询语句，可以自行替换日期参数。

USE AdventureWorksDW2008R2
GO

SELECT DP.EnglishProductName,
       F.SalesOrderNumber,
       DD.FullDateAlternateKey AS 'OrderDate'FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS DP
ON F.ProductKey = DP.ProductKey
INNER JOIN dbo.DimDate AS DD
ON F.OrderDateKey = DD.DateKey
WHERE DD.FullDateAlternateKey BETWEEN '2007-01-01' AND '2007-09-30'AND DP.EnglishProductName IN ('Adjustable Race',
'All-Purpose Bike Stand',
'AWC Logo Cap',
'BB Ball Bearing',
'Bearing Ball')

SELECT DP.EnglishProductName,
       F.SalesOrderNumber,
       DD.FullDateAlternateKey AS 'DueDate'FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS DP
ON F.ProductKey = DP.ProductKey
INNER JOIN dbo.DimDate AS DD
ON F.DueDateKey = DD.DateKey
WHERE DD.FullDateAlternateKey BETWEEN '2007-01-01' AND '2007-09-30'AND DP.EnglishProductName IN ('Adjustable Race',
'All-Purpose Bike Stand',
'AWC Logo Cap',
'BB Ball Bearing',
'Bearing Ball')

SELECT DP.EnglishProductName,
       F.SalesOrderNumber,
       DD.FullDateAlternateKey AS 'ShipDate'FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS DP
ON F.ProductKey = DP.ProductKey
INNER JOIN dbo.DimDate AS DD
ON F.ShipDateKey = DD.DateKey
WHERE DD.FullDateAlternateKey BETWEEN '2007-01-01' AND '2007-09-30'AND DP.EnglishProductName IN ('Adjustable Race',
'All-Purpose Bike Stand',
'AWC Logo Cap',
'BB Ball Bearing',
'Bearing Ball')