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

发表于2013年3月15日2020年7月7日作者 daozhihun

上周本屌要做两个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