Business Intelligence – Oracle

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.

Advertisements

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

  1. […] in Dashboard Prompts – GO URL and Presentation VariablesCustomizing OBI EE – GO URL ParametersOracle BI EE 10.1.3.3/2 – Scoping of Dashboard Prompts and Presentation Variables – Reducing Scope f…About MeOracle BI EE 10.1.3.3/2 – Same Page Navigation Drills and Passing […]

    • Prasad said

      Hi Venkatakrishnan,

      Its very useful for my current requirement
      Do you have any work around for Multi select columns as well?? Please provide me solution if exixts as soon as possible
      Thanks in advance
      Prasad

  2. David said

    Hi, Venkatakrishnan,

    I attempted to recreate what you did above, but when I added the modification to the dashboard prompt as you said to do, I received the following error message. What am I doing wrong?

    [nQSError: 10058] A general error has occurred. [nQSError: 27005] Unresolved column: “SAMPLE”. (HY000)
    SQL Issued: SELECT CASE WHEN 1=0 THEN Organization.”Branch Name” ELSE “SAMPLE” END FROM “Deposit Daily”

  3. David said

    I figured out my error.

  4. Sandeep said

    I have one main report when you click on one column you will get the popup report. in Popup report i have below code in Column formula.
    CASE WHEN “Dim – Correlation Attribute”.”Attribute Name” =’@{VARIABLENAME}’ THEN ‘@{VARIABLENAME}’ ELSE ‘ALL’ END
    so here i want to pass VariableName as dyanamic value.

  5. Gary said

    Is it possible to have one Dashboard or Page which has multiple Reports that use different prompts

  6. Shruthi said

    Hello Mr.Venkat ,

    I follow your blog very regularly . Thankyou for such a great information. Iam stuck with this requirement.

    The requirement is to do a dynamic dashboard where I want to hide some views in one section . Iam doing a Top 10 customers by Revenue for US , EU , AP regions. and I gave a page prompt as “REGION” for US , EU and AP .

    As of now i did three answer reports and placed horizontally in dashboard. If I dont select any region I should be able to see all the three views and If I select US region , the EU and AP views should be hidden .

    Can you please help me how to achieve this. That will be greatly appreciable.

    Thankyou very much

    Shruthi

  7. Roger said

    Hi Venkatakrishnan,

    A quick question on this example, if I may.

    Is ‘SH2’ your schema name (in “SELECT GEOGRAPHY.REGION_NAME FROM SH2”)?

    I couldn’t get it to work for my table “CHANNELS” and “CHANNEL_DESC” column in my “SH” schema. Getting this error:

    [nQSError: 27005] Unresolved column: “CHANNEL_DESC”. (HY000)
    SQL Issued: select CHANNEL_DESC from SH

    Could you please advice when you get a chance?

    Thank you,

    Roger

  8. Prasad said

    Hi Venkatakrishnan,

    Its very useful for my current requirement
    Do you have any work around for Multi select columns as well?? Please provide me solution if exixts as soon as possible

    Thanks in advance
    Prasad

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: