Business Intelligence – Oracle

Archive for December 13th, 2007

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.

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