8

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
Tom
  • 2,180
  • 7
  • 30
  • 48
  • 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 Sep 26 '11 at 20:28

2 Answers2

19

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

niktrs
  • 9,858
  • 1
  • 30
  • 30
  • 1
    This seems like it should be the correct sollution; however, when you add a totals column, it does not get assigned a group name. This makes it not callable. – Tom Sep 26 '11 at 17:24
8

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.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • Thanks - this worked when I the lowest-level group name e.g. `matrix1_SiteName` which was the total value of the row. – PeterX Feb 02 '15 at 03:05