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

Question

Mostly I have a screenshot below my layout.

My matrix columns are DCG1 and DCG2.

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

So basically in the screenshots below. Screen 1 - matrix structure. I have a column group Test1, I want the first value of Test1 and the last value of test 1 and put this in the Red field.

In screenshot 2, you can see the values ​​that I want to compare. Table groups are called the same as columns + groups. So dcs1group / dcs2group

Ok, here is the DDL and DML for the data source

http://pastebin.com/1ySN701D

Pastebin has been removed. Why, I’m not sure that it is lower here.

 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

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

To make this a more general answer, I 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 meanings and that grp 1 exceeds the longer range for dt than grp 2 .

I 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 ​​for each row group.

Expression in a group of lines First TextBox:

 =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 gives my required results:

Note that the second row of grp has a narrower range than the first, but its first / last columns are independent for each group, so they are correct in each grp . There are a lot of things.

Custom code to aggregate the result of the LookupSet

The LookupSet expression LookupSet wrapped in a custom Code.SumLookup 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 to this SO question.

This assumes that each matrix cell can be the sum of several values, so it needs to be summed. LookupSet returns an array of values ​​that is aggregated by Code.SumLookup .

Details for LookupSet

Next, the LoopupSet expression 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 accepts the following parameters:

 LookupSet(source_expression, destination_expression, result_expression, dataset) 

In our expression, we want to get all the values ​​from DataSet1 that correspond to the first dt in the current grp .

For source_expression I use:

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

This gets the first dt in the string area ( "grp" is the name of the string group), and then adds this to the current grp. This creates an expression to match a similar expression when searching in DataSet1 .

i.e. destination_expression :

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

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

All matching Fields!val.Value in DataSet1 are built into an array using LookupSet , then aggregated using Code.SumLookup .

Update Expression for Recent Values

The expression for Last TextBox is almost 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 in them, simply subtract one expression from the other into the Difference TextBox or even refer to the ReportItems values:

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

Where Last and First are the names of the text fields.

Conclusion

Obviously, you will need to update your specific case, but you can make sure that it can be done.

Should I do this in my report? You can see that there are many steps, and in general it would be easier to refer when creating a DataSet. But, if this is not an option, we hope this LookupSet approach LookupSet useful.

Answer 2

AFAIK is not possible in SSRS. Believe me, I tried. Fortunately, you have an SQL data source, so I would allow this requirement where you have (almost) unlimited possibilities for generating and processing data.

For example, I would replace your final choice 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 the [Difference] column with a copy of [Total] for the 1st column and 0 - [Total] for the last column.

SQL could probably be made more efficient, but hopefully its simple use in CTE is easier to track.

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

By the way, your test data seems a little simplified - it will only generate 2 columns, and all cells have values. But its wonderful you posted DDL and DML - it simplified data and code extensions and tested it.