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

Date formatting in RTF template: Excel Preview

Received Response
457
Views
5
Comments
Zshaikh
Zshaikh Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

Summary

Date formatting in RTF template: Excel Preview

Content

Hi All,

i have a date field auth_date as dd/MM/yyyy in rtf template, PDF preview gives expected output (i.e. auth_date as 23/Oct/2017) but excel preview gives same date in dd-MMM-yy format.

but i need dd/MMM/yyyy as date format in excel as well.

PDF output:

image

excel output:

image

any suggestion???

P.S: don't want to use Excel template as it does not support custom formatting and matrix table coding

Thanks and Regards,

Zeenat Shaikh

Answers

  • timdexter
    timdexter Rank 6 - Analytics Lead

    Hi

    When Excel opens it see and recognizes the date data and then applies the default date format to the data. Its out of the RTF templates hands at that point.

    AS the other reply has stated, the only way around it is to get the date format to a string using to_char so that Excel does not think its a date. You might still run into trouble as Excel appears to be pretty adept at recognizing data formats for you, even if they are wrong :0(

    cheers

    Tim

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    If Your source Type is SQL Query then convert auth_date into char , to_char(auth_date,'dd/mon/yyyy') ,

    OR

    use directly in the RTF template <?xdofx:to_date(SYSDATE,'dd-mon-yyyy')?>

  • Venkat Thota - BIP
    Venkat Thota - BIP Rank 7 - Analytics Coach

    Try this

    to_char(auth_date,'dd/mon/yyyy')

  • Zshaikh
    Zshaikh Rank 4 - Community Specialist

    Issue is solved.

    have added two spaces at the end of date format and finally excel is taking it as string.

    to_char(pu.createdate,'DD-MON-YYYY  ')

    Regards,

    Zeenat

  • Zshaikh
    Zshaikh Rank 4 - Community Specialist

    Hi Tim,

    thanks for reply, actually i had already tried using to_char in sql but unfortunately excel still recognizing it as date and displaying it in dd-MMM-yy

    Regards,

    Zeenat