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.
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
Note that the substring formulas, which have to span dates from 2000 through 2019, need the comparisons to both ‘200’ and ‘201’. Of course, next decade, the formulas will need further adjusting, but once every 10 years isn’t too bad!