Search code examples
reporting-servicesssrs-2012ssrs-tablix

SSRS Add Row Percentage to Tablix

I am trying to add some kind of calculated item (Excel equivalent) into a tablix I have created from a dataset.

e.g.

Type       Today        Month       Annual
Sales      1,000        15,000      35,000
GP           200         1,500       5,000

I want to add a row below that will calculate the margin % and also be dynamic so when the figures change, so will the Margin %

e.g.

Type       Today        Month       Annual
Sales      1,000        15,000      35,000
GP           200         1,500       5,000
Margin %     20%           10%        14.3%
Solution

  • If this is in the footer, then you can conditionally sum on the field Type, for example for the Today column:

    =SUM(IIF(Fields!Type.Value = "GP", Fields!Today.Value, Nothing)) / SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, Nothing))

    Of course, if Sales can be zero then you will get divide-by-zero errors so you need to make the expression a bit more complicated to bypass the error-generating divide-by-zero calculation of the IIF function parameters:

    =IIF(SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, 0.0)) <> 0.0, 
SUM(IIF(Fields!Type.Value = "GP", Fields!Today.Value, Nothing)) / IIF(SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, 0.0)) = 0.0, 1.0, SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, Nothing))),
Nothing)