Saturday, September 26, 2009

OBIEE Using the Top N Filter (repost)

Learn to use the Top N filter to filter results in Answers requests. Covers advanced usage of the Top N filter using the by clause, to show the Top N results within a particular grouping (i.e., Top N Customer Revenue amounts by Product):


Suppose you want to see the Top 5 results from an Answers report, such as the Top 5 overall customers by revenue:

This can easily be acheived by using a simple “in Top N” filter:

What if you want to see the Top 5 Customers by Revenue for each Product? Using a simple Top N filter won’t work in this case, as it will produce the following results:

To get OBIEE to show the correct results, you need to take the Top N filter into SQL mode. Starting with a normal Top N filter, click Advanced at the bottom of the filter window and select Convert this filter to SQL:

Next, edit the filter with the by clause enclosed in red - unfortunately you’ll have to type the column name in manually, so be sure to use quotes if the column or presentation table contains spaces. The column used here is the field you want to group by (Product, in this example):

Now, the Top N filter will give the desired results - The Top 5 Customers by Product:

The full request criteria (notice the sorting options used to produce the results above):


This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html
Till Next Time

6 comments:

Voggler said...

Great post !
Very helpful
Thanks
Amir

Ossy said...

Great post :), but i'm not able to see the pictures :( How will the query look like when you add the BY clause?

McCulloch said...

Hi John

Really useful blog, thanks very much! But, the screen shots for this post have been dropped. Can anybody tell me the syntax referred to above for adding GROUP BY to an Advances Filter?

Anonymous said...

Very helpful post - The screen shots are missing - please post them

Anonymous said...

Hi, this is a very usefull post and I'm trying the whole day to figure out where to enter the 'by'-clause.
Would you please post the screen shots?
Thanks you very much

John Minkjan said...

I don't have the orignal pics anymore, but for those of you looking for the BY statement have a look here:
http://obiee101.blogspot.com/2008/02/obiee-aggregate-by.html

Regards

John