Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Dynamically varying Colums in Sub-Prompts – Passing Presentation Variables to Dashboard Prompts

Posted by Venkatakrishnan J on January 21, 2008

We just had another interesting question today in one of our internal forums. The question was how do we dynamically vary the columns in a Dashboard Prompt based on a selection in another dashboard prompt. Let me explain a bit further. For example, lets assume that we have 2 dashboard prompts. One has values like Region Sales and Brand Sales (these are hard coded values, same as what we saw in the previous blog entry). Now the requirement was, whenever Region Sales is chosen in one prompt the values in the other prompt should change as to show the values of only category names (CATEGORY_NAME). Whenever Brand Sales is chosen in the 1st prompt the 2nd prompt should show only the product names (PRODUCT_NAME). I can envision a similar requirement in many scenarios where one might want to have a single page wise dashboard prompt that would filter all the other prompts within the dashboard whenever a selection is made. In order to achieve this, lets start with the first dashboard prompt where we shall print dummy values ‘Region Sales’ and ‘Brand Sales’. Make this dashboard prompt to set a presentation variable say Report. For more details on how to achieve this refer my blog entry here.

      

Now lets start creating the sub-prompt whose reference column would change based on what is chosen in the first prompt. In order to do this, lets include a dummy column (whatever we choose in the dashboard prompt will not affect any columns. In order to use this prompt we can only use presentation variables) by entering the below case statement in the formula tab.

CASE WHEN 1=0 THEN PRODUCTS.CATEGORY_NAME ELSE ‘Dummy unused Column’ END

      

Now in the dashboard prompt, convert the results to show only SQL Results and enter the below SQL.

SELECT CASE WHEN ‘@{Report}’=’Region Sales’ THEN PRODUCTS.CATEGORY_NAME
WHEN ‘@{Report}’=’Brand Sales’ THEN PRODUCTS.PRODUCT_NAME ELSE ” END FROM SH2

      

So basically, we are passing the presentation variable that we used in the first prompt to the second prompt. Lets now look at the final output after including both the prompts in a dashboard.

      
Lets first choose Region Sales and see what happens to the second dashboard prompt.

      

Similarly, lets choose Brand Sales and see what happens.

      

The only disadvantage of this approach is that the users would have to hit the GO button to make the dashboard prompts to change. But again this can be very helpful in situations where you want a single dashboard prompt to control the output of multiple dashboard prompts.

Advertisements

7 Responses to “Oracle BI EE 10.1.3.3/2 – Dynamically varying Colums in Sub-Prompts – Passing Presentation Variables to Dashboard Prompts”

  1. David said

    Hi Venkat.

    I was able to recreate the two dashboard prompts as you explained. I have a problem with the second use of the prompts. What I mean is, when a value is selected in the first prompt, the second prompt properly constrains the choices. However, if the user then chooses a different selection for the first prompt, the most recently chosen value of the second prompt continues to display. How do you make the second prompt reset to a blank when the first prompt is activated?

    Thanks.

  2. santhosh said

    Hi Venkat,
    i was able to pass dynamic variables to subprompts and it works fine. but if i make the subprompt as multi select instead of dropdown, its not showing anything in the subprompt. Please help me with this.

    Thanks.

  3. huygelen luc said

    We experience the same problem as David, is there a solution or is this a known feature (bug) ?

  4. Shelley Phillips-Mills said

    Hello,

    I’m new to OBIEE. I have found your blog to be very helpful. I have implemented: Oracle BI EE 10.1.3.3/2 – Dynamic Report Columns using Dashboard Prompts and Presentation Variables – Part 2

    What I would like to do now is to have the sub-prompt as another report column prompt and not as table field prompt. But pass the presentation variable that we used in the first prompt to the second prompt.

    In the first prompt I have three report column options. In the second prompt, I want the three more options for each of the three report column options in the first prompt and not the same three.

    For example:

    First Prompt : ‘Region’, ‘Company’, ‘Product’
    Second Prompt: If ‘Region’ in Prompt1 then report columns for Prompt2 are Project Manager, Cost Type, Contract Type;
    If ‘Company’ in Prompt1 then report columns or Prompt2 are Work Type, Project Manager, Region;
    If ‘Product’ in Prompt 1 then report columns for Prompt2 are Contract Type, Cost Type, Project Manager

    Is this possible?

    Thank you,

    Shelley

    • Hathai said

      Dear Shelley Phillips-Mills,

      I am so new to OBIEE and I did try several days about the same problem as yours

      First Prompt : ‘Region’, ‘Company’, ‘Product’
      Second Prompt: If ‘Region’ in Prompt1 then report columns for Prompt2 are Project Manager, Cost Type, Contract Type;
      If ‘Company’ in Prompt1 then report columns or Prompt2 are Work Type, Project Manager, Region;
      If ‘Product’ in Prompt 1 then report columns for Prompt2 are Contract Type, Cost Type, Project Manager

      Could you solve this problem yet?

      Please suggest me too .

      Thnx in advance

  5. Shelley Phillips-Mills said

    P.S. the timestamp appears to be off. My time is 3:59PM EST.

  6. Satya said

    I need to one help regarding OBIEE Dashboard..If I log in as Administrator,I can see the Save Current Selections –> For Others.. in the Page Option section of the dashboard. But I can’t see the For Others if I log in as other users. I can only see For me.. I have a requirement to enable “For Others..” for other users. Could someone help me to achieve that???

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: