Thursday, August 6, 2009

OBIEE TopN versus the rest

Getting a TopN in OBIEE is pretty simple using the rank function:



But how can we compare this to the rest?

Let’s add an extra column which gives us the rank number if it’s between 1 and 10, else 99.


case when RANK("F1 Revenue"."1-01  Revenue  (Sum All)") <= 10 then RANK("F1 Revenue"."1-01  Revenue  (Sum All)") else 99 end

Remove the original filter and check the results:


Now add the Customer name to the extra Column:


case when RANK("F1 Revenue"."1-01  Revenue  (Sum All)") <= 10 then Cast( RANK("F1 Revenue"."1-01  Revenue  (Sum All)") as char)|| ' - '||"D1 Customer"."C1  Cust Name" else '99 - The Rest' end

Check the results:


Now the big trick, switch to pivot table and arrange the columns like this:


Till Next Time


Sharath said...

Excellent post, thank you.

Stijn said...

Hi John,
nice one, especially when you put these results in a pie chart!

Anonymous said...

Hi John,
Cool tip for the TOPn/Rank functions. Would you show me "the big trick" how to sum all "99-Others" values when you switched to pivot table.

OBIEE Beginner!

Kwl_PranayJain said...

hi John,
There is this requirement where
a customer selects a month or say multi select of month from prompt by sum revenue with respective month for a particular customer i have to determine top 10 and thats not it... the i will have prompt to choose 2nd month which should give me customer data for those who are top customer by choice of 1 month prompt should only display no matter they are top or not in 2nd month.... did you understand what i meant..??
All this has to happen in pivot


Anonymous said...

Hello John…
Your Topn vs the rest topic is exactly what I need for my reports, but I didn’t have any luck when trying to get the sum amount of all 99-The Rest as shown. Would you please go into detail on how to do that??? Or Any one any idea ????
Greatly appreciated!!!
"BI newbee"

Anonymous said...

somebody get the solution for get the sum of the rest.

John Minkjan said...


the trick isn't that difficult see article : "switch to pivot table"



Eric said...


What about using ranking when there is more than 1 dimension in the report (top 10 products down, months across) and I want the ranking to be done on the year total ?

Anonymous said...

I am having a hard time as well summing "The Rest". I can't find the article "switch to pivot table"
also. Any help would be greatly appreciated. Thanks, sherri

John Minkjan said...


Have look:



supreet said...

Hi John

Excellent way to do the requirement.Wont call it trick ;)

Even we had the same requirement with additional things required..would like to add those in case somebody else has the same..

In addition to showing others we had to show the %ge share of each Customer.To do so I added same measure "Revenue Sum All" in the pivot table and selected "Show Data as %' of column and that shows each customer's contribution.


Paul McGarrick said...

Hi John

I hope you'll publish this comment for the benefit of people reading your post in future. My link shows an alternative way of doing the TopN grouping.

Keep up the good work buddy!


Rushi said...

Hi John,

I have a similar requirement like this.
Can u please explain with an example how to achieve that.
Requirement is : We have to get Top 5 countries and to get top 3 product for respective countries.
That Menas topn vs top n..How to achieve this.

Please help me out...
Thank in Advance

John Minkjan said...


play aroud with the group by / aggregate by fucntion.



PJ said...

Hi John,

Thanks for all the help :)

Just one doubt, is there any way to make a drill down through the top 10 results?