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

Question

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

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;

Solution 1

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)

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:

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:

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.

Solution 2

AFAIK this is not possible in SSRS alone. Believe me I've tried. Fortunately you have a SQL datasource so I would resolve this requirement there where you have (almost) unlimited powers to shape and manipulate data.

For example I would replace your final select with:

; WITH CTE_Base AS ( SELECT * FROM #tmpHoldingTable ) , CTE_Test1 AS ( SELECT Test1 , ROW_NUMBER () OVER ( ORDER BY Test1 ) AS Column_Number_Test1 FROM CTE_Base GROUP BY Test1 ) SELECT CTE_Base.* , CTE_Test1.Column_Number_Test1 , CASE WHEN CTE_Test1.Column_Number_Test1 = 1 THEN Total WHEN CTE_Test1.Column_Number_Test1 = ( SELECT MAX ( Column_Number_Test1 ) FROM CTE_Test1 ) THEN 0 - Total ELSE 0 END AS [Difference] FROM CTE_Base INNER JOIN CTE_Test1 ON CTE_Base.Test1 = CTE_Test1.Test1

This adds a [Difference] column with a copy of [Total] for the 1st column and 0 - [Total] for the last column.

The SQL could probably be made more efficient, but hopefully breaking it into CTEs is easier to follow.

Then in the SSRS Designer you can add a [Difference] column outside the [Test1] column group and let it sum (default).

BTW your test data seems a bit simplistic - it will only produce 2 columns and all cells have values. But its great you posted DDL & DML - it made it easy to extend the data and code and test this.

Comments

[Mahesh]

Did you tried using the First(Fields!yourfield.value,"yourgroup") and Last(Fields!yourfield.value,"yourgroup")

[Jeroen]

It would be good if you help us reproduce your scenario. Add some basic DDL and DML to set up sample tables that include problematic cases. In addition, some info on what the dataset and groupings for your tablixes would also help.