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:
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 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