Creating a dashboard level column selector in OBIEE
Users of OBIEE Answers are accustomed to the versatility of reports provided with the Column Selector. The column selector setup is very easy and intuitive to configure and use. However, it is a report level component, and as such does not work across reports in the context of a dashboard page or an entire dashboard. That is not something users like to hear or care to understand. Fortunately, you can “fake out” the column selector functionality using a dashboard prompt, and by doing that provide the same column selector functionality, for your dashboard page or your entire dashboard. Here’s how:
First, in your Answers reports, use a variable as the column you would like to substitute using the selector functionality. Do this in every Answers report you plan to include in the dashboard that should “swap” a column based on the user selection
Next, create a dashboard prompt that will act as a column selector. Select the prompt scope as desired (Page or Dashboard). In the Show column, use custom SQL to retrieve the list of columns you wish to have the users swap. Reference column names as Table.Column. For example, using the Paint repository, the following SQL will result in allowing the users to select between the Region and District columns:
select ‘Markets.Region’ from Paint where Markets.Region = ‘CENTRAL REGION’
select ‘Markets.District’ from Paint where Markets.Region = ‘CENTRAL REGION’
The query uses a where clause to gurantee only one row is retrived from the database for each value, and can be unioned as many times as needed to include any number of columns you would like to provide the users as selected.
Pick the desired value as the Default and be sure to use the Set Variale option to populate the variable you created in the Answers report with the value from the prompt.
All you need to do now is pull in all the reports you created in Answers, along with the dashboard prompt. You will be able to change the “selected column” using the prompt, and the reports will change accordingly.