matrix - SSRS如何获取矩阵行组的第一个和最后一个值？

Question

我基本上将下面的屏幕截图作为我的布局。

我的矩阵列是 DCG1 和 DCG2。

在矩阵的末尾，我有一个总组，只需查找即可。但我想找到该组的第一个值和最后一个值之间的差异。我已经尝试了 ReportItems 中的所有内容!到值(value)观。我无法让 SSRS 识别这些值。

所以基本上在下面的屏幕截图中。屏幕截图 1 是矩阵结构。我有一个名为 Test1 的列组，我想要 Test1 的第一个值和 Test 1 的最后一个值，并将其放在红色框中。

在屏幕截图 2 中，您可以看到我想要比较的值。表分组的命名与列+组的命名相同。所以 dcs1group/dcs2group

好的，这是数据源的 DDL 和 DML

http://pastebin.com/1ySN701D

pastebin 已被删除。为什么，不确定，所以在下面。

IF EXISTS      (SELECT [name]         FROM tempdb.sys.tables        WHERE [name] LIKE '%tmpHoldingTable%')   BEGIN      DROP TABLE #tmpHoldingTable;   END;CREATE TABLE #tmpHoldingTable(   dcs1    NVARCHAR (50),   dcs2    NVARCHAR (50),   Total   DECIMAL (10, 2),   Test1   NVARCHAR (50))INSERT INTO #tmpHoldingTable (dcs1,                              dcs2,                              Total,                              Test1)VALUES ('Contract',        'Breach of Contract',        500.00,        '01/01/2013-12/31/2013'),       ('Contract',        'Breach of Contract',        300.00,        '01/01/2014-12/31/2014'),       ('Employment',        'Discrimination',        500.00,        '01/01/2013-12/31/2013'),       ('Employment',        'Discrimination',        300.00,        '01/01/2014-12/31/2014'),       ('Employment',        'Research',        500.00,        '01/01/2013-12/31/2013'),       ('Employment',        'Research',        300.00,        '01/01/2014-12/31/2014')SELECT * FROM #tmpHoldingTable;

Answer 1

是的，这是可能的，但正如您所看到的，它有点复杂。

为了使这个答案更加通用，我创建了自己的数据集，其中包含简化的列，但数据更多:

select grp = 1, val = 100, dt = cast('01-jan-2015' as date)
union all select grp = 1, val = 110, dt = cast('01-jan-2015' as date)
union all select grp = 1, val = 200, dt = cast('02-jan-2015' as date)
union all select grp = 1, val = 210, dt = cast('02-jan-2015' as date)
union all select grp = 1, val = 300, dt = cast('03-jan-2015' as date)
union all select grp = 1, val = 310, dt = cast('03-jan-2015' as date)
union all select grp = 1, val = 400, dt = cast('04-jan-2015' as date)
union all select grp = 1, val = 410, dt = cast('04-jan-2015' as date)
union all select grp = 1, val = 500, dt = cast('05-jan-2015' as date)
union all select grp = 1, val = 510, dt = cast('05-jan-2015' as date)
union all select grp = 2, val = 220, dt = cast('02-jan-2015' as date)
union all select grp = 2, val = 230, dt = cast('02-jan-2015' as date)
union all select grp = 2, val = 320, dt = cast('03-jan-2015' as date)
union all select grp = 2, val = 330, dt = cast('03-jan-2015' as date)
union all select grp = 2, val = 420, dt = cast('04-jan-2015' as date)
union all select grp = 2, val = 430, dt = cast('04-jan-2015' as date)

请注意，每个 grp/dt 组合都有两个值，并且 grp 1 对于 dt< 的范围较长/em> 大于 grp 2。

我基于此创建了一个简单的矩阵:

由于您使用的是 SQL Server 2012，因此可以使用 LookupSet 函数获取每个行组的第一个/最后一个值。

First行组文本框中的表达式为:

=Code.SumLookup(
    LookupSet(
        First(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString
        , Fields!dt.Value.ToString & Fields!grp.Value.ToString
        , Fields!val.Value
        , "DataSet1"
    )
)

根据我的示例数据，这给出了我所需的结果:

请注意，第二个 grp 行的范围比第一行窄，但其第一列/最后一列对于每个组都是独立的，因此在每个 grp 内都是正确的。这里发生了很多事情。

用于聚合 LookUpSet 结果的自定义代码

LookupSet 表达式包装在 Code.SumLookup 自定义函数中:

Function SumLookup(ByVal items As Object()) As Decimal
  If items Is Nothing Then
    Return Nothing
  End If

  Dim suma As Decimal = New Decimal()
  suma = 0

  For Each item As Object In items
    suma += Convert.ToDecimal(item)
  Next

  Return suma
End Function

这取自 this 的答案所以问题。

这假设每个矩阵单元可以是多个值的总和，因此需要求和。 LookupSet 返回一个值数组，该数组由 Code.SumLookup 聚合。

LookupSet的详细信息

接下来，LoopupSet 表达式本身:

    LookupSet(
        First(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString
        , Fields!dt.Value.ToString & Fields!grp.Value.ToString
        , Fields!val.Value
        , "DataSet1"
    )

LookupSet 采用以下参数:

LookupSet(source_expression, destination_expression, result_expression, dataset)

在我们的表达式中，我们希望从 DataSet1 获取与当前 grp 范围中的第一个 dt 匹配的所有值。

对于 source_expression 我使用:

First(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString

这将获取行范围中的第一个 dt("grp" 是行组的名称)，然后将其附加到当前的 grp。这会创建一个表达式，以便在 DataSet1 中查找时与类似的表达式相匹配。

即destination_expression:

Fields!dt.Value.ToString & Fields!grp.Value.ToString

最后，我们指定希望 Fields!val.Value 作为 result_expression 和 DataSet1 作为 dataset参数。

DataSet1 中所有匹配的 Fields!val.Value 值均由 LookupSet 构造成数组，然后由 Code 聚合。 SumLookup.

更新最后值的表达式

Last TextBox 的表达式实际上是相同的；只需将 First 更改为 Last:

=Code.SumLookup(
    LookupSet(
        Last(Fields!dt.Value, "grp").ToString & Fields!grp.Value.ToString
        , Fields!dt.Value.ToString & Fields!grp.Value.ToString
        , Fields!val.Value
        , "DataSet1"
    )
)

了解差异

最后，要获得这些差异，只需在差异文本框中将一个表达式与另一个表达式相减，甚至引用ReportItems值即可:

=ReportItems!Last.Value - ReportItems!First.Value

其中 Last 和 First 是文本框的名称。

结论

显然，您需要针对您的具体情况进行更新，但您可以看到这是可以完成的。

值得在您的报告中这样做吗？您可以看到涉及很多步骤，通常在生成数据集时更容易解决。但是，如果这不是一个选项，希望这个 LookupSet 方法有用。