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
15 comments:
Excellent post, thank you.
Hi John,
nice one, especially when you put these results in a pie chart!
Cheers,
Stijn
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!
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
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"
somebody get the solution for get the sum of the rest.
Thanks
@All
the trick isn't that difficult see article : "switch to pivot table"
regards
john
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 ?
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
@Eric,
Have look:
http://obiee101.blogspot.com/2009/12/obiee-top-n-months-across.html
Regards
John
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
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
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
@rushi,
play aroud with the group by / aggregate by fucntion.
regards
John
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
Post a Comment