Aggregate Fetch XML Queries in Dynamics CRM 2011 - COUNT

Roshan Mehta, 16 June 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 retrieve the total number of Opportunities where the Estimated Revenue is greater than $50,000 from the records below.

The expected value here is 5.

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 COUNT function on by using the aggregate=”COUNT” and alias=”count” parameters. I have also added a filter to the query to make sure it only returns records where the estimated revenue is greater than $50,000.

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.