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?

Matsu

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.

Sidon

This works. As well as RunningValue(FieldName,SUM,Nothing) – Matsu

Thank you for such a detailed example. – Matsu

You're welcome. Interesting that it works with a Nothing scope; I wouldn't have expected that - I guess it depends exactly where the TextBox is located in relation to everything else. – Sidon