Oracle Communities

Oracle Analytics and AI

Child Item

In OTBI How to set TIMEZONE using Data format setting properties?

When we change the column properties in the Data Format tab regarding the TIMEZONE, it always reverts to the default setting. We cannot set the timezone as required.

The requirement is to remove the 'time' from the "Invoice Creation Date" column and display only the date. To achieve this, I opened the column properties of the same column to modify it accordingly. However, when I set the 'Date Format' to custom and modify the 'Custom Date Format' to remove the time, it does not work as expected. Right after I change the 'Date Format' to 'Custom', the 'Display Time Zone' changes from my required setting (GMT +8 Taipei) to the default.

How can I set the 'Display Time Zone' to my required setting (GMT +8 Taipei) and also configure the 'Custom Date Format' to display only the date without the time?

Additionally, I have not found a working solution even after following the instructions in 'Doc ID 2169806.1'. Please help me resolve this issue.

This below image shows when we set the 'Date Format' to another setting with date and time:

This below image shows when we set the 'Date Format' to 'Custom':

Thanks & Regards,

Vamsi S

Find more posts tagged with

Analytics

OTBI

Accepted answers

All comments

Bhaskar Konar

Hi @Vinoth Mounagurusamy,

If your requirement is to show only date part, you can easily achieve by defining the column as Date.

Say the Column Name is 'Invoice Creation Date' so in the edit formula tab you have to define like:

CAST(Invoice Creation Date as DATE)

It'll only return the Date part.

Hope this help.

Cheers,

Dimple-Nagesh-Support-Oracle

Dear Vinoth Mounagurusamy,

Can you please confirm the values of the following as Admin user:
Login to OSC --> Username --> Set preferences --> Regional --> TimeZone --> Check what is the value set (attach the screenshots of the same)
Login to BI --> username --> Myaccount --> General Preferences --> Timezone --> Should be greyed out and should show the same value as in OSC (attach the screenshots of the same)
Login to BI --> username --> Myaccount --> BI Preferences --> Timezone --> Should show the same value as in OSC (attach the screenshots of the same)

If all the above are not same, please set it to the same value and run the Scheduled process "Refresh Denormalised Time Dimension Table for BI"

Login to OSC --> Navigator --> Scheduled Process --> Dropdown --> search --> Refresh Denormalised Time Dimension Table for BI" --> OK --> Submit.
Wait until success, then logout and clear your browser cache, relogin and restest the issue and update the test results on the SR.

I will be waiting for your responses.

Thank You,
Regards,
Dimple Nagesh

Vinoth Mounagurusamy

Hi @Bhaskar Konar,

Thank you for your prompt response.

I tried the suggested option, and it successfully fetches only the date without the time. However, the date is incorrect in some cases. For instance, when an invoice is created on November 21st at 12:17 AM, it incorrectly shows the previous day as November 20th.

Working Solution:
As mentioned, the requirement is to display the date without the time, but it must reflect the UTC +8 timezone. Since our system is already set to Manila (UTC +8), I avoided modifying the 'Edit Formula' tab. Instead, I updated the Column Properties as follows:

Changed the 'Date Format' to Custom.
Removed the time and retained only the date in the 'Custom Date Format' field.

Although the 'Display Time Zone' defaults back, it still aligns with the required timezone (UTC +8). This solution was confirmed as correct after testing provided by the functional team.

Additional Query:
Could you please clarify the functionality of the option 'Save as system-wide default for "Invoice Creation Date"' in the column properties? If I select this, will it apply the modified settings (e.g., date format) globally across the system for all instances of "Invoice Creation Date"?

Additionally, if this option is applied, how can we revoke or undo it later? Your guidance on this would be greatly appreciated.

Thanks & Regards,
Vamsi S

Vinoth Mounagurusamy

Dear @Dimple-Nagesh-Support-Oracle,

Thank you for your detailed Prompt response and guidance. I truly appreciate the effort you've put into explaining the steps.

However, I’d like to let you know that I am not familiar with OSC and do not have access to it, so I’m unable to verify the configurations as outlined. Fortunately, I have identified a working solution for my issue.

If I encounter similar challenges in the future that require OSC involvement, I will refer to your instructions. Thank you again for your support!

Best regards,

Vamsi S

Dimple-Nagesh-Support-Oracle

Hello Vamsi,

My apologies, I missed your update. I meant to say once you login to application, you can navigate and check the steps shared, assuming you are using Fusion applications.

Thanks,
Dimple N

Vinoth Mounagurusamy

Hello @Dimple-Nagesh-Support-Oracle,

No worries, it’s all good! Noted on the steps shared and will make use of them when required. I do have another issue occurring in the same OTBI report and wanted to check if you’ve encountered something similar before. If so, your guidance would be greatly appreciated!

Issue link: "Issue with Date Format in OTBI Report Export to Excel — Oracle Analytics"

Thanks and regards,

Vamsi S

Bhaskar Konar

Hi @Vinoth Mounagurusamy,

I think you need to see the data in 'Data Time Zone'. Can you please check the attached XML / Catalog and filter with your invoice and see which column is giving you the correct data also match back the data with your application data. Ideally in my experience 'Data Time Zone' should be the correct one.

I'm getting following in my instance:

Column definitions:

— ootb - is the column as it is. — format - formatted using column format. —cast - used CAST to Date. —data time zone - used date time zone.

— Data Time Zone:

Data Time Zone will provide the actual scenario when data have been created if the time zones are different.

Here's the report catalog / xml. Please filter it with your invoice number and then check accordingly.

XML - Inovoice Creation Date - 051224.txt

Invoice Creation Date.zip

Hope this help.

Cheers,

User_MLN1R

Hi Everyone, I am looking for time conversion to be applied in date filter of analysis report.

RVohra

@User_MLN1R ,

As this thread was last updated more than a year back, it would be more appropriate to raise a new question for your problem. Please share more information about your environment, the products involved, and the exact problem you’re facing. This will help others give you a more accurate answer instead of making random guesses. Appreciate it!

Thanks