OTBI Report Date Format changes when exporting to Excel

ZenG

Content

We are trying to change the formatting of a column in OTBI report by changing the column properties of each date columns. We are using the following format: 

Date Format: Custom

Custom Date Format: MM/dd/yyyy [TZ:(GMT-06:00) Central Time (US & Canada)]

Display Time Zone: Default

 

The dates are showing correctly in the results tab, but when exporting to excel, it loses the formatting, and becomes General format

Example:

Date when viewed on results tab: 03/01/2021 CDT

Date when exported to excel: 44348.10469

 

 

I read on this archived post that Oracle haven't rolled out yet a solution for this based on the last comment:

https://cloudcustomerconnect.oracle.com/posts/2f386363f4

 

Can anyone confirm if they encounter similar issue? By the way, we are on 21C version as of the of this post.

 

 

Version

21C

Pulkit Kumar Jain

Hi,

You can use these 3 options to export the OTBI Analysis.

1. You can export this in Excel (.xlsx) format and after select the date column in excel and change the formatting of the column in excel.

2. You can export the analysis in MHTML(.mht) format then open the MHTML file in excel then you are able to see the date in the same format you want MM/dd/yyyy [TZ:(GMT-06:00) Central Time (US & Canada)].

3. You can export the analysis in XML and import XML in your excel, but in this, you can't format the date in your MM/dd/yyyy [TZ:(GMT-06:00) Central Time (US & Canada)] format.

 

Please find the screenshot for 2nd option sample output.

 

 

Nathan CCC

Hi, A data type of date does not really have a format so a date is a date is a date. The format you choose in column properties is how you want to display the date in your view to your user. 

So the question is do you want the value in your download to be a "real" date or a string with a particular date format? 

Are you interested in just getting data (CSV/XML) and are you interested in nice format information to interact with a user (Excel)? Remember that when you export to the formats outside the "Data" folder, like Excel and PDF, then system picks up the format you have in the current layout view you are viewing, so you can have 2 views in your analysis with perhaps a view selector, depending what view you are on, you get a different format in your download. But the export options in the "Data Folder" like CSV and XML work different, they ignore any views you have built in your results tab, and they just use the logical sql from your criteria tab, for example, if you display 2 of 3 columns in results tab in a view, you get  2 cols export to excel, but all 3 export to CSV/XML. 

Anyway here are some "hacks" to explore - depending on if you need a "date" or a "string" in Excel

1. If you choose an international standards organisation date format like yyyy-mm-dd in your column properties data format on a date column

then you can I think fool export to excel to make the column "Date" not "General". Give it a go?  

2. Or you can use edit formula to use functions to cast your date to a string in your logical sql so you are in control before it gets sent to Excel - Excel will treat this as a string "General" but it will format  

Here are some date time logical sql funstions https://docs.oracle.com/middleware/11119/biee/BIEUG/appsql.htm#i1007088

For example, to use the default date format setup by your administrator in your bi settings 

select all 0 s_0, "Time"."Date" as d, cast("Time"."Date" as character) s from "User System Usage"