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

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

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
Advertising

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'

of

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

Advertising

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

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