OBIEE - How to set and use a server variable (system/repository) of type date ?

This article is a tutorial on how to set a date in a server variable (session variable of repository variable) and use it.

This article use the schema SH : SH repository installation in the database and for the repository

To initialize the variables, two statements are used in order to test sysdate :

• the current date

SELECT TIME_ID  FROM TIMES WHERE TIME_ID = (SELECT max(TIME_ID) FROM SALES)

• and sysdate

SELECT SYSDATE FROM dual

OBIEE see the oracle date as a DATETIME then when you create the initialization blocks, you must use this format in the default value :

TIMESTAMP '2001-12-31 00:00:00'

Create then 4 initialization blocks :

• CurrentDateRepo : a repository variable with the current date statement
• CurrentDateSession : a session variable with the current date statement
• SysDateSession : a repository variable with the sysdate statement
• SysDateRepo : a repository variable with the sysdate statement

• verification of the data type in the session manager. See : OBIEE - How to find the value, the data type of a Server variable ?

You can then use it in an answer to create a logical sql as for the session variable :

SELECT Calendar."Calendar Year" saw_0
FROM SH 
WHERE Calendar."Time Id" =  VALUEOF(NQ_SESSION."CurrentDateSession") 
ORDER BY saw_0, saw_1

For the repository variable, the filter will be :

Calendar."Time Id" = VALUEOF("CurrentDateRepo") 

You made an equality between a date (the column Time Id is defined as a date) and a datetime with the session variable CurrentDateSession. BI Server fired :

SELECT DISTINCT T3883.CALENDAR_YEAR AS c1,
     cast(T3883.TIME_ID AS  CHARACTER ( 30 ) ) AS c2
FROM 
     SH.TIMES T3883 /* TIMES Fact */ 
WHERE  ( T3883.TIME_ID = TO_DATE('2001-12-31 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') ) 
ORDER BY c1, c2

With sysdate if you want to make a comparison with a date, you will get no rows because the variable don't have 00:00:00 in the hour:minute:second part of the timestamp.

One solution is to change the statement in the initialization block by :

 SELECT cast ( TO_CHAR(sysdate, 'YYYY/MM/DD' ) AS date ) FROM dual

The mask of the date format in a cast as date statement is managed by the NLS_DATE_FORMAT. Then the above statement will work only if you have set it in the connection pool with the format 'YYYY/MM/DD' as :

If you want to know more, see cast as date in obiee