*** 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.
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!
Check out the latest post:
Split Your Audience By Number Or Percentage In Customer Insights - Journeys
This is just 1 of 480 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.
Great article. Tried it and it worked. One small problem though. When I first enter a filter date (e.g. 01/09/2020) I can see that the text version updates (to 2020-09-01 in this case) and the the report, which uses the text version as its parameter, is correctly filtered. However, if I change the filter date and run the report again, there is no change to the report. I’ve made the text version visible and I can see that the problem is that the text version doesn’t change when the filter date is changed. It keeps its first calculated value. Is there any way to force the text version to recalculate when the filter date changes, so that a user can try another date or correct a mistake?
I think I’ve fixed it. If the text version of the parameter is visible then it only gets a value when the date version is first populated. If the text version is hidden, multiple dates can be used in the date version and each filters the report as expected. Apologies.
Awesome, glad you got it sorted! And no apologies needed! I only just had a chance to go through comments today, so apologies for getting back to you late!