Often times I have been asked to create a Top N report. This is relatively easy, as you can just add a TOP N to the SQL statement, and display the results in the detail of your SSRS table.
However, once in a while I get a request for a Top N report, but include 1 line with a total of the “Others”
After a lot of trial and error, some internet searching, and discussion with other SSRS folks, this is what I came up with and what I use every time now.
First, in the SQL statement I rank my results, and then group that ranking into 2 groups. The 2 groups represent Others and Non-Others.
Here is a sample SQL:
Row_Number() Over (Order by Sum(TBL.Number) desc) as Rank,
When Row_Number() Over (Order by Sum(TBL.Number) desc) <= Convert(int,10)
End as GroupNumber,
Then I put a table in my report, and add a group with the GroupNumber field as the grouping field.
I remove the group footer and put an expression on the Hidden property of the entire row:
=iif(Fields!GroupNumber.Value = 1, true, false)
This hides the row if the GroupNumber = 1 (non others)
I then put the string “Others” into a column, and the sum of the number field into another column.
In the details row, I add an expression to the Hidden property:
=iif(Fields!GroupNumber.Value = 1, false, true)
This hides the detail row if the GroupNumber = 2 (Others)
I then put the description field into the column, and the number field into the other column.
When you run the report, this is what you wind up seeing:
City A 100 <-- This is a detail row
City B 90 <-- This is a detail row
City C 80 <-- This is a detail row
Others 230 <-- This is the group header row.