SSRS Matrix show difference between dynamic column groups

Question

==Edit in relation to answers==

Both answers provided give the required result just in separate ways.

@Hannover_Fist's answer displays better but is a little more rigid in its design, it currently works fine for a Saturday to Saturday report, but would need a edit per report if the recurring date span changed

@niktrs answer doesn't display as cleanly, even with a very very small top row, but has a bit more flexibility and does show the latest column compared to the latest minus 1 column with a little more flexability

==Edit in relation to answers==

I have an SSRS matrix that dynamically pulls from a database a list of values. The matrix represents each value as a row with a date stamp period as a different column. The DB table is populated by a stored procedure that runs a number of queries and snapshots this into a static table, on a weekly basis.

The report shows for each line the sum of records for each date stamp, and is ordered so the latest date stamp shows as the most current record. i.e.

Report	03/04/21	27/03/21	20/03/2021
Report1	1000	990	800
Report2	100	2000	2500

I'm wanting to add in a calculated column that appears to the left of the matrix element that shows the difference between the latest datestamp and the latest (minus 1) datestamp as below:

Report	Change	03/04/21	27/03/21	20/03/2021
Report1	+10	1000	990	800
Report2	-1900	100	2000	2500

This date stamp column would dynamically change each week, so would be comparing different columns every 7 days i.e.

Week 1 would be 03/04/2021 compared with 27/03/2021

Week 2 would be 10/04/2021 compared with 03/04/2021

Week 3 would be 17/04/2021 compared with 10/04/2021

I've looked at the following options, but these either allow me to look at the first and last column of the matrix, or presume that the groupings are static/remain static for a longer period of time that the 7 days that mine do.

how to subtract adjacent columns in an ssrs matrix

find the difference between two columns in an ssrs matrix

SSRS: Dynamic difference between two columns in MATRIX

I've not been able to find a solution that meets the requirement of comparing dynamic column groups, that change on a regular basis

This is fairly common question that never gets a satisfactory answer. If I needed to do this I would do it in the dataset query. If you show a sample of your current dataset output then that would be useful (does it have a week number for instance?) — Alan Schofield
Depending the query and the calculations, sometimes doing it on the database instead of the SSRS would create unnecessary overhead. On the otherhand, there are simple calculations that are hard to do directly on SSRS — niktrs
I can show you that my answer displays well with minimal margins. I left the upper row large to give you better understanding of my solution. I have also make the borders for the first row to look like one with the second. If you have a date parameter using expressions like Hannover did is much simpler but if you don't, you need workarounds to overcome SSRS limitations — niktrs

niktrs niktrs · Accepted Answer · 2021-04-07T08:22:56

You can use custom code to get the values and the difference of the first two columns.

I have created 2 functions:

SetVariance takes as parameter the field value. Internally it counts column number and store the first two values in the related variable.

GetVariance returns the value and resets the variables

Public Dim column_num As Integer 
Public Dim value1 As Integer
Public Dim value2 As Integer

Public Function SetVariance (v As Integer)

  column_num = column_num + 1
  If column_num = 1 Then value1 = v
  If column_num = 2 Then value2 = v
 
End Function

Public Function GetVariance() As Integer
  Dim var As Integer
  var = value1 - value2
  column_num = 0
  value1 = 0
  value2 = 0
  Return var
End Function

In order to make SetVariance work, you have to add one line before the group values (orange) and call use the function in the expression

=Code.SetVariance(Sum(Fields!Val.Value))

Set the line height as small as possible (you can't make it invisible otherwise calculation won't work)

For the change value (yellow cell) call the GetVariance function

Code.GetVariance()

SSRS Matrix show difference between dynamic column groups

2 Answers