Oracle BI EE 10.1.3.3/2 – Presentation Variables in TOP filter
Posted by Venkatakrishnan J on February 21, 2008
I got an email in one of our help lists today stating that Answers does not seem to accept presentation variables in TOP filter. If you are not sure what i mean, look at the screenshot below.
So, basically the user wanted to have a filter like “Sales is in TOP N” where the N would be populated by a presentation variable from a dashboard prompt. Lets look at a workaround today to achieve the same. Lets start with creating a simple report containing 3 columns Country, City and its corresponding Sales.
Now, lets create another column with the below formula
TOPN(SALES1.Sales,@{test}{5})
What this does is it creates a TOPN aggregation on the column. test is our presentation variable and 5 is the default value of the presentation variable.
Now, create a filter on the newly created column as shown below
So, basically your final filter will look like the one shown below
This filter is equivalent to the one that we tried first (where we got the error). Now create a dashboard prompt with a column formula as shown below
CASE WHEN 1=0 THEN GEOGRAPHY.COUNTRY_NAME ELSE ‘Test’ END
Make this prompt to set the presentation variable test
Now, you should be able to pass the presentation variables to the report.
Kevin McGinley said
When using TopN, you don’t need to put a filter on the report. It will automatically only show the TopN rows. This is true whether you use a presentation variable in the formula or not. Cheers!
Venkatakrishnan J said
Kevin – You are right. But you need to have that as a column in your report correct or else hide it. If you notice, the above report does not have TOPN as a column but instead as a filter(we have removed the column after adding the filter).
Kevin McGinley said
Ah yes…missed that in my first viewing. Yes, if you aren’t going to actually display the ranking, then it needs to be hidden or in the filter for the TopN function to get applied. Our training course has an exercise exactly like this, but we display the ranking. Thanks for the clarification!
rdariog said
according to the TopN function, i don’t know how create a filter with a TopN function by groups. I want the TOP 10 for each group of the other two columns. any idea about that ???
Dipti Khandekar said
I am facing problem & is:
I want ctrate report in Answers. The requirement is that I should be able to select year from Page Prompt. & report should display values between that selected year to current date.
I tried to give/use presentation var in filter of year column.But am not able to see expected result. Kindly help me..
Thanks in advance.
Dipti
Brian said
In looking at the blog, it is similar tot he question I am trying to answer.. but I am not totally sure on how it would work; I am trying to view via a prompt the last (3) cycle dates – example if user select 6/2009; I want to show them 6/2009. 5/2009 & 4/2009 – but I’m not a 100% in how this would work with the presentation variables
RMS said
But this is not working when we does not give any value in the prompt, i.e., by clicking “GO” button with out any value is not working. it is giving error in the report. can u please help me.