Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

export date columns to excel 2007+ one day behind with timestamp 12.2.1.4

Received Response
141
Views
6
Comments
Yasmeen Wilde
Yasmeen Wilde Rank 1 - Community Starter
edited Aug 13, 2024 7:24PM in Oracle Analytics Cloud and Server

Does anyone has issue of exporting date columns to excel 2003+ in obiee 12.2.1.4?

All my date columns were exported to excel 1 day behind, example 1/6/2018 date value (date in database and rpd, it is not timestamp and not override in analysis)

from analysis is exported to excel 2007+ as 1/5/2018  7:00:00 PM. My excel version is 2013.

This lead me to believe it is timezone issue and not format issue...

Here are the troubleshooting steps I've tried:

1. My instanceconfig file does not have timezone tag. We never had timezone tag in 11g either and it worked fine without it.

User preferences tab timezone under my account is unknown which is expected because i didn't set any timezone in instanceconfig file.

According to the article below, if timezone is not set in obiee instanceconfig file, it automatically gets timezone from the database server.

OBIEE 12c: Database Data Time Is Not Taken And Data Timezone Is Shifted In The Analysis Results (Doc ID 2254874.1)

I verified database timezone, it is correct set as central time. All our users are in central time, with our desktop set as central time so we never had to customize timezone for various users.

2. I set my user preference timezone in obiee to central time and export again. Same issue but instead of exporting as 1/5/2018  7:00:00 PM it exported as 1/5/2018  6:00:00 PM.

3. Add timezone -DefaultDataOffset, defaultuserpreferred and defaultdatadisplay tags in instanceconfig file, export again no impact either.

4. Change datavalue tag in download tag to useformattedvalue instead of userawvalue. This fixed the date column export, exported correctly as shown in the analysis but it broke all the number columns export. All number columns were exported as text.

This won't work because I need the number columns exported as raw values.

5. Tried everything as documented on articles, admin doc and googled. I have a ticket opened with oracle sr for a week now.

Time Stamp Values in OBIEE is an Hour Different from the Database ( Doc ID 2010182.1 )

How To Use The Timezone Functionality In OBIEE 10 / 11g / 12c ( Doc ID 953881.1 )

OBIEE11g/12c : Excel 2007+ Export Does Not Honour OBIEE Time Zone Preference and/or Offset Configuration ( Doc ID 2375514.1 )

How To Globally Change Timezone Settings In OBIEE 12C For BI Publisher ( Doc ID 2242862.1 )

OBIEE 12c: Database Data Time Is Not Taken And Data Timezone Is Shifted In The Analysis Results ( Doc ID 2254874.1 )

Any guidance greatly appreciated.

Answers

  • 3689652
    3689652 Rank 1 - Community Starter

    Yasmeen Wild,

    We have to apply the patch to fix the issue. Which is a tool bug.

    Issues Details:

    Exported excel reports are showing dates one day earlier than what is displayed in answers

    For example, Selecting 'Greater Than Or Equal To' for the filter with a date of 6/1/2017 for results in the following:

    OBIEE report starts with 6/1/2017:

    Here are the patch details.

    To fix this issue, do the following:

    • Download one-off Patch 27286825: OTBI REPORT ARE SHOWING DATES ONE DAY EARLIER THAN THE WHAT IS IN THE SYSTEM from My Oracle Support.
    • Read the Readme of the patch carefully before installing the patch.
    • Install this patch on top of OBI EE 12.2.1.4 on a development or test server and fully test the bug fix and ensure that there are no unexpected results after applying the patch.
    • Once you are satisfied that the patch fixes the bug and does not cause any unexpected behavior in other product areas, you may promote the patch to your production environment(s).

    Thanks,

    Vinay

  • Gopi Mannava
    Gopi Mannava Rank 3 - Community Apprentice

    It appears to be working correctly now.  Thanks for your help Vinay.

    Regards,

    Gopi.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    If it is working now can you please mark the appropriate answer as "Correct"? This would help other forum users. Please follow forum etiquette and participate in the laid out ways.

  • Gopi Mannava
    Gopi Mannava Rank 3 - Community Apprentice

    Correct the above patch is working.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Edit: No, wait this isn't your thread. Never mind my comment then - the OP has to do this and manage his thread.

  • 3551663
    3551663 Rank 3 - Community Apprentice

    Hi Gopi,

    we are getting the similar kind of issue in 12.2.1.4

    For Ex GL Period name displaying the data as Apr-18,May-18etc in answers and when we export to excel its going behind one month like Mar-18,Apr-18 etc.

    We have applied the patch Patch 27286825 but still didn't fixed.

    we are not facing this issue in OBIEE 12.2.1.2 or 11g versions.

    Any suggestions would be really helpful.

    Thanks,