Single Prompt Switching Multiple Reports in OBIEE 11G using Presentation Variables

Single Prompt Switching Multiple Reports in OBIEE 11G using Presentation Variables:


What is Dashboard Prompt?

• A prompt that is created at the dashboard level is called a dashboard prompt because the prompt is created outside of a specific dashboard and is stored in the catalog as an object, which can then be added to any dashboard or dashboard page that contains the columns that are specified in the prompt.
•  A dashboard prompt can be a column prompt, variable prompt, image prompt, or currency prompt. 
• Dashboard prompts are reusable, because you can create one prompt and use it many times.
•  When the prompt object is updated and saved, those updates are immediately displayed in all dashboards where the prompt is used. 
• A dashboard prompt is a specific kind of filter that, when created, saved, and applied to a dashboard or dashboard pages, can filter all or some of the analyses that are embedded in a dashboard or analyses that are embedded on the same dashboard page. 
• A dashboard prompt is interactive and will always be displayed on the dashboard page so that the user can prompt for different values without having to re-run the dashboard. 
• Users can create and save dashboard prompts to either a private folder or to a shared folder.

In this blog i am going to share how a single prompt functions with Multiple reports using Variables. 

I have used 4 reports which I want to switch using dashboard prompt which are MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS . Here is an example along with the steps:

1. Create a Dashboard Prompt with the intended reports as a data value (MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS) to be selected.

2. Click on New->Dashboard Prompt->select any subject area. Note: It will not affect on our dashboard prompt as we have to create presentation variable of our choice.

3. Definition Pane for dashboard prompt will open. Now select "Variable Prompt" as below.

4. Now "New Prompt" dialogue will open. Create presentation variable as VARIABLE 1,label it as SELECT REPORT and select User Input as Choice List.

5. In Choice list Values click on plus (Green Icon) sign; it will display "Enter New Value" dialogue. Now manually enter the names of the reports which you want to display in prompt. Here I will put my four report names which I want to display as a list in dashboard prompt which are MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS as below :

6. Now expand the Options. In "Default Selection" you can give any report name which you want to display by default on dashboard by selecting that report name as "Specific Custom Value". Here I have given "MASSACHUSETTS" as my default selection.

7. Now save your dashboard prompt. In this way our dashboard prompt is ready from selecting reports which will look like :

8. Create reports which you want to display according to selected report name in dashboard prompt.

9. Now I will create four reports named MASSACHUSETTS , NEW YORK, CALIFORNIA and TEXAS and will apply some filters in theses individual reports which will allow me to show the intended report as per selection made in dashboard prompt. So let's create "MASSACHUSETTS" 

10. I am taking some columns in reports plus one dummy column. I have change the column formula of Dummy column as:

CASE '@{VARIABLE 1}' WHEN 'MASSACHUSETTS' THEN '1' ELSE '0' END

11. Now I have applied filter on Dummy Column as "is not equal to /is not in" '0'.

12. Repeat the same process for rest of the three reports New York, California and Texas

CASE '@{VARIABLE 1}' WHEN 'NEW YORK' THEN '1' ELSE '0' END

CASE '@{VARIABLE 1}' WHEN 'CALIFORNIA' THEN '1' ELSE '0' END

CASE '@{VARIABLE 1}' WHEN 'TEXAS' THEN '1' ELSE '0' END

 and 

apply filter on Dummy Column as "is not equal to /is not in" '0'.

13.Putting dashboard prompt and three reports in Dashboard by applying certain conditions on each sections in "Edit Dashboard".

Note: Put all the reports and dashboard prompt in separate sections as we have to apply the condition on section.
14. Now I am applying condition in section of "MASSACHUSETTS"  by clicking on Properties > Condition > Section Condition as below :

15. Similarly follow the same for rest of the three reports. 

16.  Now save your dashboard page and run it. 

17.  Here "MASSACHUSETTS"is my default report so it is visible on my dashboard by default. Now whatever options (report name) you will select from prompt those report will be shown. It is not necessary to show one default report; you can remove default selection from dashboard prompt. 

Output:

18. If you select the prompt value MASSACHUSETTS corresponding report will be viewed and the same applicable for NEW YORK, CALIFORNIA and TEXAS.

Hope It Helps!!

Thanks,

Shanmugha Bharathy G B