Using FetchXML Aggregation to Create a Rollup Plugin in Microsoft Dynamics CRM 2011

One of the most common requirements for a CRM implementation is for a rollup of data on a set of child records to the parent record.  CRM has several built in entities, e.g. Orders and Quotes, that implement rollups, but it does not natively support rolling up of fields for custom entities or for customizations of existing entities.  Fortunately, it is quite easy to implement a rollup using a simple plugin.  Generally, implementation of the rollup involves retrieving all of the child records related to the parent when one of the child records is created, updated, or deleted, calculating the rollup value and updating the parent.  This can be done by iterating through all of the child records after retrieving them using the organization service, but why not make things a little easier and use the new aggregation features of FetchXML to calculate the rollup value.

Aggregation support was added to FetchXML with the release of CRM 2011 and it supports the basic aggregation operations of sum, average, minimum, maximum, and count.  The query can be built dynamically in the plugin which allows us to create a generic configurable plugin that will work for most standard rollups.

The first piece of code that we will need is a method to build the aggregation query dynamically.  The following method will allow us to build the needed query.

   1: public string BuildRollupQuery(string rollupEntity, string rollupAttribute, string aggregateOperation, EntityReference parentLookup) {
   3:     return string.Format(
   4:         "<fetch distinct='false' mapping='logical' aggregate='true'>" +
   5:             "<entity name='{0}'>" +
   6:                 "<attribute name='{1}' aggregate='{2}' alias='{1}_rollup' />" +
   7:                 "<filter type='and'>" +
   8:                     "<filter type='and'>" +
   9:                         "<condition attribute='{3}' operator='eq' value='{4:B}' />" +
  10:                     "</filter>" +
  11:                 "</filter>" +
  12:             "</entity>" +
  13:         "</fetch>",
  14:         rollupEntity,
  15:         rollupAttribute,
  16:         aggregateOperation,
  17:         parentLookup.Name,
  18:         parentLookup.Id);
  19: }

This method will allow us to dynamically create the rollup query given the parameters that define the child entity and attribute that is being rolled up, the rollup operation to perform, and the lookup field to the parent of the child.  The query will aggregate the rollup attribute for all of the child records related to the parent.

Using this query we can call the following method to execute the query using the organization service and return the rollup value.

   1: public object GetRollupValue(IOrganizationService orgService, string rollupQuery, string rollupAttribute)
   2: {
   3:     return orgService
   4:         .RetrieveMultiple(new FetchExpression(rollupQuery))
   5:         .Entities
   6:         .First()
   7:         .GetAttributeValue<AliasedValue>(rollupAttribute + "_rollup")
   8:         .Value;
   9: }

As you can see, this method executes the query by passing it to the RetrieveMultiple method of the organization service and retrieves the rollup attribute returned from the first record returned by RetrieveMultiple.  Notice that the attribute value is returned by RetrieveMultiple as an AliasedValue.  This is how all aggregate attributes are returned by the RetrieveMultiple method.  I have made a few assumptions with this method for simplicity.  For example, if the attribute being rolled up, the Value property of the returned AliasedValue attribute would be returned as a Money object.  If that were the case it would be necessary to return the Value property of the Money object.  As I mentioned, for simplicity I have assumed that the attribute being rolled up is a simple type, i.e. decimal, int, or double.

We now have the value for our rollup and simply need to update the parent object.  Finally, we can make a call to the organization service to perform the update using the following method.

   1: public void UpdateParentRollupField(IOrganizationService orgService, EntityReference parentLookup, string parentRollupField, object rollupValue)
   2: {
   3:     Entity rollupParentEntity = new Entity(parentLookup.LogicalName);
   4:     rollupParentEntity.Id = parentLookup.Id;
   5:     rollupParentEntity[parentRollupField] = rollupValue;
   7:     orgService.Update(rollupParentEntity);           
   8: }

At this point we have all of the tools needed to create the rollup plugin.  In order for the plugin to operate properly and efficiently it is necessary to add a few checks and images to the plugin.  For example, it is only necessary to update the rollup if the parent lookup field was changed or the field being rolled up on the child record was changed.  These values can be checked by examining the InputParameters object that is passed to the plugin.  It will also be necessary to check to see if the parent lookup field for the rollup has changed.  If it has changed it will be necessary to update the rollup field on the old parent and the new parent.  The old parent lookup field value can be retrieved from a Pre Image entity that is registered for the update and delete messages.  The following snippet shows an example of the required checks and assumes that the Pre Image registered for the plugin is called “Target”.

   1: Entity targetInput = context.InputParameters["Target"] as Entity;
   2: Entity targetPreImage = context.PreEntityImages["Target"] as Entity;
   4: bool parentLookupChanged = targetInput.Contains(parentLookupFieldName);
   5: EntityReference parentLookupOldValue = targetPreImage.GetAttributeValue<EntityReference>(parentLookupFieldName);
   7: bool rollupFieldChanged = targetInput.Contains(rollupFieldName);

All that remains at this point is to wrap the code up in a plugin implementation and call the methods as needed.  The plugin can be made configurable by setting a configuration when registering the plugin using the plugin registration tool supplied with the SDK and retrieving the configuration string from the configuration argument passed to the plugin constructor.  Happy coding!