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=@2width=”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.
Posts
