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

Issue with Date Format in OTBI Report Export to Excel

Received Response
48
Views
8
Comments
Vinoth Mounagurusamy
Vinoth Mounagurusamy Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

Hello,

I am encountering an issue with an OTBI report. The 'Invoice Creation Date' column is formatted to display only the date (without the time) in the OTBI report. However, when I export the report to Excel using the 'Formatted' export option, the date still includes the time component (PFA screenshot 1 FYR). Additionally, when using the filter option in Excel, it displays a tree-level filter starting with the year, then the month, and finally the date, but it also shows the time for filtering (PFA screenshot 2 FYR).

The client requires the date to be displayed and filtered without the time component in Excel. Manually formatting the date column in Excel each time is not a viable solution due to the repetitive nature of the task.

Could you please advise on how to ensure that the date is exported to Excel without the time component? Any suggestions or workarounds to resolve this issue would be greatly appreciated.

Thank you!

Screenshot 1:

image.png

Screenshot 2:

image.png

Tagged:

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi, You can convert it to a string so it is not a datetime?

  • Bhaskar Konar
    Bhaskar Konar Rank 6 - Analytics Lead

    Hi @Vinoth Mounagurusamy,

    Are you using 'Data Format' tab to define the column as 'Date'? If yes, instead define the column as Date using Cast in the 'Column Formula'. It should not get the time part after download.

    CAST(Invoice Creation Date as DATE)

    Hope this help.

    Cheers,

  • Vinoth Mounagurusamy
    Vinoth Mounagurusamy Rank 2 - Community Beginner

    Hi @Bhaskar Konar @Nathan CCC, Thank you for your prompt responses.

    I have tried to define the 'Invoice Creation Date' column as a date using CAST; however, it is unfortunately not fetching the desired output. Therefore, I modified the column properties to retrieve only the date without the time component, and I am now able to achieve that.

    However, I am now stuck with the Excel export, where it still displays the time component in the filter options. I have been trying to resolve this issue but have been unsuccessful. Please help me with this."Additionally, in Excel, when we click on the filter option for that specific column, it shows a tree-level filter that starts with the year, then the month, and finally the date; however, it also includes time for filtering. The client does not want to see or filter by time.

    Best Regards,

    Vamsi S

  • Bhaskar Konar
    Bhaskar Konar Rank 6 - Analytics Lead

    Hi @Vinoth Mounagurusamy,

    You can define the column as String and then just take the 'YYYY-MM-DD' part with the help of Substring function and then again cast it to Date.

    CAST(SUBSTRING(CAST("- General Information"."Invoice Entered Date" AS char) FROM 1 FOR 10) AS DATE)

    It should only bring the Date part after download into the excel.

    Hope this help.

    Cheers,

  • Hassan El Bouihi-Oracle
    Hassan El Bouihi-Oracle Rank 5 - Community Champion

    Hi Vinoth,

    I used the Cast as Date and the export shows the date without the time as shown below:

    image.png
    image.png

    Thank you

    Hassan

  • Dimple-Nagesh-Support-Oracle
    Dimple-Nagesh-Support-Oracle Rank 6 - Analytics Lead

    Hey @Vinoth Mounagurusamy,

    Did you try this, edit the data set in BIP datamodel to locate the DATE column and edit column properties to select display DATE Only and then test the flow

    image.png

    Thanks,
    Dimple N

  • Vinoth Mounagurusamy
    Vinoth Mounagurusamy Rank 2 - Community Beginner

    Hi @Bhaskar Konar, @Hassan El Bouihi-Oracle,

    Thank you for your prompt response.

    I tried the suggested option, and it successfully fetches only the date without the time after exporting it to the Excel using 'Formatted' option. However, the date is incorrect in all the cases. For instance, when an invoice is created on November 21st at 12:17 AM, it incorrectly shows the previous day as November 20th.

    Thanks & Regards,

    Vamsi S

  • Vinoth Mounagurusamy
    Vinoth Mounagurusamy Rank 2 - Community Beginner

    Hi @Dimple-Nagesh-Support-Oracle,

    Thank you for your prompt response.

    We are currently working with the OTBI report. I have noted the information regarding the BI Publisher report and will surely refer to it if anything comes up related to BIP reports.

    Thanks & Regards,
    Vamsi S.