Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to I get cumulative monthly subtotals in SSRS?

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?

like image 718
Vivek Todi Avatar asked May 09 '13 13:05

Vivek Todi


People also ask

How do I get totals in SSRS?

To do so first, goto Row Groups pane and right-click on the Details will open the context menu. Let me open the Preview tab to check the Totals at the level of the detail.

How do I get top 10 records in SSRS?

For top 10 records from a dataset. 1: Go to the Row Group properties and add a filter on for TOP N . 2 . Sorting on "=Sum(Fields!

How do you sum distinct values in SSRS?

You use groups. At the top click the insert menu, then table, then Table Wizard. Pick your dataset and hit next. Now drag the column for the different types of items you want a distinct sum of into the Row Groups section.


1 Answers

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:

enter image description here

I've created a simple report grouped by grpMonthYear:

enter image description here

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:

enter image description here

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.

like image 83
Ian Preston Avatar answered Sep 19 '22 16:09

Ian Preston