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

Example of Presentation Variable in OBIEE

So today I am gonna tell you one of its simplest example.

You have a Dashboard wherein there is a

Report with Employee Name, Salary and New Salary and a Rating Prompt.

And I am using the following SQL to display the values in Rating Prompt.

Select case when 1=0 then “Department”.”DEPTNO” else 1 End from “Test”
UNION ALL
Select case when 1=0 then “Department”.”DEPTNO” else 2 end from “Test”

So this Prompt will display value 1 and 2 in the dropdown list.

Note: You can use any dummy column in place of “Department”.”DEPTNO” because the first condition would always be false.

Whenever the user selects a value from the dropdown, its value is set in Presentation variable called


Rating.

Now, I will use this Presentation variable to calculate New Salary in the report.

If the Rating is 1 then New Salary = Salary+200 else the same salary.


Result:

When Rating =1

When Rating =2

 Example -2:

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

Hello Again

Today, I want to blog about a scenario that I have encountered, which is very interesting to me. Let me describe the scenario without getting too much into details.

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'.

So let's get on with it.

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 BB-11 to JUL - 10 in the prompt, I hit 'Apply' and here we go:

So this is successfully done. If this set up works, the rest is easy. You can then incorporate this report with real data and it will work the same way..

Thanks

Satya Ranki Reddy