OBIEE11G Prompt Values

OBIEE11G Presentation Variable To Dynamically Switch Filtered Column Values Based On Dashboard Prompt Values

In the dashboard, the user want to be able to have an option to filter either by Month or by Year with a switch. In other words, the first thing they want to do on a dashboard is to select the option of "Year" or "Month". Once the option is selected, the next prompt will change to either filter by "Year" value or by "Month" value. The report that they are querying against is the same report that show be able to go either 'year' or 'month'.

The first thing that needs to be done is create this "switch" on the dashboard. This is basically another dashboard prompt that should only return 2 values, 'Year' and 'Month'. To do so, let's create a dashboard prompt and call it 'Time'. In my case, I randomly pick "Fiscal Period" column from table "Time" in Subject Area "Financials - AP Transactions".

In the prompt creation list, go to 'Option' and set the "choice list value" to 'Show SQL result'. In the box, enter the following code: 

SELECT case when 0= 1 then "Time"."Fiscal Period" else 'Year' end FROM "Financials - AP Transactions" union all SELECT case when 0= 1 then "Time"."Fiscal Period" else 'Month' end FROM "Financials - AP Transactions"

 

 

The code will generate 2 values 'Year' and 'Month' as a result of the union of 2 select statement. Each select statement will return one of the 2 values because the condition '0 = 1' is always false.

If you notice, at the bottom of the this window where "Set A Variable" is filled with Presentation Variable. This is how to set a presentation variable using Dashboard Prompt. This time, the variable is named 'Time'. My purpose is the pass this variable into the next prompt that I am about to create.

The idea is that, the variable 'Time' will hold one of the 2 values of 'Year' or 'Month' depending on which is selected by the user. Then the next prompt will use this variable to determine whether it should return year column data or month column data.

Therefore, the next prompt that I am creating is called 'Period' with a 'between' operator.

So let's take a look at this next prompt 'Period':

In here, the first thing I do is edit the column formula. Since this prompt is also created based on a randomly chosen column, I have to define it's content. This time, the expression is:

case when '@{Time}' = 'Month' then "Time"."Fiscal Period" when '@{Time}' = 'Year' then "Time"."Fiscal Year" else 'N/A' end

As you can see, it is a condition saying that if Presentation Variable 'Time' happens to be 'Year', then this column should be "Fiscal Year"; if Presentation Variable 'Time' happens to be 'Month', then this column should be "Fiscal Period", otherwise it is 'N/A'.

Now back to the prompt creation window. Again here we have to set the Choice value list to be 'Show SQL Result'. By doing so, the logical sql statement is automatically generated with the above condition embedded into the select statement.

Here as you can see, I set another presentation variable call 'Period'. My goal is to use this variable as the filter condition for the report.

So let's preview these 2 prompts and see how they look like:

 

So as you can see from above. The prompt 'Time' has only 2 values 'Year' and 'Month' in the drop down list. You can select either one and hit 'Apply', then the content of the drop down list of prompt 'Period' will hold either years or month data. Just remember to hit 'Apply' after picking 'Year' or 'Month' from the first prompt in order to see the updated list of the second prompt.

So far so good. Now let's create a simple report with only 1 field for testing purpose. Here I again randomly pick a column calling it 'Period' from the subject area for this report, then I have to edit the column formula to allow it to be either year column or month column depending on the presentation variables. Same logic here as in the second prompt 'Period'.

The expression is again:

case when '@{Time}' = 'Month' then "Time"."Fiscal Period" when '@{Time}' = 'Year' then "Time"."Fiscal Year" else 'N/A' end

Then, we need to create a filter condition on this column so that it will filter the values coming from the second prompt's presentation value. In this case, the filter condition is 'equal to' the name of the presentation variable 'Period':

Now, let's put everything together on a dashboard and see how everything works:

Let's pick 'Year' and click 'Apply'. Then you can see a list of years in the drop-down of the select prompt:

 

In this example, I select between 2003 to 2005 as my prompt value. Then I hit 'Apply' again to see the result of the report: 

                     

Now let's go back and try 'Month' and see what happen:

After selecting the period from and hit 'Apply' and here we go:

 

So this is successfully done...