Creating a Top N Report with ‘Others’

No, this is not a blog about “Lost” ;-)

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:

SELECT
Row_Number() Over (Order by Sum(TBL.Number) desc) as Rank,
Case
When Row_Number() Over (Order by Sum(TBL.Number) desc) <= Convert(int,10)
Then 1
Else 2
End as GroupNumber,
Description,
Number
...

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.

peace

BobP