Filtering on timestamp with time zone using date prompt

Question

Hi!

We have an issue in OBIEE 12.2.1.2.0.

In short, the problem is this:

Have a dimension column of type timestamp with time zone called BALANCE_TIMESTAMP.

All database values are saved as GMT.

Requirement:

Filter on this column in an OBIEE analysis, but do not use time, only date (calendar).

Take into account that time zones will vary for the users.

Say we have column values:

ID           BALANCE_TIMESTAMP

1             01-Feb-2017 11:00:00 PM GMT

2             02-Feb-2017 03:00:00 PM GMT

3             02-Feb-2017 11:00:00 PM GMT

4             03-Feb-2017 11:00:00 PM GMT

If prompt is set to 02-FEB-2017 and the user localtime zone setting in OBIEE is GMT, the result set in the report should be:

2             02-Feb-2017 03:00:00 PM GMT

3             02-Feb-2017 11:00:00 PM GMT

If prompt is set to 02-FEB-2017 and the user local time zone setting in OBIEE is GMT+2, the result set in the report should be:

1             01-Feb-2017 11:00:00 PM GMT  (= 02-Feb-2017 01:00:00 AM GMT+2)

2             02-Feb-2017 03:00:00 PM GMT (= 02-Feb-2017 05:00:00 PM GMT+2)

We have tried different options, but no one has worked out so far.

Any pointers would be great!

Best Regards,

Morten

Morten_J · Answer

Thank you Arijit!

We are testing on a similar solution. Created a database function where the timestamp is converted to correct date using cast .. at time zone . .as date. Use evaluate to call the function. Seems to be working so far.

Thanks,

Morten

ArijitC · Answer

This morning when I saw this problem, I found it interesting and tried to resolve it.  I came up with this

Idea is to use oracle to_date query to resolve this

select to_char(to_date('2017-02-01 11:59:59 pm', 'YYYY-MM-DD HH:MI:SS pm') + 2/24, 'YYYY-MM-DD HH:MI:SS am') from dual;

where 2 of 2/24 is the offset value, used as dashboard prompt pv_offset

'2017-02-01' is the date prompt , presentation variable pv_date.

Finally you can use this analysis in another analysis to get your final result.

Now here is how I did this …2 dashboard prompts...

Prompt pv_date value expected 02-FEB-2017, 02-JAN-2016 etc

Prompt pv_offset value expected 0 (for GMT), +2, -2 etc

I used Evalueate to push the function to the underline oracle database.

Formula for from date

EVALUATE('TO_CHAR(TO_DATE(%1,%2)+ @{pv_offset}{2}/24,%3)/*%4*/' AS CHAR ,'@{pv_date}{02-JAN-2017}','dd-MON-YYYY','YYYY-MM-DD HH:MI:SS am', "HYP_FDW_WMBEXR_V"."DIM_PERIOD_ID")

"HYP_FDW_WMBEXR_V"."DIM_PERIOD_ID" is the column name. Used as dummy. When evaluated it will be commented out /*"HYP_FDW_WMBEXR_V"."DIM_PERIOD_ID"*/ as it is part of %4.

Formula for to Date

EVALUATE('TO_CHAR(TO_DATE(%1,%2)+ @{pv_offset}{2}/24,%3)/*%4*/' AS CHAR ,'@{pv_date}{02-JAN-2017} 11:59:59 pm','dd-MON-yyyy HH:MI:SS pm','YYYY-MM-DD HH:MI:SS am', "HYP_FDW_WMBEXR_V"."DIM_PERIOD_ID")

Hope This Helps..

Thanks

Arijit

Id