Saturday, November 3, 2007

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

2 comments:

WillR said...

Hey Bob,

I also found a way to do this for MDX

WITH
SET [Top 10 Developers] as 'TopCount({[Developer].[Name].members}, 10, [Measures].[YourMeasure])'
MEMBER [Developer].[Rest of Developers] as 'SUM(Except({[Developer].[Name].members}, {[Top 10 Developers]}))'

SELECT
non empty crossjoin({[Measures].[YourMeasure]},{[Time].[Year].[2007]}) on columns,
({[Top 10 Developers], [Rest of Developers] }) on rows
FROM [YourCube]

Kind regards,

Will

Anonymous said...

Cheers for that Bob. Worked a treat...

Ronan