Aggregate Fetch XML Queries in Dynamics CRM 2011 - SUM

Roshan Mehta, 19 May 2012

Previously, I have taken you through data querying using FetchXML in Microsoft Dynamics CRM 2011. FetchXML supports all features of QueryExpression plus the ability to perform aggregates such as sum, average, min, max, and count. This is extremely useful and doesn’t require the developer to perform their own calculations when querying data as they would when using other querying methods such as QueryExpression or QueryByAttribute. There is one disadvantage when using aggregates with FetchXML – only one aggregate can be specified in a single query.

Let’s take a look at an example of how to calculate the total estimated revenue for all Opportunities with the following record set:

The expected value here is $789,000.
 

The FetchXML query requires the distinct=”false” and aggregate=”true” parameters. We also have to remove any <attribute order…/> definitions as these are not allowed in an aggregate FetchXML query. I have included the estimatedvalue column in my query and specified that this is the field we want to perform the SUM function on by using the aggregate=”SUM” and alias=”total” parameters.

We then write our FetchExpression as we normally would, but since we are not returning a CRM field, we need to use an AliasedValue and cast it to the appropriate type. The output looks like this:
 

Using this method is a nice way to perform calculations on your CRM data without having to loop through all returned records and write your own calculation logic. We will explore the other aggregate functions in later posts.