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

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

3 - Steps

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

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

3.3 - Verification of the type of the variable

3.4 - Use of the variable

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

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

4 - Support

4.1 - 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)

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap