OBIEE 11G: Setting multiple values to Presentation Variables through goURL and conditional filtering

Tweet

I am writing this blog after a loooooong gap and I can tell you that it is worth the wait. In this blog, we discuss about the process of setting multiple values to a presentation variable through goURL. This blog also introduces the concept of CONDITIONAL FILTERING in OBIEE.

Do not confuse the setting multiple values to a presentation variable with setting multiple presentation variables through goURL, because the later is easy ;-)

There are 3 parts of the problem

1. How do we capture the list of values from a user selection in OBIEE?
2. How do we pass the selected values through the goURL?
3. How do we use the presentation variable to filter using the selected list of values?

Let us now look at the process to solve this 3 step problem.

1. How do we capture the list of values from a user selection in OBIEE?

This is a no-brainer. We know that we can make a prompt as a multi select and assign its selection to a presentation variable. If a user selects a list of values then the list is assigned to the presentation variable.

2. How do we pass the selected values to the goURL?

We will have to do a little bit of JavaScript coding for this.

Let us look at our code now

<script language = "JavaScript"> function passValues() {
var pvPeriod = '@{pv_multiple_values_period}';

pvPeriod = '%27'+pvPeriod+'%27';
pvPeriod = pvPeriod.split(',').join('%27%2C%27');
window.open("saw.dll?Dashboard&PortalPath=%2Fusers%2FVishal%2F_portal&Page=Prompts%20with%20

multiple%20values%20drilldown&Action=Navigate&var1=dashboard.currentPage.variables%5B%27

pv_multiple_values_period%27%5D&val1=%22"+pvPeriod+"%22","_self"); } 

</script> <input type="button" value="Switch to Drilldown View" onclick="passValues();">

Following are the important points to note in the above JavaScript code.

1. We are capturing the presentation variable set in the prompt above in the narrative view of the analysis using var pvPeriod = '@{pv_multiple_values_period}';
The prompt and the analysis that has this JavaScript code will have to be on the same dashboard page for the analysis to capture the value of the prompt.

Obviously, you can have other analysis as well on the dashboard page. We can also put the narrative view in one of the analysis on the dashboard page.

Note that we are creating a button to call JavaScript but can also use goURL to create a link on a column of a report. We can then use that link to pass the values of any row that the user clicks on along with the value selected by the user in the prompts.

2. %2Fusers%2FVishal%2F_portal&Page=Prompts%20with%20multiple%20values%20drilldown is the path of my dashboard page. Your dashboard page path will be different.

3. The process of setting/passing of presentation variable is below. I have borrowed the following string from my JavaScript code above.

&Action=Navigate&var1=dashboard.currentPage.variables%5B%27pv_multiple_values_period%27%5D&val1=%22"+pvPeriod+"%22"

I will now explain each element of the above string in the order in which it appears in the string.

&Action=Navigate :- This is a required part for goURL and it does not need explanation.

&var1=dashboard.currentPage.variables :- var1 is required for the first variable. If you have 2 variables then you will have another part in the URL string that starts with &var2.

%5B%27 :- This is HTML encoded value of [' . We have to enclose every presentation variable in [' and ']. We will put '] after the presentation variable to be set. We have used the HTML encoded value so that the special characters do not mess with the values being passed.

pv_multiple_values_period :- This is our presentation variable that we are planning to set.

%27%5D :- This is HTML encoded value of '] .

&val1= :- This is required for the first presentation variable and it is paired with &var1. The value to be set for the presentation variable follows &val1=. So if we have 2 presentation variables to be set then we will have a &var2 and &val2 pair as well.

%22 :- This is the HTML encoded value of double quotes. We close the double quotes because we now have to put a JavaScript variable in place that holds the value to which we want to set our presentation variable.

+ :- The plus sign is used to concat values in JavaScript.

pvPeriod :- This is the JavaScript variable that holds the value to be set.

Go back to the JavaScript code and note that we have the following statement in it.

pvPeriod = '%27'+pvPeriod+'%27';

We are concatenating %27 before and after pvPeriod. This is because the value to be set has to be passed in single quotes and %27 is the HTML encoding of a single quote.

We then have the following statement in our JavaScript code.

pvPeriod = pvPeriod.split(',').join('%27%2C%27'); 

If we dont use this code, OBIEE will put all the values to be passed in one string and hence it will not solve our purpose.
For example, let us say that you wanted to set 'Period 1','Period 2' and 'Period 3' as the value of the presentation variable so that OBIEE does something like the following in the where clause of the report that uses presentation variable.

T597842.MCAL_PERIOD_NAME IN ('Period 1','Period 2','Period 3')

Without pvPeriod = pvPeriod.split(',').join('%27%2C%27');, OBIEE will create the following string and it does not help us.
T597842.MCAL_PERIOD_NAME IN ('Period 1,Period 2,Period 3')

Let us now see the process of using the presentation variable.

3. How do we use the presentation variable to filter using the selected list of values?

Now that our goURL produces a comma seperated list of values with each value enclosed in a single quote, all we need is a report that can utilize it.

If we were to use the prompt in the report the regular way,  OBIEE will puts an extra single qoute next to each single quote as shown below

Note the extra single quotes.
We have to convert the filter to an SQL filter and use the IN clause to fix this issue.

Note that in the above screenshot, the values are passed as we expect them to pass.

Ok, so this will help us to pass multiple values to the presentation variable and also enable us to filter using those values in the analysis but what if we have multiple prompts that need this arrangement.
In that situation, we might have a case in which the user might select a few values in one prompt and not select anything in the other prompt.

In this situation, because the prompt in which the user has NOT selected any value, the presentation variable will not get a value and will result in the report not returning any data.

We want OBIEE to ignore the filter if the presentation variable does not have anything assigned to it and to apply the filter if the presentation variable is set to some value.

Note that when you convert the filter to a SQL filter, you can no longer use '%' as your default to force OBIEE to open the filter if the presentation variable is not set to a value because OBIEE will not use a like operator but an equal/IN operator along with '%'.

The solution to this problem is to use the same column as the default. I like to call this mechanism, the CONDITIONAL FILTERING in OBIEE.

So in case of the above example, we might want to modify the above filter as the following.

 "Time"."GL Posting Date - Period" IN (@{pv_multiple_values_period}{"Time"."GL Posting Date - Period"})

Putting the same column name, as is present on the left side of the filter, as the default value, we will ensure that OBIEE does not apply any filters if no value is passed to the presentation variable.

Till next time ..