Using OBIEE Session Variables in Select Tables in the Physical Layer

By Kurt Wolff

There are many times when it is very beneficial to pass the value of session variables (or report variables) into the SQL used to define a Select table in the physical layer. This allows the select statement to focus on just the data you want, rather than creating a view with potentially millions of rows and then subsequently applying a filter to that result set.

There are three cases to consider, depending on whether the session variable is intended to filter a column that has a numeric, varchar, or date data type.

The first case is where a session variable has a numeric value. In the following example, the session variable RETAILERID has been assigned a numeric value. The intent is to filter that data just for that retailer. COMPANYID is the name of a physical column. The syntax is:

WHERE COMPANYID=ValueOf(NQ_SESSION.RETAILERID)

The second case is where a session variable needs to be evaluated as a string. In this case, enclose the ValueOf function (including the name of the session variable) in single quotes.

WHERE upper(SALESREP) = upper('valueof(NQ_SESSION.USER)')

The third case, dates, is the hardest. Dates are, frankly, inordinately messy in OBIEE. There are a plethora of ways that dates can get formatted depending on which application is being used to select the dates. It would be nice if there was a single place where you could say “I’d like dates to be formatted like this.” But there isn’t (a huge oversight, in my opinion), and if you attempt to descend into the javascript code forest to tweak things – well, good luck.

The approach I’ve used, which is not ideal but has worked for me, is to hedge your bets in the Select statements. For example, the format of a date report variable can vary, depending on whether the user has changed the default value set by a dashboard calendar prompt.

For example, here are dates as set by the default values in the prompt.

When the user modifies the date range using the first calendar, the format of the first date changes.

If these date prompts are setting report variables, you need to be able to deal with both formats. I’ve done it this way.

BETWEEN case when substr('valueof(NQ_SESSION. StartDate)', 1, 3) = '200' or substr('valueof(NQ_SESSION. StartDate)', 1, 3) = '201' then to_date(substr('valueof(NQ_SESSION.StartDate)',1,10), 'yyyy-mm-dd')
else to_date('valueof(NQ_SESSION.StartDate)', 'mm/dd/yyyy') end
AND case when substr('valueof(NQ_SESSION.EndDate)', 1, 3) = '200' or substr('valueof(NQ_SESSION.EndDate)', 1, 3) = '201' then to_date(substr('valueof(NQ_SESSION.EndDate)',1,10), 'yyyy-mm-dd')
else to_date('valueof(NQ_SESSION.EndDate)', 'mm/dd/yyyy') end