A requirement came up where the default value in an OBIEE 11 g prompt for all column values was not going to work for the client. We were building a state and national dashboard and the prompt needed to show “National” when all values of the state prompt where chosen. Now this was a relatively late arriving requirement so we did not want to go in and update all 50 plus reports from ‘is prompted’ filters to presentation variables. So we decided to utilize the groups feature introduced in 11G. The outcome looks like this:
Here are the steps to make you own custom all values, or any other subsets you are interested in.
Step 1. First create a dummy report that has the column you would like to prompt on and then create a group named ‘National’ in the report.
Step 2: Provide the Display Label as ‘National’ and move all the available members (in this case US States) to the selected area as below and click OK.
Step 3: Expand the Selection Step section and click the save icon on the column you created to group on. This will save the selection steps.
Step 4: You will want to name it the save as the label value you gave it above. Save it in the recommended folder for the dashboard group it will be used with. Decide if you want to save it statically or dynamically. In this case for all values you will want it dynamically in case new values are added later.
Step 5: Modify the dashboard prompt to include the new group value.
Edit the prompt and modify the Choice List Values option as ‘All Column Values and Specific Groups’ as below.
Step 6: Click on the’+’ sign to include the group value. When the popup window opens expand the Shared folder to view the available groups. It will be available in the folder of the saved report and under Subject Area Contents as in the image.
Move the National value to the selected pane on the right. Provide the default selection of the prompt as ‘National’ and save the prompt.
There you have it a customized all column values choice in your prompt that can be utilized for any number of custom groups.
