How to fix Timezone issue with dates in Oracle Fusion BI Publisher reports ~ Geek-Bench

Typically, the timezone issues in Oracle Fusion BI Publisher reports often arise due to differences between the database timezone, BI Publisher server timezone, and user session timezone.

When you use dates in Oracle Fusion BI Publisher reports, either in where conditions to compare with effective dates or filtering out the data based on date parameters of the report, sometimes the report shows data pertaining to the previous day instead of today's date that was passed in parameter (or was the actual effective date).

The reason behind this is the conflict between the Timezone set at user profile level and the Timezone at BI server level.

By default, BI Publisher may use the database timezone, which can lead to discrepancies when users from different regions view reports.

There are a few ways to fix this problem.

Method 1 - Report Properties:

- Open the report definition

- Navigate to Properties

- Navigate to Formatting tab

- Scroll down to FO Processing section

- Find the option named Report Timezone and set it to User

- Save the report

- With this change, the report should work based on the user Timezone in all the dates. If the user A has Timezone set to EST then the output will be according to that, if another user B has Timezone set to CST then that output will be based on Central Timezone.

Method 2 - Data Model:

- If one wants BI server to use a dedicated Timezone in all cases, then the configuration has to be made at Data Model level

- Navigate to the desired data model of the report

- Edit the query and modify the reference to the date column as shown below. I'm using SYSDATE as the basis column for my example and Central Timezone for my example.

SELECT

to_char(CAST((from_tz(CAST(sysdate AS TIMESTAMP),

'+00:00') AT TIME ZONE 'America/Chicago') AS DATE),

'DD-MON-YYYY') AS formatted_date

FROM

dual

- View the output and we should see the date will be correctly shown based on Central Timezone

Method 3 - Template:

- If the timezone issue is happening only with the displayed format then we can update the BI Publisher RTF or XSL template to apply the correct timezone format.

- RTF Template: We have to use <?format-date:....> function to format dates correctly.

For example:

<?format-date:CREATION_DATE;'MM/dd/yyyy HH:mm:ss z';'America/New_York'?>

- XSL Template: We can use format-dateTime function to format dates correctly.

For example:

<xsl:value-of select="format-dateTime(CREATION_DATE, '[MM/dd/yyyy HH:mm:ss z]', 'America/New_York')"/>

Additional Checks:

In addition to all above methods, it's also recommended to verify if the Fusion application environment is reflecting the correct timezone or not.

To ensure this, check below steps:

- Navigate to Setup and Maintenance.