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

Bi Server Architecture With Client


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


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'

Obiee Repository Variable Date Type

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

Obiee Server Variable Date Type

Use of the 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
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


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.

Obiee Server Variable Sysdate No Result

You may have to cast it as a date


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)

Discover More
Obiee Variable Manager
OBIEE - BI Server Variables (session and repository)

The OBI Server can handle two types of variable : repository variable: constant or refreshed periodically session variable: depend of the user and are then set up during the login process. system...

Share this page:
Follow us:
Task Runner