Using the Top N Filter
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):
October 4th, 2008 at 2:37 pm
Precisely what I was looking for.
Excellent post! Keep it up!