SSRS中使用Matrix控件展示矩阵类型的数据

上周本屌要做两个SSRS的报表,这两个报表与产品中其他的报表都不相同,因为它要求动态生成行和列的数据。

客户希望的报表大概是下面这个样子:

列中红线框起来的数据是根据Shoe表动态决定的,行中红线部分的数据是通过Inventory和Inventory两张表,关联查询EstWinLoss表动态生成的。蓝色的部分是固定的行,将当天各个Shoe的数据计算完后填入相应的区域。最底下的绿色部分是根据ShoeRating表,将相应的玩家信息填入。

这个东东看似很难实现,但借助SSRS的强大功能,实现起来并不困难。

首先M$的MSDN上有一个简单的例子,设计了一个如下(左)的报表,而在Visual Studio中设计时,仅用了右边的样式就能达到要求:

 

  

  实际上,SQL生成的是行数据。要将数据填到矩阵中,我们需要指定的行和列坐标,以及要填充的数据。很明显,上面这个报表中,数据集只要有三个字段就可以了:Category、Geography和LineTotal。接下来的报表生成工作就交给SSRS来完成。

很明显,M$的实现工程是这样的:SELECT DISTINCT Category来生成行,再SELECT DISTINCT Geography来生成列。然后在逐行扫描数据,根据这两个字段决定填入哪个格子中,再根据LineTotal字段填入具体的数据。SSRS还有一个很好用的Group功能。比如第一张图中左边有HKD和MOP两个大组,这时我们再加一个字段来“定位”,就好像三维坐标一样。

按照这个思路,我们首先来设计输出数据表的样式。

DECLARE @out TABLE( GameDate DATETIME NULL, CurrencyId NVARCHAR(20) NULL, Value MONEY NULL, ShoeNum INT NULL, Quantity MONEY NULL, EstWinLossId BIGINT, SortOrder SMALLINT NOT NULL)

我们使用GameDate、CurrencyId和Value对行数据进行分组,ShoeNum对列进行分组。Quantity是实际要显示的数据。EstWinLossId是别的查询要用到的字段,这里我们不管它。为什么要一个SortOrder呢?因为在显示报表的时候,我们要显示100K、50K的值,再按顺序显示Total、Fills等行,这个字段实际上是为了排序。如果没有的话,SSRS就会根据Group By的字段按照字段顺序显示。

这样,我们就设计出了如下的报表:

  无论是Tablix还是Matrix都有一个限制:只能从一个数据集中查找数据。

这样的话,为了在同一个表格中显示其他的信息,我们只能将这些数据使用SQL语句“拼接”起来。我们把Total、Fills等固定的行标签当作”CurrencyId”,通过UNION操作与其他查询的数据拼接起来,这样就能显示在一张表中。此时Value字段显然是没有作用的,我们填入0,并在表格中的表达式中填入如下内容,这样就可以把为0的这些值留空:

=IIf(CDbl(Fields!Value.Value) > 1e-6, Fields!Value.Value, String.Empty)

我们在显示绿色部分数据时字体样式和其他的行不一样,这样便于区分,那么SortOrder的作用就显现出来了。我们在CurrencyId的单元格属性中设置字体样式,表达式像下面那样:

=IIf(CInt(Fields!SortOrder.Value) >= 100, "Italic", "Normal")

这样SortOrder大于或等于100的就变成斜体了(加粗类似,这里省略)。

把SQL语句写好后,我们得到的数据是下面这个样子的:

  那么得到的报表是什么东东呢?这时奇迹发生了:

  效果还不错吧?(为空的单元格是为了测试在数据库中没有相应的数据故意留空的。)

那么我们还有最后一个问题,就是在填值的单元格中我们是Sum(Quantity)(M$的例子是Sum(LineTotal))。为什么一定要是Sum呢(如果不是就会分开显示)?因为得到的行数据可能有多行数据都在同一个“坐标”,这样就是显示它们的总和了。这一点很好用,在我做的另一个报表中不论选择一个桌子还是一个范围,都会自动计算出我想要的数据,而不用在SQL语句中自己计算了(会很麻烦,大家可以自己试一下)。

附:Matrix查询用的数据集的SQL语句:

USE [***_92_Xiaoyu]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [TMS].[Report_NewShoeReportSummary] 
        @locationId INT = 3,
        @fromGameDate DATETIME = '2013-03-05 00:00:00.000',
        @toGameDate DATETIME = '2013-03-05 00:00:00.000',
        @fromShoe BIGINT = 0,
        @toShoe BIGINT = 0
AS
BEGIN
  
DECLARE @out TABLE( GameDate DATETIME NULL, CurrencyId NVARCHAR(20) NULL, Value MONEY NULL, ShoeNum INT NULL, Quality MONEY NULL,
                    EstWinLossId BIGINT, SortOrder SMALLINT NOT NULL)
  
DECLARE @inv TABLE( GameDate DATETIME NULL, InventoryId BIGINT, ShoeNum INT NULL, [Current] MONEY NULL, Fill MONEY NULL,
                    Credit MONEY NULL, Total MONEY NULL, Need MONEY NULL, [Drop] MONEY NULL, WinLoss MONEY NULL, ShoeId BIGINT NULL)
  
INSERT INTO @inv
  
