Thursday, August 6, 2009

OBIEE TopN versus the rest

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

image

image

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.

image

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:

image

Now add the Customer name to the extra Column:

image

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:

image

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

image

Till Next Time

15 comments:

Sharath said...

Excellent post, thank you.

Stijn said...

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

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.
Thanks,

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
like

_____Month________4&5________3
Customer_Rank_Revenue___Revnue
A________1__10000_______7000
B________2__90000_______10000
C________3__85000_______90000
D________4__70000_______75000

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.
Thanks

John Minkjan said...

@All

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

regards

john

Eric said...

John,

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...

@Eric,

Have look:
http://obiee101.blogspot.com/2009/12/obiee-top-n-months-across.html

Regards

John

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.

Supreet

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.

http://total-bi.com/2010/10/obiee-topn-versus-the-rest/

Keep up the good work buddy!

Cheers,
Paul

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
Rushi

John Minkjan said...

@rushi,

play aroud with the group by / aggregate by fucntion.

regards

John

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?

Cheers