Using CRM 2015 Calculated Fields for Opportunity Estimated Revenue

It’s time for part 2 in the Smarter Sales Process article trilogy. As described in my previous blog post about lead qualification process customization, this content is taken from my MSDynamicsWorld.com webcast titled “A Non-Developer’s Guide to Smarter Sales Processes in Microsoft Dynamics CRM 2015“. After having adjusted the lead to opportunity process to better suit our needs, we’ll next have a look at how the data managed in the opportunity stage could be more easily entered and maintained by leveraging the no-code customization tools available in Dynamics CRM.

Introduction to Calculated Fields

As you should have noticed by now, the CRM 2015 release added the possibility for defining two new “complex” field types in addition to the traditional “simple” fields. Rollup fields is something I’ve covered in more detail in an earlier blog post, so this time we’ll be working with the other type, which is the calculated fields. Essentially these are the types of fields where you don’t directly insert a value in CRM, but rather the field value is calculated from one or more other fields in the system, based on the formula and conditions you specify in the calculated field definition editor found from the field’s properties in the CRM customization menus.

How calculated fields differ from rollup fields is that with them we’re always working on the current record, whereas rollup fields retrieve data from related records. Well, actually that’s not entirely true, since a calculated field can also reference a value from a related parental record in its formula. A more accurate description could therefore be that calculated fields can access data from the “1” side of the 1:N one-to-many relationship, whereas rollup fields are the tool for retrieving data from the “N” side of the logical data model in our CRM organization.

Another aspect that the system customizer must be aware of before starting to leverage these new tools for building CRM 2015 solutions is how and when the field values are calculated. As demonstrated in my post “CRM 2015 Rollup Fields: The Gotchas“, the data shown in rollup fields may be up to 30 minutes old, since these are updated based on an asynchronous job (unless you apply the workaround described in that blog post). Once they are updated, though, the values are persisted in the database. Calculated fields work in the exact opposite way, meaning they are calculated in real-time, but the data is not actually stored in the CRM database. While the latter part of that sentence might sound strange at first, it simply means that the CRM platform performs the calculation any time the specific calculated field is needed. This includes opening a form that contains the field, browsing a view with such columns, accessing a dashboard with charts referencing calculated fields, making SDK calls for retrieving this data and so on.

To familiarize yourself with the details of these features, have a look at the TechNet article Define Calculated Fields. Or if you’re in a hurry, spend 5 minutes watching this YouTube video from the CRM product team, to see how what the new fields look like in the customization UI.

Applying Calculated Fields on Revenue Estimation

The scenario that I chose for the Smarter Sales Process series deals with the way we determine an estimated value for an opportunity record in Dynamics CRM. By default, you have the option of either entering a lump sum into the Est. Revenue field of an opportunity record, or creating individual opportunity product records as line items that have a specific revenue value. This is what the bit field IsRevenueSystemCalculated is all about, with its options of “User Provided” or “System Calculated”. Since many organizations using Dynamics CRM don’t actually bother maintaining the detailed product catalog and price details in corresponding CRM records (at least not without a custom integration being built to sync the data from other systems), the “User Provided” option is quite often used for recording just the total estimated revenue value for the opportunity. What this means in practice is that the sales people end up using their own Excels to calculate the various components from which the revenue is generated and just entering the end result into CRM. If any parameter in the equation changes, it’s back to updating your Excels, then CRM again. Oh joy.

Could there be some middle ground between taking just a single figure from an external Excel sheet and having a full blown product & price catalog maintenance process for CRM? If the products and services you’re selling consist of a limited set of key revenue components that are typically included in each quotation you make, then exploring the possibilities of creating custom fields for these components directly onto the opportunity record might well be in order. While these will not provide the high granularity data of having opportunity product line items with links to the related product record, the data entry and maintenance experience is probably going to be a lot easier for you to sell to the CRM end users. (After all, we’re talking about sales people here, who are the toughest crowd you’ll ever need to please with your CRM system functionality.)

