Aggregate Calculations in Microsoft Dynamics CRM 2015

Following on from the Hierarchy View in Microsoft Dynamics CRM 2015, another nifty new component that is turning heads in the CRM world is the Aggregate/Rollup field functionality. Prior to 2015, if you wanted to do a calculation on a field or rollup some business information, you had to write code to do it and this limitation could end up being quite expensive for what amounted to a pretty simple requirement. In truth, it is very common for a Sales Team to want to know the aggregate cost amount for opportunity products right there on the Opportunity and there was previously no easy way to achieve this. Microsoft have included new functionality that makes this really quite simple to implement directly within the User Interface.

As part of the standard field definition that we are all familiar with, power users will notice a new attribute called Field Type. This is a drop down with some values dependent on the Data Type of the field being specified. As we know, Data Types can be one of the following :

  1. Single Line of Text
  2. Option Set
  3. Two Options
  4. Image
  5. Whole Number
  6. Floating Point Number
  7. Decimal Number
  8. Currency
  9. Multiple Lines of Text
  10. Date and Time
  11. Lookup

The Field Type drop down changes values dependent on the Data Type chosen above. Depending on that, the values will be either Simple (pre 2015 standard type field), Calculated (new for 2015) or Rollup (new for 2015).

clip_image001

As with all field definitions, once the field is created, these values are fixed and cannot be changed later. You cannot, for instance, change a calculated field back into a simple type field at a later date, although you can obviously edit the calculation ‘definition’ going forwards. It is necessary therefore to make sure that all fields have their correct purpose defined right from the very beginning of design time which does add some small overhead to the entire project.

Calculated Fields

Calculated fields can be used for almost any data type except Text, Image or Lookup fields. Calculations are performed synchronously on any field in the Form when the Save event is invoked.

Calculations are not necessarily arithmetical in nature and there are some useful built in functions though such as :

  1. ADDDAYS, ADDHOURS, ADDMONTHS, ADDWEEKS, ADDYEARS
  2. CONCAT
  3. SUBTRACTDAYS, SUBTRACTHOURS, SUBTRACTMONTHS, SUBTRACTWEEKS, SUBTRACTYEARS
  4. TRIMLEFT, TRIMRIGHT

For the purposes of this demonstration, lets suppose that Orders can have a special discount applied based on the total amount of the order. We will need two new fields in the entity, one to record the special discount value and one to show the calculated discount. In the definition of the Special Discount Value (the field that will invoke the calculation) we set the Field Type to Calculated and select the EDIT button to create the definition for the calculation. Note that the Special Discount field will be a Simple type field since it is merely required to hold the initial value.

clip_image002

When the Calculated Field editor is opened, there is a standard IF….THEN…ELSE set up within which values can be selected. In the case of our scenario, if the Total Amount has value, then the Special Discount Value will equal Total Amount minus Special Discount. As you can see, the calculation is specified in English to help with understanding the logic.

clip_image003

The Action selection also usefully has a type of intellisense which helps you to correctly select the fields as necessary.

clip_image004

It is entirely possible to have quite complicated calculations and in this way, percentages, weighting and calculated dates are potential scenarios that can be addressed easily. Multiple conditions are supported as well as an ELSE and ELSE IF clause.

clip_image005

Once you have your definition specified, all that is left is to view the Form and invoke the calculation via the Save event. In this case, a £5 special discount was applied and the Special Discount Value now shows Total Amount minus the Special Discount.

clip_image006

Calculated fields can be used like any other field and lend themselves to being included in Views (for instance). In this case, My Orders view shows us that two special discounts were applied to these orders. Both fields can be sorted as shown with the ‘up arrow’ symbol.

clip_image007

Rollup Fields

Let’s suppose that the Sales Team would like to know the aggregate value of all Opportunity Products amounts associated with an Opportunity and that this value should be displayed in a field on the Opportunity Form. As we have observed, previously, this would have required a plugin or workflow to manage this information, but now, it can be achieved with just a few clicks from within the UI.

Rollup fields are designed to provide aggregate information from child records and so, we can use this Field Type as a definition for the Rollup field we are going to create. As with Calculated fields there is a limitation to the Data Types that are supported for rollup since you could not sensibly rollup a Text field for instance. In this case, only Whole Number, Decimal, Currency and DateTime fields are supported and only for certain operations as can be seen below.

im
age

 

In our scenario above, we can see that Currency and Sum are supported which is exactly what we would like to do.

First of all, we need to create the destination field for the rollup value that will sit on the Opportunity Form. We do that in exactly the same way as normal with the slight deviation that we choose the Rollup field type from the Field Type drop down.

clip_image008

Once we have that definition, we can choose the EDIT button and start to define the actual information that will be used. The source is the Opportunity since the field rests on the Opportunity entity and Use Hierarchy is No. These values are completely filled in by CRM. If there is no Hierarchy already set up for the entity, then the field will stay as NO and you cannot edit it. If you have created a hierarchy for the entity, then the field can be edited using the relationship definition created as part of that process. If Hierarchy is used, then the rollup will use the values of fields within that hierarchy as well and this can also be optionally filtered.

clip_image009

In our scenario, the related entity will be the Opportunity Product and the Aggregation will be SUM the Opportunity Product Amount field.

clip_image010

Usefully, there is a filter which is optional, so for instance, you might want to limit the rule to only SUM opportunity products which have a quantity greater than 1 or perhaps the CreatedOn is later than a certain date. This definition can be changed later if the user wishes to add another filter or different criteria but the field cannot be changed from a rollup type.

clip_image011

Once the definition has been created, all that remains is to place the field on the form and open the record.

clip_image012

Rollup Fields : Considerations

Unlike Calculated fields, rollups are asynchronous in nature which means that the actual calculation is updated every hour as part of the Asynchronous Jobs. There is the ability to manually invoke the calculation by clicking on the ‘recalculate’ icon in the field of the form though. The API also allows for the developer to invoke a recalculation as necessary. Rollup fields are also stored in the database just like any traditional field and so would be available to charts, reports, security and views. For instance, if we wanted to extend the My Open Opportunity View, we could add this field to that and see it in the Grid like any other field.

clip_image013

Interestingly, it is perfectly feasible for Rollup fields to be part of Calculated fields and vice versa. Some complicated business logic can be satisfied with very little effort on the part of the user. For instance, if we wanted to know the total value of discounts applied to orders (see calculated fields) associated with an account, we could create a rollup field with this definition.

clip_image014

If we added the field to the account entity and manually invoked the update :

clip_image015

Limitations

As with all new functionality, there are some limitations to be considered.

In the general sense, workflows are not triggered by field updates unfortunately and this is certainly an enhancement we would like to see. To add to the general complications, latest values of calculated fields area are not available in the plugin pipeline. This could cause some consternation if you are trying to invoke a plugin based on a value in one of those calculated fields, but realistically speaking, if you want anything more complicated than the UI provides for, you would use custom development in the first place and generate the same calculation there. Obviously, development still has a role to play in CRM 2015 and wont be changing any time soon.

Calculated fields specifically are subject to the following limitations :

  1. Can only go one level up in an N:1 relationship
  2. Can only have all ANDs or all ORS in conditional logic.
  3. Not available for offline
  4. Calculations only refresh OnSave of the Form

Rollup fields are subject to the following :

  1. Only available using a single directly related 1:N related entity (Opportunity and Opportunity Products for instance)
  2. Rollup using another rollup field is not supported.
  3. Rollups are asynchronous and so the latest value will not always be available if changes are made to child records.

Leave a Reply