how to set a user preference date format in BIP for Excel Template — Oracle Analytics

Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

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

how to set a user preference date format in BIP for Excel Template

Received Response
455
Views
5
Comments
Abhiengine
Abhiengine Rank 2 - Community Beginner
edited Aug 13, 2024 7:30PM in Oracle Fusion Data Intelligence

Summary:

Either it should be mm/yy/dd or dd/mon/yyyy etc whatever user has setup in his preferences

Content (required):

Can you please help me in how can the date format should be set as per user preferences in BIP report.

in my case I'm displaying the PO creation date in Excel Template, so in the output the date format should be user preferences.

Answers

  • Abhiengine
    Abhiengine Rank 2 - Community Beginner

    for the RTF, I am aware <?format-date:CreationDate; 'XDODEFDATE'?> This only works for rtf template.


    Needs for excel template as above is not working on excel template in BIP

  • Sumanth V -Oracle
    Sumanth V -Oracle Rank 8 - Analytics Strategist

    @User_7885W

    Please check the below document and see if it helps with your requirement.

    How To Set Date As Per User Preference Time zone in Fusion Applications BI Publisher Report (Doc ID 2864734.1)

  • Abhiengine
    Abhiengine Rank 2 - Community Beginner

    Hi Sumanth


    Use the following sample SQL:


    SELECT TO_CHAR(FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone (select :xdo_user_timezone from dual),'YYYY-MM-DD HH24:MI:SS ') creation_date, creation_date creation_date2, :XDO_USER_TIMEZONE, :xdo_user_name name FROM ap_invoices_all where

    TRUNC(FROM_TZ(creation_date, DBTIMEZONE) at time zone (select :xdo_user_timezone from dual)) <=:p_date order by creation_date

     

    This query only changed the time zone but not the date format. Here we are specifying the date format like

    TO_CHAR(FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone (select :xdo_user_timezone from dual),'YYYY-MM-DD HH24:MI:SS ') .

    I need to format date according to user preference , not by hard coding.

  • Kalyan Reddy
    Kalyan Reddy Rank 2 - Community Beginner

    @Abhiengine Are you able to derive the date format from User preference? we do have the same requirement?

    Thanks in advance.

  • User_VO7FP
    User_VO7FP Rank 2 - Community Beginner

    @Abhiengine Are you able to derive the date format from User preference? we do have the same requirement?

    Thanks in advance.