As a sales manager, you might want to take a look at a complex account that has multiple subsidiaries, with insight into the consolidated sales situation. A hierarchical visualisation such as the following is nice to have:
You can accomplish this with a combination of calculated- and rollup fields.
I will talk you through this step-by-step:
- On the Opportunity entity, create a calculated currency field called Weighted Revenue – Weighted Revenue is defined as Probability x Estimated Value:
- Edit the new field to specify the calculation formula:
- Publish the Opportunity entity.
- On the Account entity, create a rollup field named Opportunities (Unweighted):
Rollups are for calculations across one-to-many relationships (like the sum function in Microsoft Excel).
- Edit the new field to specify the rollup calculation formula. Set Use Hierarchy to ‘YES’ and then specify the related entity:
In this example it is Opportunities (Account). Specify the relevant relationship field in your case.
- Specify that the rollup must filter to include only Open Opportunities:
- Specify the Aggregate function, in this case, the sum of the Estimated Revenue on the Opportunity field:
- Create an Opportunities (Weighted) rollup field on the Account entity by applying the same steps as above (steps 4 to 7), except that this time, the aggregate function is the sum of the Weighted Revenue on the Opportunity field:
- Customise the display of the hierarchical visualisation by adding the two new fields on the Account Hierarchy Tile form:
- Also add these two rollup fields on the Account form and publish the Account entity.
- To see the rollup fields in action, navigate to a view of your Accounts:
Click a ‘View Hierarchy’ icon (on the left of the Accounts) and notice two problems — no totals are displayed and the field labels are truncated:
- Edit the labels on the Account Hierarchy Tile form to shorten the text. For example, use ‘Odds’ instead of ‘Probability’:
- Open an account record to have a look at the calculated rollup fields. Hovering over the fields displays a tooltip that the field has not been calculated yet:
Rollups automatically calculate across all affected records 12 hours after being created for the first time. After that, they recalculate on an hourly basis, using asynchronous system jobs.
- To calculate values for this record immediately, click the Refresh button on each field.
Once a rollup field is calculated on a record, it is saved on the record. Note that this updates only this one record.
- Calculate Opportunity fields on a sampling of your Account records and click the Hierarchy icon (top right on the record window) to have a look at the Visualisation:
- To force the calculation to happen sooner across all the records, navigate to Settings/Systems Jobs. Filter with ‘Recurring System Jobs’ and double-click the ‘Mass Calculate…’ job for one of your new rollup fields:
- Choose ‘Actions/Postpone’(sic):
- Move the job run to the current date and time and OK:
- Run the equivalent ‘Mass Calculate…’ job for the other rollup.
On completion the Hierarchical Visualisation shows the correct data for all your records.
More About Rollup Fields
According to MSDN article http://msdn.microsoft.com/en-us/library/dn817863(v=crm.7).aspx#BKMK_Rollup, the Mass Calculated Rollup Fields job will occur immediately when a solution containing a rollup attribute is imported. This assumes that you are installing a solution during a time that won’t adversely impact users.
It also lists the following limitations:
- Rollup attributes can’t be used as a workflow event or wait condition. These attributes don’t raise the event to trigger workflows.
- The ModifiedBy and ModifiedOn attributes for the entity aren’t updated when the rollup attribute is updated.
- A maximum of 100 rollup attributes can be defined within an organization. Each entity can have no more than 10 rollup attributes.
- A rollup attribute formula can’t reference another rollup attribute.
- A rollup attribute formula can’t reference complex calculated attributes. Only calculated attributes that reference simple attributes in the same record can be used with rollups.
- A rollup attribute formula can’t include records in many-to-many (N:N) relationships. It can only include records in one-to-many (1:N) relationships.
- Rollup attribute formulas can’t use one-to-many (1:N) relationships with the ActivityPointer or ActivityParty entity.
About Relationships between Opportunity and Account/Case
Dynamics CRM has always had a composite lookup field on Opportunity called Potential Customer (customerid) that would identify the potential Account or Contact. When business process was introduced to CRM 2013, we gained two new fields: Parent Account and Parent Contact, which can now also be used to identify the Potential Customer.
The Case entity, on the other hand, still uses a composite field for the Customer (customerid), and has an additional relationship Responsible Contact (responsibleconatctid). The composite relationships also can’t be used in calculated fields to lookup attributes from the parent record types, so having separate relationships may actually work easier in practice.
The differences in approaches are confusing and the constraints don’t make an awful lot of sense. Some guidance on this from the product team would be welcome. In the meantime, take care to base your logic on the relationships used by your project!