Tuesday, January 17, 2012

OBIEE 11g - Using a Dashboard Prompt as a Column Selector

Yesterday, I was looking for a solution for creating a column selector that would work for an entire dashboard page, without the objects on the page being part of the same compound view.  I found this link on Business Intelligence Consulting Group site that looked promising ( BI Consulting Group ).  However, with some Prompt functionality in OBIEE, I was able to do it using a simpler method that does not require any updates to the RPD.  


Create Variable Prompt
1.  First, instead of using the method from the link above where they start by creating a member in the RPD, I decided to bypass this by using the standard functionality within a Variable Prompt.  I decided to populate the variable 'SELECT_COLUMNS' with a variable prompt, and added custom values that will correspond to Presentation Column names in the RPD.  
Add your Presentation Column names in the 'Choice List Values', just as they are in the RPD


Example of Presentation Columns in RPD that match the custom
values in the Variable Prompt



2.  Now, I update my OBIEE query to have the Presentation Variable as part of the column name.  Now, when I put these together in a dashboard, the Presentation Variable allows me to select the column that I want to show in the dashboard.  Like I said, a column selector will do the same thing if you only have one query/view.  However, if you want to update more than one compound view, a column selector isn't really going to allow you to update all views with the same column selector. This approach allows you to do this without any RPD development.


Now, I add my Presentation Variable 'SELECT_COLUMNS' in the column formula to allow for the Variable Prompt to drive the Presentation Column that will be displayed in my dashboard.