Business Intelligence – Oracle

Oracle BI EE – 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



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


Make this prompt to set the presentation variable test


Now, you should be able to pass the presentation variables to the report.



8 Responses to “Oracle BI EE – Presentation Variables in TOP filter”

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

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

    • usman fawad said


      I am new to OBIEE. It becomes a challenge for me to pass variables from dashboard prompts and to catch their value in Answer’s Request.

      I would like to illustrate it with an example.
      Request based on the following SQL statement

      From emp
      Where DOB = ‘@{p_date}’

      Now DOB is in TIMESTAMP format,
      it looks like ‘11/1/2009 12:00:00 AM’.

      Dash board Prompt also depends upon the same column and have also same TIMESTAMP format.

      In next step
      I create a dashboard page
      Place prompt and report there
      Saved the dashboard
      Now I select a date from prompt list and press go button
      It shows no result.

      I tried several combinations in @1 syntax e.g.

      Where DOB = ‘@{p_date}’
      Where DOB = ‘@{p_date}{1-nov-2009} ’
      Where DOB = cast(to_char(@{p_date}, ‘dd-mmm-yyyy’) as date)
      And many other combinations

      I am looking for any help/tips in this regard

      Thanking in anticipation

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

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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: