*** NOTE: ALL INFORMATION IS ACCURATE AT DATE OF PUBLISHING ***
While writing FetchXML reports recently, I experienced a strange date parameter issue. Running the report was fine in Visual Studio, I could pick any date range and the report would run without errors. Once published to CRM, it failed under certain circumstances. For example, keep in mind that I am in the United Kingdom where the date format is dd/mm/yyyy:
- 01/08/2017 to 12/08/2017 = report runs
- 01/08/2017 to 13/08/2017 (or any day after the 13th) = report fails
After some thought it seemed like the dates were being switched from dd/mm/yyyy to mm/dd/yyyy. So instead of the dates being 1st August to 13th August, the report seemed to be running as January 8th to the 13th Month, 8th Day…. which obviously doesn’t exist. The following were all true:
- Report language – en-GB
- CRM environment language setting – English UK
- User language setting – English UK
- Laptop language setting – English UK
Based on the environment I needed to publish the report to, I needed to fix the issue within the report itself rather than on a reporting server. After much searching online, I finally found a solution that worked.
Even though I already had a start and end date parameter, I needed to try and format them so the report would work regardless of the end users location or language settings. To do this, we needed to allow the user to pick the dates, then pass those dates through to some hidden parameters which could then be formatted with yyyy/MM/dd. I needed a start date and an end date so added the following:
- startdate (Date Time type) – this parameter will be displayed to the end user as a date control
- enddate (Date Time type) – this parameter will be displayed to the end user as a date control
- startdatevalue (text string) this parameter will be hidden. Set the default value as: =Format(Parameters!startdate.Value, “yyyy-MM-dd”)
- enddatevalue (text string) this parameter will be hidden. Set the default value as: =Format(Parameters!enddate.Value, “yyyy-MM-dd”)
In the FetchXML query, use the hidden parameters you created:
<condition attribute=’createdon’ operator=’on-or-after’ value=”@startdatevalue” />
<condition attribute=’createdon’ operator=’on-or-before’ value=”@enddatevalue” />
The solution will use the yyyy-MM-dd format to fetch the record in fetch xml but it will show the report result in dd/mm/yyyy format (or the format on the users machine/CRM settings). Job done!
Check out the latest post:
Installing Updates For D365 Marketing AKA Customer Insights Journeys
This is just 1 of 409 articles. You can browse through all of them by going to the main blog page, or navigate through different categories to find more content you are interested in. You can also subscribe and get new blog posts emailed to you directly.