How to I get cumulative monthly subtotals in SSRS?

Question

I'm using SSRS to create a report which shows a lot of transactions according to a trade date. I've made a group on the month and year called 'grpMonthYear'. Inside that group I've made a subgroup on 'TradeDate'.

The groups and all work perfectly. I'm also generating monthly subtotals in the footer of the group 'grpMonthYear'.

But now I want the cumulative subtotals.

Example, if Jan'13 totaled up to $5,000.00 and transactions in Feb'13 totaled up to $7,000.00 So the monthly subtotal in Feb'13 should show me $12,000.00

I tried using

RunningValue(Fieldname,SUM,'grpMonthYear')

But it doesn't work.

Am I missing out something?

Answer 1

You need to set the scope in the RunningValue function to one outside the current group, for example the table's DataSet itself.

So something like:

RunningValue(Fieldname,SUM,"DataSet")

Here's a simple example based on the following data:

I've created a simple report grouped by grpMonthYear:

The Month Total is just the sum in the current group scope.

The Cumulative Total expression is the following:

=RunningValue(Fields!tradePrice.Value, SUM , "Trades")

Where Trades is the DataSet name. This now gives the required results:

So hopefully this helps - just keep the ordering of all the elements of the table in mind as well as the specific parent scope to use if there are nested groups.