Aggregates and FetchXML Reports

Having written many SQL reports using SSRS, the first time I stepped in to the world of writing FetchXML reports for Microsoft Dynamics 365, I made a lot of assumptions. Using a SUM aggregate was one of them. I figured it would be as simple SUM(currencyvalue) just as it is in SQL. Instead, I got error after error. Spent ages Googling but kept running in to part answers, or articles which assumed you already knew a lot more. For anyone just starting out, here is how to use an aggregate (SUM, Count, Average) on a FetchXML Report.

What do you need to do?

The first thing is to make sure you declare this at the start of your query:

<fetch version=”1.0″ distinct=”true” aggregate=”true” returntotalrecordcount=”true” >

Make sure aggregate is in there and set to true. Next, make sure that all of the attributes in your query have an alias, and either an aggregate or a groupby clause. Took me a while to figure this out! You can see a few examples below. The alias can be anything, but make sure they make sense.

<attribute name=”totalamount” alias=”totalamount_sum” aggregate=”sum” />
<attribute name=”name” alias=”InvoiceSubject” groupby=”true” />
<attribute name=”statuscode” alias=”Status” groupby=”true” />

Use the FetchXML Builder

One thing that helped was using the FetchXML Builder plugin in the XRM Toolbox. If you haven’t already found this, make sure you check it out if you do anything related to CRM at all. It can be a life saver!

Once you have the modified query, you should have something like totalamount_sum which will be the original attribute (or field) you added. However, you will also have a second field, which would be totalamount_sumValue in this instance. That is what needs to be added to your report in order to get the correct value and avoid errors. Probably pretty obvious if you are not a beginner with FetchXML. Hope this helps out others in the same boat!