Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Passing Operators in Dashboard Prompts – GO URL and Presentation Variables

Posted by Venkatakrishnan J on February 19, 2008

As you might probably know, there is no out of the box way currently available in OBI EE to make an end user to choose operators(like , = etc) and then pass these operators to filters. Sometimes your end users might have a requirement say to see a report where Sales > 3000, Sales < 3000 and Sales = 3000. In such a case, we would need the capability for the end user to choose the operator from a drop-down in a dashboard prompt and then enter the value in an edit prompt. Since, there is no out of the box way to achieve this lets look at a simple work around to get this feature using GO URL and presentation variables. So, first lets start with creating a simple dashboard prompt. This dashboard prompt would contain a Drop down listing the operators and then an edit prompt for the sales. So, include any column in the dashboard prompt and enter the below formula.

CASE WHEN 1=0 THEN Markets.Region ELSE ‘Operator’ END

And make this prompt to set a presentation variable Operator. Also, in the results section convert the results to SQL and enter the below SQL.

SELECT CASE WHEN 1=0 THEN Markets.Region ELSE ” END FROM Paint UNION ALL SELECT CASE WHEN 1=0 THEN Markets.Region ELSE ‘=’ END FROM Paint

      

Similarly, include the Dollars column as a second column to this Dashboard Prompt. Convert this to an edit box and make it to set the presentation variable Dollars. Save this dashboard prompt. Now go to Answers and start creating a report containing Region, Brand and Sales Dollars. Add a filter on Sales Dollars with an “is prompted” clause.

      

Save this report. Now create another report with 2 columns. The 2 columns of this report would point to the 2 presentation variables in the dashboard prompt i.e Operator and Dollars. In the 1st column enter the below formula.

CASE WHEN ‘@{Operator}’ = ‘=’ THEN ‘eq’ WHEN ‘@{Operator}’ = ‘>’ THEN ‘gt’ WHEN ‘@{Operator}’ = ‘<‘ THEN ‘lt’ END

In the 2nd column enter the below formula,

CASE WHEN 1=0 THEN Markets.Region ELSE ‘@{Dollars}’ END

      

      

Now go to the narrative view of the report and enter the below code. This is basically an iframe code which will render the Report1 by passing the Operator and Value parameters to the GO URL.

      <iframe src=http://localhost:9704/analytics/saw.dll?GO&nquser=Administrator&nqpassword=Administrator&path=/shared/Paint+Demo/Operator+Select/Report1
&Action=Navigate&P0=1&P1=@1&P2=”Sales%20Measures”.Dollars&P3=@2 width=”650″ height=”400″></iframe>

      

Now, go to the dashboard page and include the prompt that we created first and the narrative view of the second report. Now, if you notice, we can change the operator and value simultaneously.

      

      

Advertisements

2 Responses to “Oracle BI EE 10.1.3.3/2 – Passing Operators in Dashboard Prompts – GO URL and Presentation Variables”

  1. Tina said

    Smart tips:)

  2. Camstra said

    Hi Venkat,

    Very nice and helpfull article you have here. I have just one little problem: when I try to replicate your solution in our environement, everything works nicely, but the report does not accept the select values. It always loads with an ‘equal to’ (even though url with which the iFrame gets called is right and does change along to ‘gt’, ‘lt’ or ‘eq’).

    Do you have any idea what might be causing this?
    I’m using OBI version 10.1.3.3.2 (Build 071217.1900) with Firefox 2.0.0.12 as browser.

    Any help would be greatly appreciated.
    Thanks!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: