Business Intelligence – Oracle

Archive for February 19th, 2008

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

Posted in All Posts, OBI EE Plus | 2 Comments »