SSRS How to get the first and last values of a matrix row group?

matrix reporting-services ssrs-2012
user222427 picture user222427 · Jan 15, 2015 · Viewed 17.2k times · Source

I basically have the screenshot below as my layout.

My matrix columns are DCG1 and DCG2.

At the end of the Matrix I have a Total Group, which works just find. But I want to find the difference between my first and last value of the group. I've tried everything from ReportItems! to values. I cannot get SSRS to recognize these values.

So basically in the screen shots below. Screen shot 1 is the matrix structure. I have a column group called Test1, I want the first Value of Test1 and the last Value of Test 1 and place that in the Red box.

In screenshot 2, you can see the values i want to compare. The table groupings are named the same as the column + group. So dcs1group/dcs2group

enter image description here

enter image description here

Okay here is the DDL and DML for the datasource

http://pastebin.com/1ySN701D

The pastebin has been removed. Why, not sure so here it is below.

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

Ian Preston picture Ian Preston · Jan 22, 2015

Yes, this is possible, but as you can see it is a bit complicated.

To make this a more generic answer, I have created my own DataSet, with simplified columns but more data:

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)

enter image description here

Note that each grp / dt combination has two values, and that grp 1 is over a longer range for dt than grp 2.

I have created a simple Matrix based on this:

enter image description here

Since you are using SQL Server 2012, you can use the LookupSet function to get the First/Last values per row group.

The expression in the First row group TextBox is:

=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"
    )
)

Based on my sample data, this is giving my required results:

enter image description here

Note that the second grp row has a narrower range than the first, but its first/last columns are independent for each group so are correct within each grp. There are quite a few things going on here.

Custom code for aggregation of LookUpSet result

The LookupSet expression is wrapped in a Code.SumLookup custom function:

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 is taken from the answer at this SO question.

This assumes that each matrix cell can be the sum of multiple values, so this needs to be summed up. LookupSet returns an array of values, which is aggregated by Code.SumLookup.

Details for LookupSet

Next, the LoopupSet expression itself:

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

LookupSet takes the following parameters:

LookupSet(source_expression, destination_expression, result_expression, dataset)

In our expression, we want get all values from DataSet1 that match the first dt in the current grp scope.

For source_expression I use:

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

This gets the first dt in the row scope ("grp" is the name of the row group), then appends this to the current grp. This creates an expression to match to a similar expression when seeking in DataSet1.

i.e. destination_expression:

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

Finally, we specify we want Fields!val.Value as the result_expression and DataSet1 as the dataset parameter.

All matching Fields!val.Value values in DataSet1 are constructed into an array by LookupSet, then aggregated by Code.SumLookup.

Update expression for Last values

The expression for the Last TextBox is practically the same; just change First to 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"
    )
)

Get the Difference

Finally, to get the difference of these, simply subtract one expression from the other in the Difference TextBox, or even reference the ReportItems values:

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

Where Last and First are the names of the TextBoxes.

Conclusion

Obviously you will need to update for your specific case, but you can see that this can be done.

Is it worth doing this in your report? You can see there are many steps involved, and in general would be easier to address when generating the DataSet. But, if that is not an option hopefully this LookupSet approach is useful.

Related questions

How to specify an order for the columns in a matrix?

I'm working on a SQL Reporting Services report (in VS.Net 2005) which displays a count of different data in a matrix. The columns have a count on the amount of customers in a certain set. So I have several columns …

Read More
SSRS - OutOf MemoryException - is there a limit to the number of rows that can be displayed

I have created an rdl doc that points at a proc that returns 90 000 rows and I am getting an out of memory exception. Is there a limit to how many rows the report projects can handle? Currently I have changed …

Read More
How to define a two-dimensional array?

I want to define a two-dimensional array without an initialized length like this: Matrix = [][] but it does not work... I've tried the code below, but it is wrong too: Matrix = [5][5] Error: Traceback ... IndexError: list index out of range What is …

Read More