In this example scenario we’re selling CRM consulting projects that have three common revenue categories: consulting revenue, license revenue and “other” revenue. The total value of each area is calculated via a specific formula, after which each area specific revenue is summed up into the Total Amount field. As you’ve probably guessed by now, this is achieved by using the CRM 2015 calculated fields feature. Compared to adding the line items one by one, configuring unit prices and other variables, the data entry process is considerably faster, since all the user needs to do is tab through the relevant fields on a single form and enter values where necessary. You can catch a quick glimpse of the live opportunity form in this YouTube recording of the webcast.

For a detailed explanation of how this type of functionality can be configured in Dynamics CRM, have a look at the following slides: Smarter Sales Process in Dynamics CRM 2015 – Part 2: Revenue Estimation.

A Few “Gotchas” on Calculated Fields

If you’ve implemented a similar custom opportunity form in the past by using Javascript to perform the calculations, then you should be aware that the native calculated fields in Dynamics CRM don’t offer exactly the same user experience as custom scripts do. The reason is that the calculation logic is executed whenever the fields referenced in the formula are retrieved from the CRM, meaning not before you’ve submitted the updated source field values into the database. So, the moment you change a field value (let’s say “consulting hours” in our example”) and click/tab to the next field, a client-side script would be able to recalculate the fields instantly, triggered by the onChange event of the field. A calculated field will just sit there waiting for the source data to be saved to CRM first, either via the auto-save feature every 30 seconds or the user explicitly clicking on the save icon in the bottom right corner of the form.

Considering that with CRM 2013 we received the Business Rules feature that acts in practice the same way as a custom script (meaning it’s executed on the client side), this may seem like a slight setback in the level of application UI responsiveness for Dynamics CRM. Knowing that there are also some calculation capabilities available with Business Rules, you might be wondering if this feature could be used instead of calculated fields, to deliver an even better user experience. Well, based on my personal experience, if you try to build a very complex chain of calculations by using Business Rules, you’ll soon find yourself in a world of pain trying to figure out why the events don’t always trigger the way you want them to, how to handle resetting field values and so on. Although I haven’t yet used the calculated fields feature in as many real life scenarios as Business Rules, at least I haven’t run into similar problems in ensuring validity of the calculations performed with them, so my recommendation would be to always opt for calculated fields whenever you need to… calculate the value of a field (ever get the feeling that us consultants just state the obvious things?).

Another thing to be aware of when it comes to calculated fields and Business Rules is that the two don’t mix. More specifically, a Business Rule cannot reference a calculated field, so you cannot grab the results of the calculation and use them in a subsequent business logic implemented via a Business Rule. Luckily, you can still access the calculated field values in a workflow process, which is the workaround that I’ve used in my aforementioned presentation.

During the webcast, there was also a very good questions presented on whether there are some limitations on how many calculated fields you can use on a single entity. Based on the official documentation, there doesn’t seem to be any hard limit on the number of calculated fields you can create, but you cannot include more than 10 calculated fields into a single view (or chart), which could potentially be reached if creating a highly complex calculation scenario and a summary view to export the resulting data out of CRM.

As always, the responsibility on system performance impact ultimately lies on the system customizer, even if the solution is created just via clicking the CRM configuration options instead of writing custom code. As the no-code customization tools get more advanced with every release of Dynamics CRM, it’s becoming increasingly important also for people in the business analyst role to have a basic understanding of how the underlying application platform operates. I want to highlight a couple of recent whitepapers that Microsoft has released around the solution design and performance topic, which should give you plenty of food for thought, whether you’re approaching Dynamics CRM from a developer point of view or if you’re an analyst that’s aspiring to design more complex CRM solutions:

• Microsoft Dynamics CRM Online patterns & principles for solution builders
• Microsoft Dynamics CRM 2015 Performance and Scalability Documentation