Using Rollup Fields in Microsoft Dynamics CRM 2015
There are many instances when it can be useful to see a rolled up summary of data from connected CRM records.
For example, a service manager may find it helpful to see at a glance the total number of cases that a contact has logged by referring to a field on the contact record which automatically rolls-up this data by counting the associated cases.
Marketing teams that have integrated CRM with an email marketing solution like dotMailer or Click Dimensions may want greater visibility of the total number of all email clicks from their recent campaigns for each contact. The aggregated sum total of clicks could be displayed in a new contact field and be shown in a list view.
Earlier versions of Microsoft Dynamics enabled data to be rolled up from one entity to another but for most requirements this needed custom code to be written.
With the CRM 2015 release, Microsoft has reduced this development effort by releasing a fine tuned feature which can be configured to rollup values from child to parent records in just a few steps.
Using a scenario, we’ll demonstrate how the new functionality will rollup the total value from opportunities won to the corresponding parent account.
As a result, this will help sales teams and account managers quickly assess the value of each account thereby avoiding the need to manually check the value on individual opportunities.
For this example we’ll go to Settings > Customisations > Customise the System and expand the account group within the Entities option to create a new field.
The new field will be called ‘Total Opportunity Value’ and will be set as a ‘rollup’ field type and has been set as a ‘currency’ attribute:
Click on “Edit” button beside Field type to configure rollup criteria.
In this example we will rollup the sum of actual revenue from all the ‘Won’ Opportunities that are linked to the parent Account record.
Save, Close and Publish the customisation.
A new rollup process will be set to run for the first time 12 hours after the field is created and can be subsequently changed.
The rollup job can then be configured to run every x hours by default. Administrators can easily change this by modifying the “recurring system job” by modifying the relevant rollup process.
Rollup fields that appear on CRM forms can also be manually refreshed by pressing the icon beside that field. Hover on the current value to see when the field was last recalculated.
Using our sales opportunity example, once published the new rollup field would be displayed on the account record as follows:
A maximum of 100 rollup fields can be defined per Organisation, and up to 10 rollup fields for entity.
Access to rollup data can be secured using regular field security permission to ensure that it is only available to authorized users if needed.
In the current CRM 2015 release, rollups can only be configured with one : many relationship, many : many relationships are not presently supported. Also, it’s not currently possible to rollup an already defined rollup field to another parent entity.
Dynamics CRM rollups supports SUM, COUNT, MIN and MAX calculations where there availability of each aggregation varies based on rollup field type selected.
These options offer considerable flexibility in determining how rollup data will be presented. Going back to our first example of counting the number of cases logged by a contact this could even be displayed in the format of a CRM bar chart with rolled up totals arranged monthly so fluctuations and peak usage can be more easily determined.
When a rollup field is created on any entity, the platform automatically creates two other fields to store metadata for the field calculation timestamp and status of last operation. These are suffixed with “_date” and “_status” accordingly.
“_date” stores a timestamp when the rollup field was last recalculated and “_status” shows result of operation in numeric value, these statuses reflect:
0. Not Calculated
2. Overflow Error
3. Other Error
4. Retry Limit Exceeded
5. Hierarchical Recursion Limit Reached
6. Loop Detected
We hope you find this a helpful introduction to rollup fields and you can identify how this new functionality can be applied to your own database.
We'd be delighted to hear from you and share examples of rollup fields that you've applied, or if you want to learn more about configuring these.
RELATED: Dynamics CRM Calculation Fields
Click here to see a demonstration of rollup fields and the other improvements in Dynamics CRM 2015.