Search code examples
reporting-servicesssrs-tablixssrs-2017

How to display row group and grand total percentages on a matrix

enter image description here

I have an SSRS matrix report that i have so far managed to format as above. As you can see i have managed to add the totals by "Area" broken down by "Section"

I have also added the grand total row at the very bottom and all is fine up-to this point.

Now my questions:

  1. How can i add the percentage contribution of each "Section" towards the total percentage (100%). See the red row in the screenshot below, in this case that's what i want to add.
  2. How can i add the percentage contribution of each "Area" towards the total percentage. See the "Total (%) by Area" column in red in the below screenshot.

Does any body have an idea of how i can achieve the above.

I want the final matrix to look like the screenshot below.

enter image description here

Solution

  • Whenever you want to do a calculation for a group in a table, you can use an aggregate expression with the scope of the Group Name.

    So you would use the Group Name that is creating the Section and Area groups. 

    =SUM(Fields!COUNT_FIELD.Value) / SUM(Fields!COUNT_FIELD.Value, "SECTION")


=SUM(Fields!COUNT_FIELD.Value) / SUM(Fields!COUNT_FIELD.Value, "AREA")