Calculate percentage of total columns based on total column in SSRS Matrix

8
votes

Looking to add a column in my SSRS Matrix which will give me the percentage from the total column in that row.

I'm using the following expression, but keep getting 100% for my percentages (I'm assuming this is because the total is evaluated last, so it's just doing Total/Total? 

=FORMAT((Fields!ID.Value/SUM(Fields!ID.Value)), "P")

The field ID is calcuted within SQL, not SSRS.

For example

Site   |  Value 1  |    %1   |   Value2  |   %2    |   Total
  1    |    20     |   50%   |    20     |   50%   |    40
2
matrixssrs-2008reporting-services
Are the values of "Value 1" and "Value 2" the same for example purposes or are they being fed by the same Fields!ID.Value? – KreepN

2 Answers

18
votes

Probably this is happening because you need define the right scope for the SUM function:

SUM(Fields!ID.Value,"group_name") instead of plain SUM(Fields!ID.Value)

Updated:

I needed some time to make an example since I didn't have reporting services available the first time I answered you.

You can see the result and the field values

enter image description here

enter image description here

7
votes

Hard to provide details without more info on the setup of your groups, but you should look at using the scope option to the aggregate operators like SUM or first:

=SUM(Fields!ID.Value, "NameOfRowGrouping") / SUM(Fields!ID.Value, "TopLevelGroupName")

Also, to keep things clean, you should move your format out of the expression and to either the placeholder properties or textbox properties that contains your value.