Oracle Transactional Business Intelligence

Products Banner

OTBI Report Date Format is not holding when exporting to Excel

270
Views
5
Comments

Summary

OTBI Report Date Format is not holding when exporting to Excel

Content

I have a report and the date format is set to the local time zone.  The date is formatted correctly on in the report on the results tab, but when exported to Excel it reverts back to a different time zone, losing the formatting.  I don't recall having this problem before.  Does anyone know how to resolve this issue? 

Comments

  • Let me add that when I exported to CSV format, the time zone formatting holds, so I have a workaround to my problem.  Does anyone know how to resolve the issue when exporting to Excel?

  • I had this exact opposite issue. Had problem with CSV as it's just a data dump but Excel will hold the format.  Here is the response from Oracle on an old SR

     "Hello Gopi,

    Unfortunately this cannot be done in CSV as I said CSV is just a data dump with no formatting and there is no option to format any data,
    If you need to export huge amount of data, you can explore working with etext templates in BI publisher for such reports.

    Regards, "

    We can change the timezone to default based on Locale value set under preference by going to Edit Formula > Data Format > Date Format > Locale Short date:time.  Also, make sure the preferences under BI for your user account is set to the correct timezone.

  • I formatted the date as you described.  I also experienced this issue in the reverse in the past.  Wow--I'm at a loss.

  • Found in a blog, https://community.oracle.com/thread/3679621?start=15&tstart=0

    Hi All,

    I have found a workaround for this issue.  Within the Answers report, I updated the date column to use a custom formula of 'd/mm/yyyy' and then selected to apply this to all columns with the same format. After doing this I could then export and view the date in the correct format.  I have run this past Oracle Support who confirmed it has worked and they say "that it looks like Custom format makes Excel 2007 Export to honour the changes in the Data format which it didn't when locale and regional settings where changed.  Multiple testing haven't revealed any issues, so workaround holds good."

    Hope this helps others.

    Lance

  • Moved to correct forum.