Business Intelligence – Oracle

Oracle BI EE – Dynamic Report Columns using Dashboard Prompts and Presentation Variables

Posted by Venkatakrishnan J on December 13, 2007

I got a very strange request today from a new BI EE user. I thought i would blog about it since this was a very unique request. We all know that we can use column selectors to dynamically vary the column in a report. The problem with column selectors is that they are specific to a report. And the user wanted to have a dynamic column set across all his reports inside a dashboard. Lets understand this with an example. Assume that there is a dashboard prompt wherein one can enter the column names. In the same page having the dashboard prompt assume that there are 2 reports. One report has the columns REGION_NAME and SALES. The other report has the columns REGION_NAME and AMOUNT. So, what the user wanted was to dynamically vary the REGION_NAME column based on what was entered in the dashboard prompt. So, his aim was to simultaneously analyze different metrics in different reports at the same time. Lets try achieving this. We shall start with creating a simple Dashboard Prompt on the REGION_NAME column. Use Edit Box as the Control and set the Presentation Variable promptdyn. Have the default value of this prompt as GEOGRAPHY


Now, lets create 2 reports. In the first report include 2 columns REGION_NAME and SALES. Go to the formula of REGION_NAME column and enter @{promptdyn}. So, we are basically making the column name dynamic.



In the same way create the other report with @{promptdyn} as one column and AMOUNT as the other column. Now include both the reports in the dashboard.


As you see above, the default value of GEOGRAPHY.REGION_NAME has propagated to both the reports. Now lets change the value in the dashboard prompt to GEOGRAPHY.COUNTRY_NAME


Both the reports would now be having COUNTRY_NAME as the report columns. But the main disadvantage of this is that the Dashboard Prompt has to be manually entered with the Presentation Layer columns and care must be taken to ensure that we are not entering wrong values. In order to have the drop down you need to have these presentation columns in a seperate table. Maybe a future blog entry to have workaround for this edit box solution.

4 Responses to “Oracle BI EE – Dynamic Report Columns using Dashboard Prompts and Presentation Variables”

  1. […] by Venkatakrishnan J on January 24, 2008 If you had gone through my previous blog entry here, you would be knowing how to go about dynamically varying the columns in a report. But one drawback […]

    • Subhash.T said

      Hi Venkateshwaran,

      I am strucked out with creating a drop down for dashboards.Like if I click on dashboard header on one dash board I should get list all dashboards. Among that I should select one.

      Could you please give some solution for that.

      Thanks and Regards,


      • Kotha said


        Is there any way to hide a column based on a calculated value. For example display the column in OBI answers only when the value in the field is not null.


  2. Leo Pandzic said

    Hi Venkatakrishnan,

    I have a problem using Presentation variable.
    I have three columns. The first one is for Branch, the second one is Amount1 for the year which I choose in Year prompt (In Dashboard), and the third one is also the Amount2 but for the previous year than the chosen year.

    I created in Formula field for the second column (Amount1)

    FILTER(AGREGAT.”Amount” USING AGREGAT.”Year” = @{YearPrompt}{AGREGAT.”Year”})

    and I also created almost the same filter formula for Amount2 (for previous year)

    FILTER(AGREGAT.”Amount” USING AGREGAT.”Year” = @{YearPrompt}{AGREGAT.”Year”} – 1).

    I got the good results for the Amount1, but I can’t get any results for Amount2 ?

    Please, where I made a mistake?

    Thank you for your time and help !


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: