Retrieving Dates in FetchXML Aggregation for use in Dynamics CRM Reports

Zoe Sands, 23 July 2015

While writing a report using FetchXML, I needed to perform aggregation on a linked-entity where I encountered a problem in that fields that are not aggregated have to be grouped. For most fields this is fine; however, for DateTime data types this can be problematic as you cannot group by a date on its own. Instead, you need to group by each part of the date – i.e. the year, month, and day.

In my example below, I have an entity called "Client" which stores information such as the client's name, and the date they became a client (Start Date). Every meeting with the Client is recorded in an entity called "Client Meeting Summary", which includes the date of the meeting (Visit Date). I needed to build a report that showed the most recent visits for a client. In order to achieve this I used a FetchXML "Max" aggregate on the visit date field. Here is my first attempt at the query:

But this throws an error stating that a date needs to have a DateGrouping. I fixed this by adding a grouping for the Day, Month and Year parts of the date. I adjusted my query as follows:

This solved the grouping problem but did mean that each of date values had to be reassembled as a string. In some cases this would be find but if you want to maintain the DateTime format, this is not ideal.

To solve this issue, we can also aggregate the start date instead of applying a grouping since the query is going to return a single record anyway, due to the visit date aggregation. The final query looks like this:

This allows us to achieve the desired result, while still maintaining the ability to treat the start date as a true date attribute.