Menu Close

Filter Top N Group Totals

Lets say you have a dataset that is returning a bunch of rows but you have a top N or some kind of a filter that will display only the Top N rows base on a criteria. Well most likely you have a filter set up under the Details Group. Now you want to have a sum of the totals for the Top N results. Well if you just use Sum(Fields!name.Value) the SSRS will return the SUM of all rows generated by the dataset in your report and not the SUM of only the Top N results shown on your report.
So here is the solution:

For a Table:

Lets say you have Grouped by Product.Name so in your Row Groups you will have something like this:

[ (ProductName)
= (Details)

And you would like to filter the Top 10 Products so in that case if you right click (Details) the Group Properties under filters you will have a filter condition defined which in result will make the report return only the Top 10 Products.
1. Create 1 additional row that is inside the Details group. You can do that by right clicking the row that will show the Details and then choose Insert Row bellow.
2. In that newly created row enter this Expression:

=RunningValue(Fields!Ammount.Value, Sum, “ProductName”

and Hide the whole row
3. Right click the (Details) group and select Add Total -> After
4. In the newly created Row enter this expression:

=reportitems!AmmountSubtotals.value

where AmmountSubtotals in my case is the name of the Textbox created in step 2.
Enjoy!

For Matrix:

Same method like the above one but after you create the Matrix you need to go to the Column Group where you most likely will have only 1 Group in there. So right click it then select Add group -> Child Group -> check Show detailed data.
Then add the columns needed and Add Totals for the column where you will show the result with the Expression:

=reportitems!AmmountSubtotals.value