SELECT  e.GameDate, i.InventoryId, s.ShoeNum, i.Total AS [Current], e.Fill, e.Credit, (e.Opener + e.Fill - e.Credit) As Total,
        (i.Total - (e.Opener + e.Fill - e.Credit)) AS Need, (e.CardedDrop + e.UncardedDrop) AS [Drop], 
        (i.Total - (e.Opener + e.Fill - e.Credit) + e.CardedDrop + e.UncardedDrop) AS [WinLoss], s.ShoeId 
FROM [TMS].[Inventory] i
    INNER JOIN [TMS].[EstWinLoss] e
        ON e.GameDate = i.GameDate AND e.[Shift] = i.[Shift] AND e.LocationId = i.LocationId AND e.StatTime = i.StatTime
    INNER JOIN [TMS].[Shoe] s
        ON e.ShoeId = s.ShoeId
WHERE
    i.GameDate BETWEEN @fromGameDate AND @toGameDate
    AND i.LocationId = @locationId
    AND i.InventoryTypeId = 'SU'
    AND ((@fromShoe = 0 OR @fromShoe IS NULL) OR (s.ShoeNum BETWEEN @fromShoe AND @toShoe))
  
INSERT INTO @out
  
SELECT  e.GameDate, cs.CurrencyId + CHAR(10) + CHAR(13) + '(' + cs.[Description] + ')', d.Value, s.ShoeNum,
        (id.Quantity * d.Value) AS Quality, e.EstWinLossId, 10 AS SortOrder
FROM [TMS].[Inventory] i
    LEFT OUTER JOIN [TMS].[InventoryDetail] id
        ON i.InventoryId = id.InventoryId
    INNER JOIN [TMS].[Denomination] d
        ON id.DenominationId = d.DenominationId
    INNER JOIN [TMS].[ChipSet] cs
        ON cs.ChipSetId = d.ChipSetId 
    INNER JOIN [TMS].[EstWinLoss] e
        ON e.GameDate = i.GameDate AND e.[Shift] = i.[Shift] AND e.LocationId = i.LocationId AND e.StatTime = i.StatTime 
    INNER JOIN [TMS].[Shoe] s
        ON s.ShoeId = e.ShoeId
WHERE
    i.GameDate BETWEEN @fromGameDate AND @toGameDate
    AND i.LocationId = @locationId
    AND i.InventoryTypeId = 'SU'
    AND ((@fromShoe = 0 OR @fromShoe IS NULL) OR (s.ShoeNum BETWEEN @fromShoe AND @toShoe))
  
INSERT INTO @out
  
SELECT  s.GameDate, p.FirstName + ',' + p.LastName, 0, s.ShoeNum,
        r.WinLoss, 0, 100 AS SortOrder
FROM [TMS].[ShoeRating] sr
    INNER JOIN [TMS].[Rating] r
        ON sr.RatingId = r.RatingId
    INNER JOIN [TMS].[Shoe] s
        ON s.ShoeId = sr.ShoeId
    INNER JOIN [TMS].[Patron] p
        ON r.AccountId = p.AccountId
WHERE
    sr.ShoeId IN (SELECT DISTINCT ShoeId FROM @inv)
  
DECLARE @minShoeId INT = (SELECT TOP 1 ShoeId FROM @inv ORDER BY ShoeId ASC)
  
INSERT INTO @out
  
SELECT DISTINCT(GameDate), '', 0, @minShoeId, NULL, 0, 95
FROM @inv
UNION
SELECT DISTINCT(GameDate), 'Patrons',0, @minShoeId, NULL,0,98
FROM @inv
  
SELECT * FROM @out
UNION
SELECT it.GameDate, 'Current' AS CurrencyId, 0 AS Value, it.ShoeNum, it.[Current], 0, 20
FROM @inv it
UNION
SELECT it.GameDate, 'Fill' AS CurrencyId, 0 AS Value, it.ShoeNum, it.[Fill], 0, 30
FROM @inv it
UNION
SELECT it.GameDate, 'Credit' AS CurrencyId, 0 AS Value, it.ShoeNum, it.Credit, 0, 40
FROM @inv it
UNION
SELECT it.GameDate, 'Total' AS CurrencyId, 0 AS Value, it.ShoeNum, it.Total, 0, 50
FROM @inv it
UNION
SELECT it.GameDate, 'Need' AS CurrencyId, 0 AS Value, it.ShoeNum, it.Need, 0, 60
FROM @inv it
UNION
SELECT it.GameDate, 'Drop' AS CurrencyId, 0 AS Value, it.ShoeNum, it.[Drop], 0, 70
FROM @inv it
UNION
SELECT it.GameDate, 'WinLoss' AS CurrencyId, 0 AS Value, it.ShoeNum, it.WinLoss, 0, 80
FROM @inv it
ORDER BY GameDate ASC, SortOrder ASC
END

 

SSRS中使用Matrix控件展示矩阵类型的数据”的一个响应

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

You are commenting using your WordPress.com account. Log Out /  更改 )

Google+ photo

You are commenting using your Google+ account. Log Out /  更改 )

Twitter picture

You are commenting using your Twitter account. Log Out /  更改 )

Facebook photo

You are commenting using your Facebook account. Log Out /  更改 )

Connecting to %s