Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

User Time-Zone in BI Publisher Excel Template

222
Views
3
Comments
Alberto Romani-Oracle
Alberto Romani-Oracle Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

Summary

User Time-Zone Management in Bi Publisher

Content

Hi, I am creating a report which has to extract a field which is managed with TIMESTAMP. Since this report should be used across many countries, I would like to display the filed with the correct time-zone of the user who launches the report.

I am developing a standard BI Publisher Data Model and an Excel template as layout. I have reviewed many whitepaper, which suggested to work with following attributes in the data model:

SESSIONTIMEZONE, DBTIMEZONE, TZ_OFFSET(SESSIONTIMEZONE)

Unfortunatly these values always retrieved the same value, independently on the User.

Then I reviewed if there was the possibility to work on the layout. The only suggestion that I received was applicable on RTF and XPT. I should have used the following formula:

<?format-date:STARTDATETIME;'SHORT_TIME_TZ'?>

Does anyone have a suggestion on what I could apply to solve my issue when producing an Excel Template layout?

Regards

Alberto

Version

20A (11.13.20.01.0)

Tagged:

Comments

  • FPonte
    FPonte Rank 6 - Analytics Lead

    Hi Alberto.

    This is always tricky to setup.

    I used this kind of report to check the proper configuration that worked for my users. You my try on your setup.

    Also check the DM column configuration and play with User Preferences.

    I think you need to logoff and logon again for each time you change the User Preferences.

    Hope this help.

    Cheers

    Fernando

    BIP_TMZ1.JPG

  • FPonte
    FPonte Rank 6 - Analytics Lead

    I'm in NZ so I change my Timezone to Sydney.

    The first image is the result I got.

     

    BIP_TMZ1_PREF.JPG

  • Robert Fighiroae-Oracle
    Robert Fighiroae-Oracle Rank 4 - Community Specialist

    Hi Alberto,

    Currently, this can be achieved via RFT or XPT templates; guide: Fusion BI Publisher - Time Zones Display - Best Practices of Coding (Doc ID 2256176.1)

     

    Excel cannot recognize canonical date format. If the date format in the XML data is in canonical format, that is, YYYY-MM-DDThh:mm:ss+HH:MM, you must apply EXCEL functions, calculations and formats to display it properly (e.g. REPLACE, SUBSTR).

    Please check the example from the documentation: 6.5 Formatting Dates -> "Formatting a Canonical Date in Excel"

    https://docs.oracle.com/middleware/11119/bip/BIPRD/create_excel_tmpl.htm#CHDJAJCA

    Best regards,

    Robert