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

About

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

Articles Related

Steps

The statement

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

Creation of the system and repository variable

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'

DATE '2001-12-31'

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 type of the variable

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

Use of the variable

CurrentDate

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

Sysdate

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.

You may have to cast it as a date

Support

The repository variable doesn't support the date datatype from Oracle

Oracle has one date datatype and it's the timestamp datatype of OBIEE. It means that you have always the hour, minute and second.

If you use one repository variable in a report and that you don't want to see the hour, minute and second, you have to cast it (in the repository of in an anlytics) such as :

cast( myRepositoryVariable as date)