KPI Partners Blog

Handy Date Session Variables in OBIEE

Posted by KPI Partners News Team on Sat, Sep 10, 2011 @ 09:20 PM

By Kurt Wolff

It is often convenient to set a number of session variables to capture date values that you use repeatedly in your queries. For example, if you have weeks that end on Saturday, you might want to have the date of the most recent Saturday in a session variable, called perhaps PREVIOUSSATURDAY. You can then use that session variable as the default date value in your queries – for example, “Periods”.”Date” = VALUEOF(NQ_SESSION.PREVIOUSSATURDAY).

Assume today is June 10. Using the convention that weeks begin on Sunday (adjust accordingly if that’s not the case for your enterprise), we can think of Current, Previous, and Next weeks.

Handy   Picture1








Since you are going to be setting these date variables using physical SQL in initialization blocks, the SQL issued will be specific to the database platform you are using. For Oracle, you could write:



trunc(sysdate) - to_char(sysdate,'D')+1 CurrentSunday

, trunc(sysdate) - to_char(sysdate,'D')+2 CurrentMonday

, trunc(sysdate) - to_char(sysdate, 'D')+7 CurrentSaturday

, trunc(sysdate) - to_char(sysdate,'D')+8 NextSunday

, trunc(sysdate) - to_char(sysdate,'D') PreviousSaturday

, trunc(sysdate) - to_char(sysdate,'D')+2-8 PreviousSunday

, trunc(sysdate) - to_char(sysdate,'D')+2-7 PreviousMonday

, cast(to_char(trunc(sysdate), 'YYYY') as INT) CurrentYear

, Cast(to_char(trunc(sysdate), 'YYYY')-1 as INT) PreviousYear

, add_months(trunc(last_day(sysdate)),-1) + 1 CurrentMonthFirstDay

, last_day(trunc(sysdate)) CurrentMonthLastDay

, add_months(TRUNC(last_day(sysdate)),-2) + 1 PreviousMonthFirstDay

, case when last_day(SYSDATE) = SYSDATE then TRUNC(SYSDATE) else add_months(TRUNC(last_day(sysdate)),-1) end LASTDAYCOMPLETEMONTH

from dual;

Handy   Picture2


Handy   Picture3


If you are using a calendar that’s different from the normal “Gregorian” calendar (i.e. a fiscal calendar) that you have stored in a Periods table, you can write the analogous SQL for that calendar. You won't be able to use the Oracle date functions for many of the values you want, but you can still write the SQL to return the values according to the fiscal periods in your calendar using different methods.

Tags: Kurt Wolff, Oracle BI, Blog

Subscribe to the KPI Blog