Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics 365 | Integration, Dataverse... / Formatting Date fields...
Microsoft Dynamics 365 | Integration, Dataverse...
Suggested answer

Formatting Date fields in SSRS reports

(0) ShareShare
ReportReport
Posted on by unicorn_tech 89

Hello. Any date field from Dynamics 365 that I bring in to an SSRS report with a FetchXML data set, is not recognized as a date.  I can display the data without any issue, but I can't format the field to some how convert the string into a date field. I suspect it has to do with the time value in the field.

I have tried the formula: 
=Format(CDate(Left(Fields!createdon.Value,10)))

This works with many of the values, but unfortunatley, not all of the values (perhaps because they don't have a time in the date field?)

timestamp.PNG

Below is an example of the above column, and the data without any formatting.  I can't see a reason for why the formula would work on some of the values, but not the others.  This is the "Created On" field that is in all entities.

 date-with-both.PNG

I would appreciate suggestions for how to solve this.  Thank you!

  • unicorn_tech Profile Picture
    unicorn_tech 89 on at
    RE: Formatting Date fields in SSRS reports

    Interesting. Though I'm not using an IF function here. My goal is just to sort by date, which I can't do if the date can't be formatted as such.

  • Suggested answer
    Nya Profile Picture
    Nya 29,058 on at
    RE: Formatting Date fields in SSRS reports

    Hi,

    Some values in an expression can evaluate to null or undefined at report processing time. This can create run-time errors that result in #Error displaying in the text box instead of the evaluated expression. The IIF function is particularly sensitive to this behavior because, unlike an If-Then-Else statement, each part of the IIF statement is evaluated (including function calls) before being passed to the routine that tests for true or false. The statement =IIF(Fields!Sales.Value is NOTHING, 0, Fields!Sales.Value) generates #Error in the rendered report if Fields!Sales.Value is NOTHING.

    Please refer to the following section which may help you.

    Suppressing Null or Zero Values at Run Time

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,791 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,488 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Get batch job history log from D365 FO to Power Automate

Product updates

Dynamics 365 release plans