Get a row group and column group value in an SSRS Matrix detail cell

1
votes

I have a matrix in an SSRS report, where the column group and the row group are based on the same dataset. So the same values appear in the column and row headers. The dataset includes a percentage value and I want to show the difference between the row and column percentage values in the detail cells. How do I get the percentage values for the column and row group to calculate the difference?

For example, If the data looks like this:
Group 1, 30%
Group 2, 5%
Group 3, 10%

Then here is what I want to turn it into. I want to show the difference between the percentage of the column and row values in the detail cell.

               Group 1 (30%)         Group 2 (5%)          Group 3 (10%)
Group 1
(30%)             n/a                          25%                              20%
Group 2
(5%)             25%                          n/a                               5%
Group 3
(10%)             20%                        5%                              n/a

1
reporting-servicesssrs-tablix
Can you add a few rows of sample data and your expected result for this data? – Ian Preston
Is your row data value an aggregate of your column values? – Ross Bush
Is your data source SQL? I imagine you'll need to feed SSRS a dataset with all the combinations to achieve your requirement - I'm thinking a SQL CTE with a CROSS JOIN to itself. – Mike Honey

1 Answers

1
votes

I was able to get this to work within the SSRS report by creating a cell in both the row and column headers for the percentage associated with each group value. I set the Name property for these two cells to RowPercentage and ColumnPercentage. In the detail cell expression I calculated the value this way:

=Abs(ReportItems!ColumnPercentage.Value - ReportItems!ColumnPercentage.Value)