Recently I had a few cases in working with Power Automate and Dataverse Plugins where I had do stuff like sums and other things related to many records. Until I mostly gone the probably “most simple” way: Fetching all relevant records and iterate through them and do the calculation that was needed. Works okay but always felt a bit cumbersome 🤔

This time I wanted to try something new I always was there but until now had never tried: FetchXML aggregates. And wow, that stuff is amazing! 🔥

🏢 Business case

I’m working on a finance app in which you can manage different Budgets. One of the features is that it will create Budget Entries per month when you have recurring costs like rent, salary, etc. So we have a Budget record which can have unlimited Budget Entry records. Like this:

To get an overall view of the finances of a month or year you need to sum up all the Budget Entries for that period. Easily done if you fetch all those and iterate through them.

🔥 FetchXML Aggregations

First of all, what is FetchXML? It is the native query language for Dataverse. If you have ever used Advanced Search in a Model Driven App or defined a view for a table, in the backend you created a FetchXML query. They can look like this and define which entity you’re querying, what attributes you need and what filters should be applied:

<fetch mapping='logical'>   
   <entity name='account'>  
      <attribute name='accountid'/>   
      <attribute name='name'/>   
   </entity>  
</fetch>

This is a simple example from the Microsofts docs 👉 Use FetchXML to construct a query. You can also build incredibly powerful (and complex) queries with it.

The new part for me were aggregations. Not because they are hidden or not well documented, in fact the next page of the FetchXML documentation literally is Use FetchXML aggregation. What is aggregation in FetchXML?

In Microsoft Dataverse, FetchXML includes grouping and aggregation features that let you calculate sum, average min, max and count.

The following aggregate functions are supported:

  • sum
  • avg
  • min
  • max
  • count(*)
  • count(attribute name)

That does sound like exact the solution I need for my finance app 💪

🔨 Build it using XrmToolBox

The best way to get started in my experience it - as it is so often with the Power Platform - the XrmToolBox with its plugin FetchXML Builder by Jonas Rapp.

With it I was building the query to fetch all relevant Budget Entries, in this case all of the current year:

<fetch>
  <entity name="nckr_budgetentry" >
    <attribute name="nckr_dateyear" />
    <attribute name="nckr_datemonth" />
    <attribute name="nckr_amount" />
    <attribute name="nckr_name" />
    <filter>
      <condition attribute="nckr_dateyear" operator="eq" value="2021" />
    </filter>
  </entity>
</fetch>

Giving me all the information I need:

As I mentioned former me would now have build an iteration to sum those records up by nckrdateyear and nckr___datemonth. Current me however - after reading the FetchXML aggregation documentation - had a much better idea 🤯

Why not let Dataverse do the heavy lifting? It has all the information and using FetchXML I can tell it a) aggregate a sum and b) group it by these two fields.

In order to achieve that you have to change the following in your query:

  • Add “aggregate=true” to the element
  • Add an alias and “aggregate=sum” to attribute you want to sum up (in this case nckr_amount)
  • Add an alias and “groupby=true” to attributes by which you want to group. Can be more that one. (in this case nckr_dateyear and nckr_datemonth)
  • Make sure that all attributes either have an aggregate or a groupby. (in this case I remove nckr_name because it couldn’t be used for either)

Now the FetchXML looks like this:

<fetch aggregate="true" >
  <entity name="nckr_budgetentry" >
    <attribute name="nckr_dateyear" alias="dateyear" groupby="true" />
    <attribute name="nckr_datemonth" alias="datemonth" groupby="true" />
    <attribute name="nckr_amount" alias="amount" aggregate="sum" />
    <filter>
      <condition attribute="nckr_dateyear" operator="eq" value="2021" />
    </filter>
  </entity>
</fetch>

And the results like this:

Wooohooo! All the work already done and no need to write anymore logic 💓

💫 Using it in Power Automate & Plugins

Build it in the XrmToolBox is super convenient, but in most cases that’s not where we need to execute it for our apps. No worries, FetchXML and its aggregation feature is supported all over the Power Platform 🎉

Power Automate

The Microsoft Dataverse connector and its List rows action in Power Automate fully supports FetchXML and with it aggregations. You can enter the FetchXML you build in XrmToolBox under “Show Advanced options” -> “Fetch XML Query”:

Plugins

This is not just something for Low-Coders, why would you write custom code in a plugin to do this stuff when there is a native way which is also a lor more performant. And it’s also not hard to include it to your plugin code. Use a FetchExpression with that FetchXML in RetrieveMultiple call to get the results:

factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
service = factory.CreateOrganizationService(context.UserId);

var fetchData = new
{
nckr_dateyear = "2021"
};
var fetchXml = $@"
<fetch aggregate='true'>
    <entity name='nckr_budgetentry'>
    <attribute name='nckr_dateyear' alias='dateyear' groupby='true' />
    <attribute name='nckr_datemonth' alias='datemonth' groupby='true' />
    <attribute name='nckr_amount' alias='amount' aggregate='sum' />
    <filter>
        <condition attribute='nckr_dateyear' operator='eq' value='{fetchData.nckr_dateyear}'/>
    </filter>
    </entity>
</fetch>";

return service.RetrieveMultiple(new FetchExpression(fetchXml));

Quick Tip 💡 The XrmToolBox can create this C# code for you (under View -> FetchXML C# Code):

💪 Next steps

Cool stuff right? Go try it out! In hindsight I have no idea why I didn’t use it earlier, FetchXML aggregation could have saved me a lot time. So I hope that maybe also one of you has the same eureka moment as I had when I tried this out 🙂

These were the first small steps with aggregations, there is actually a lot more they can do and I will cover that in the next posts - stay tuned!