Date Parameter Issue With FetchXML

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.

Add 2 Parameters for each Date Control

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:

<filter type=’and’>
<condition attribute=’createdon’ operator=’on-or-after’ value=”@startdatevalue” />
<condition attribute=’createdon’ operator=’on-or-before’ value=”@enddatevalue” />
</filter>

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Articles