Search code examples
reporting-servicesssrs-2008ssrs-2008-r2ssrs-2012ssrs-tablix

SSRS RunningValue contained within another RunningValue

I have a SSRS report that shows the waste minimised/disposed by a city for a given time period. The report looks like this:

enter image description here

I am using the RunningValue() function to determine the 'Cumalative Quarterly' totals e.g. the cumulative quarterly total for 'Waste Generated':

=RunningValue((Fields!WasteGenerated.Value), SUM, "WasteYear")

The 'Waste Diverted' is calculated with the following formula:

=((Fields!WasteMinimised.Value / Fields!WasteGenerated.Value))

And the 'Quarterly Waste Diverted' is still straight forward as:

=(Sum(Fields!WasteMinimised.Value) / Sum(Fields!WasteGenerated.Value))

Next I want to calculate the "Cumulative Quarterly Waste Diverted" based on the RunningValue() of 'Waste Minimised' and 'Waste Generated':

enter image description here

enter image description here

I tried this by having nested RunningValue() functions:

=RunningValue(((RunningValue((Fields!WasteMinimised.Value), SUM, "WasteYear")) / (RunningValue((Fields!WasteGenerated.Value), SUM, "WasteYear"))), SUM, "WasteYear")

However, with this I get the following error:

The Value expression for the textrun 'Textbox43.Paragraphs[0].TextRuns[0]' uses a RunningValue function in an outer aggregate. RunningValue functions cannot be specified as nested aggregates.

I cannot think of another solution other than RunningValue() to complete this 'cumulative' function

Solution

  • The answer was actually pretty simple if you think about it. I don't need to have any nested RunningValue() functions. I just need to apply the 'Waste Diverted' formula to the RunningValue() totals:

    =RunningValue((Fields!WasteMinimised.Value), SUM, "WasteYear") / RunningValue((Fields!WasteGenerated.Value), SUM, "WasteYear")