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?
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.
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!
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With