exceloracle-databasedatetimeobiee

Excel problem: Exported from OBIEE to Excel analysis adding time part to a date column


There is a date column used in OBIEE analysis. That column is declared as 'date' datatype in Oracle table, type in rpd is also 'DATE'. However, when exported to Excel, Excel is adding time values to the end of date part of this column. Here is a simple analysis:

enter image description here

The same analysis when exported into Excel:

enter image description here

As you can see, it is adding '5:00:00' to the end of '11.05.2022'. My guess is it is adding timezone values by default, which is the following in our OBIEE:

enter image description here

How can we prevent Excel from adding unnecessary values? Should we change settings in instanceconfig.xml file to use formatted value using 'UseFormattedValue' parameter? Or is there any simpler way to do this?


Solution

  • UseFormattedValue is the way to go because your problem isn't the Oracle side, but rather Microsoft trying to be "smart" in their interpretation in Excel. https://docs.oracle.com/middleware/bi12214/biee/BIESG/GUID-FF6954BA-2DE0-4422-BA58-05F32936F4FF.htm#BIESG9196