Business Intelligence – Oracle

Archive for February 13th, 2008

Oracle BI EE 10.1.3.3/2 – Scoping of Dashboard Prompts and Presentation Variables – Reducing Scope for a specific Report

Posted by Venkatakrishnan J on February 13, 2008

Today we shall see an example of how to go about reducing the scope of Dashboard prompts to only specific reports. As you would already know, Dashboard Prompts have 2 types of scoping. One is Page and the other is Dashboard.

      

So, basically you cannot have multiple prompts for the same dashboard column in the same page. Lets look at an approach today to see how we can have dashboard prompts of reduced scope. Before doing that, lets try to understand the scoping of Dashboard prompts. Lets first start with a creating 2 dashboard prompts each pointing to the same REGION_NAME column. Each of the dashboard prompts would set a presentation variable. In my case, i have chose Sample and Sample1 as the presentation variable names. Both the prompts would have a page scope.

      

      

Now, lets create 2 reports each having the same columns (REGION_NAME and SALES). For one report, let’s create a filter on REGION_NAME to be equal to that of the presentation variable Sample and the for the other report, create a filter of REGION_NAME to be equal to that of the presentation variable Sample1.

      

      

Now, lets pull both the dashboard prompts and the reports into a single dashboard page. As you would see, when you choose any value for one of the prompts only the second report output changes since the prompt would still call the latest presentation variable Sample1 instead of Sample. This happens because of the page scope of the prompts.

      

Now, how do we go about reducing the scope of the prompts to only one report. In order to do this, edit both the prompts. In formula section, enter

CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘You can put Anything Here’ END

And convert the Results to SQL Results and enter the below SQL.

SELECT GEOGRAPHY.REGION_NAME FROM SH2

      

      

Now, if you go back to the dashboard page, you would notice that both the presentation variables would be set and both the reports would have proper filters applied.

      

Though we have seen this technique before, we have not applied it in this context earlier. This can be pretty useful in a lot of situations when you do not need multi-select prompts.

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