I just love the new Rollup fields in CRM 2015, but when I demo the software, I do not like the delay between changing the underlying values in the child records and the automatic asynchronous update scheduled to run some time later. Ditto for routing Cases.
Arrives a tweet from Mark Smith (@nzCRMGuy) in my Inbox this morning, with a link to demian_rasko’s msdyncrmworkflowtools project on codeplex:
This solution adds to custom workflow activities, one to force Rollup field calculation to run for the a parent entity whenever a specific field on the child record changes, and another to force the Routing Rule for a Case to execute immediately.
This becomes a standard part of my demo toolbox, I suspect 🙂
If you work in presales, like I do, you may want to showcase the self-service BI capabilities of Dynamics CRM, or use PowerBI for even richer visual exploration of data. The problem is: quite often the sample data in our demo environments do not provide enough volume or richness to allow for great visualisation and analysis. So, let’s invoke that trusted companion Microsoft Excel to generate some rich data – in this series of posts, we will eventually generate a large volume of Cases for existing Contacts, with a good distribution of Dates, Case Types, Case Origins, Priorities, Subjects and Current Statuses. To do this, we start by investigating the technique for generating a random selection from a list in Excel – let’s capture a list of values for Priority, and to make it easy to refer to this List, we turn it into a Named Range by selecting the entire column and then adding the range name on the top left: We can now use Excel functions against this list, for example this formula will yield the count of the number of cells in the Named Range “Priority” that are not empty. Use =COUNTA(Priority) as shown: A useful helper function returns the value at a specific position in the range, in this case the value at the 2nd position, i.e. “High”. Use =INDEX(Priority,2) as shown: The last helper function we will need generates a random (integer) number between two values, in this example it would randomly generate one of 2,3 or 4. Use =RANDBETWEEN(2,4) as shown: Every time the spreadsheet is recalculated, a new random value will be generated. We are now in position to select a random value from this list, by combining formulas. Use =INDEX(Priority,RANDBETWEEN(2,COUNTA(Priority))) as shown: This is the enabling formula that allows us to generate meaningful data. Consider the following worksheet: The Contact column is generated from the data in column A of this worksheet, exported from CRM. Use =INDEX(Contact,RANDBETWEEN(2,COUNTA(Contact))) as shown: Similar formulas on the various columns now provide the start to a random set of Case data, drawn from the following lists: In order for the data set to be more realistic, we can add a better distribution to some of these lists, for example: The same formulas now yield a better distribution: The Case Number and Title fields shown above were populated with these formulas: =”CAS”&”-“&RANDBETWEEN(1000,9999)&”-“&RANDBETWEEN(100,999) =E2&” logged by “&C2. In the next chapters we will look at expanding this with additional useful columns and a few other techniques, but this here is really the basis for generating good quality sample data.
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!
I normally like to include City and Country fields on the Views for Opportunities and Cases, to enable an easy export to Excel for PowerBI mapping. In CRM 2013, we could add the City and Country of the related Account to the view of Opportunities by using the ability to add fields from parent entities to a view. The problem was that this lead to wide clumsy column headings like “Address 1: City (Account)”:
Apart from the cosmetic problem here, the potential Customer could also be a Contact – so we may need to add the same two fields for Contact. This would lead to two pairs of City and Country fields, which is not easy to consume in PowerBI.
Using the new Calculated Field capability in Dynamics CRM 2015, you can sidestep these problems elegantly with the following steps:
- Create a new City field on Opportunity and specify the Field Type as ‘Calculated’:
- Click Edit to add the following Condition and Action for Opportunities related to an Account:
This effectively specifies that the calculation retrieves the ‘address1_city’ field from the parent Account record.
- Add a second Condition and Action for Opportunities related to the parent Contact record:
- Do the equivalent for the Country field: create a new Country record on Opportunity, specify the field type as ‘calculated’, then edit the calculation to retrieve the ‘address1_country’ field from both the parent Account and Contact fields.
- Edit Views to include the City and Country fields. Our subsequent view looks much better:
- Click on ‘Export to Excel’ and specify ‘Static Worksheet with Records from all pages in the current View’.
- In Excel, save the generated Excel file as an Excel Workbook to enable PowerMaps.
- On the Insert menu, click Map. Follow the Wizard to define your map, first identifying City and Country as Geographical indicators, and then selecting to map at the City level:
- Drag and drop from the field list to set the Height to the Sum of Est. Revenue, the Category to Probability and the Time dimension to Est. Close Date:
This yields a great animated visualisation – I often end my demos of Dynamics CRM on a high note with this.
Got to love Bob’s take on life – CRM will be dead soon:
Well worth a watch / listen.
Posted in General
Well, I guess yesterday’s release of CRM 2015 (see Bob Stutz’ blog post) was just the energy boost I needed to pick up blogging again. CRM 2015 makes great strides in marketing, sales and customer service – and from a platform perspective the following key features will greatly improve the user and developer experience:
- User-definable Hierarchies with graphical visualisation
- Calculated Fields (Simple formulas, lookups and roll-up calculations)
- Business Process Flows with conditional branching
- And, not just for fun, voice integration with Cortana on Windows Phone
As of 1 December 2014, Microsoft Dynamics CRM Online is available in 130 markets and 44 languages. Using #CRM2015 with Office 365 integration, and the ever-so sexy PowerBI offers the best CRM Experience yet.
More details and explorations to follow, stay with me…
Posted in General
Tagged CRM 2015