Aggregations using FetchXML - Rishan Solutions

FetchXML is a powerful query language used in Microsoft Dataverse, and it allows you to retrieve and manipulate data from the platform in a flexible and structured way. One of its key features is its ability to perform aggregations, such as counting records, summing values, averaging, and more, which is crucial when working with large datasets. Aggregations in FetchXML are useful for scenarios like generating reports, calculating totals, and summarizing data.

In this guide, we’ll explore how to perform aggregations using FetchXML, focusing on common use cases such as count, sum, average, min, and max operations.

1. Overview of FetchXML Aggregations

FetchXML supports various aggregation functions that you can apply to fields within your data. Here are some of the most commonly used aggregation functions:

Count: Counts the number of records.
Sum: Sums the values of a field.
Average: Calculates the average value of a field.
Min: Returns the minimum value of a field.
Max: Returns the maximum value of a field.

These functions can be applied to numeric or date fields, allowing for a wide range of analytical operations directly within Dataverse.

2. Basic Syntax of Aggregations in FetchXML

The general syntax of FetchXML includes the use of the <aggregate> element, which allows you to specify which aggregation function to use on a specific field. Aggregations can be grouped and filtered, much like normal queries, and you can combine them with sorting or additional filtering.

Here’s the basic structure of an aggregated FetchXML query:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="revenue" aggregate="sum" alias="total_revenue" />
    <order attribute="name" descending="false" />
  </entity>
</fetch>

In this query:

The attribute name="revenue" is aggregated using the sum function.
The result is aliased as total_revenue, which is useful for accessing the aggregated result.
An order clause is added to sort the results by name.

3. Common Aggregation Functions in FetchXML

Count Aggregation

The count aggregation function is used to count the number of records in a given field. This is especially useful when you want to find the number of records meeting specific criteria.

Example: Count the number of contacts related to an account:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="contactid" aggregate="count" alias="contact_count" />
    <filter>
      <condition attribute="contactid" operator="neq" value="null" />
    </filter>
  </entity>
</fetch>

In this example:

We’re counting the number of contactid entries.
The result will be aliased as contact_count.

Sum Aggregation

The sum aggregation function is used to calculate the sum of numeric values from a specified field.

Example: Sum the total revenue of all accounts:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="revenue" aggregate="sum" alias="total_revenue" />
  </entity>
</fetch>

Here:

We’re summing the revenue field for all accounts.
The result will be aliased as total_revenue.

Average Aggregation

The avg (average) aggregation function calculates the average value of a numeric field.

Example: Calculate the average revenue of accounts:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="revenue" aggregate="avg" alias="average_revenue" />
  </entity>
</fetch>

This example calculates the average of the revenue field across all accounts and aliases it as average_revenue.

Min and Max Aggregations

The min and max aggregation functions are used to find the smallest and largest values, respectively, from a numeric or date field.

Example: Find the minimum and maximum revenue of all accounts:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="revenue" aggregate="min" alias="min_revenue" />
    <attribute name="revenue" aggregate="max" alias="max_revenue" />
  </entity>
</fetch>

Here:

The min function returns the lowest revenue value, aliased as min_revenue.
The max function returns the highest revenue value, aliased as max_revenue.

4. Grouping Aggregated Data

Sometimes, you may want to group your aggregated data by a specific field. For instance, if you want to sum the revenue for each account type, you can use the <link-entity> element to group the data based on related fields.

Example: Group revenue by account type:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="accounttypecode" />
    <attribute name="revenue" aggregate="sum" alias="total_revenue" />
    <groupby>
      <attribute name="accounttypecode" />
    </groupby>
  </entity>
</fetch>

This query will group the results by the accounttypecode field and sum the revenue for each group.

5. Using FetchXML with Power Pages

Once you’ve created your FetchXML query with the appropriate aggregations, you can use it within Power Pages in various ways:

Custom Web Resources: You can use JavaScript to execute FetchXML queries from Power Pages forms or pages and display the aggregated results.
Embedded Reports: Use the aggregated data in reports and dashboards embedded within Power Pages, possibly utilizing Power BI for visualization.
Data-Driven Components: You can display aggregated results in custom lists, grids, or charts, allowing users to interact with summarized data on the portal.

6. Considerations for Performance

While FetchXML is powerful, aggregation queries can become slow when dealing with large datasets. Here are some tips to optimize performance:

Use filters to narrow down data before applying aggregation functions.
Limit the number of records retrieved by using pagination.
Ensure appropriate indexes are available on the fields you’re aggregating (e.g., revenue).

7. Advanced Usage with Joins and Filters

You can also combine aggregation functions with advanced filtering and joining. For example, if you want to calculate the total revenue for accounts in a particular industry, you could use a filter along with a sum aggregation.

Example: Sum revenue for accounts in the “Technology” industry:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="revenue" aggregate="sum" alias="total_revenue" />
    <filter>
      <condition attribute="industrycode" operator="eq" value="100000000" />
    </filter>
  </entity>
</fetch>