In-form Analytics in Dynamics CRM 2015

Due to the updates included in Microsoft Dynamics CRM 2015 it is even easier to provide contextual data insights from within records, such as accounts and contacts based on their related records. Through some simple, yet effective configuration you can provide users with an up-to-date tailored overview of your organisations’ interactions with clients or potential customers .

In this example I will configure the Account form in CRM to display Sales Pipeline and Order data using the following functionality:

  • Roll-up Fields
  • Sub-grids
  • Charts

Prior to customising your entity form you will need to identify what content you are looking to deliver within the form and makes sure the entity relationships are in place.

Please Note: The amount of calculated data which is rendered on load for your CRM records can affect performance in the application, you will want to test the load time for your customisations to be sure it won’t impact on the overall user experience.

Step 1 – Create Roll-up fields in CRM

Roll-up fields in CRM are a new feature introduced in 2015, they enable administrators to add calculated data fields to forms without the need for development. We can use this functionality to obtain data from Opportunities associated with an Account.

Firstly, navigate to Settings > Solutions in CRM and select your solution, navigate to the Account entity and open the Account form you are going to be working with.

Once in the account form select the Insert tab at the top of the page and click to add a new Tab. Give your new tab a name, (in this case I have called mine “Sales Data” and formatted as two columns) and click OK. There are some additional fields that have been added to CRM 2015 out of the box; “Open Revenue” and “Open Deals” which you will find in  the field explorer. For this example I am going to add both to my newly created tab in a section.


You can open the field properties to view the roll-up calculation applied to each of these fields. In the field details you can see that the field type is set to “Rollup”, by clicking edit you will see the current aggregation and filters in use, those for the “Open Revenue” field are in the image below. The “Open Deals” field is a rollup that uses a count of open opportunities.


Further to these fields I am going to add one for the “Closed Revenue Sum” and “Lost Revenue Sum” using the following steps; New Field > Currency Field > Field Type = “Rollup” > Edit.

For the Sum of Closed Revenue or Closed Revenue (Sum) field I have used the following rollup query;

Source: Account
Use Hierarchy: Yes
Relationship: account_parent_account
Related Entity: Opportunities (Potential Customer)
Filters: If Status Reason equals “Won”
Aggregation: SUM of Actual Revenue

For the Lost Revenue (Sum) field I have used the following rollup query;

Source: Account
Use Hierarchy: Yes
Relationship: account_parent_account
Related Entity: Opportunities (Potential Customer)
Filters: If Status Reason equals “Cancelled, Out-Sold”
Aggregation: SUM of Est.Revenue

Place your new fields on the form in the desired order and save your changes.

Step 2 – Add Charts to the Form

Next I am going to use the sub-grid functionality to add charts to the account form. This is a useful trick that can deliver some great results with very little fuss. The key is to make sure you are using the right view for the Chart for the sub-grid.

Insert a new section in the tab created in step one and then click to insert a new “Sub-Grid”, in the Set Properties window select “Only Related Records”, the entity to “Opportunities (Potential Customer)” and the default view to as required, for this example I am going to use “Open Opportunities”. Then scroll to “Chart Options” at the bottom of the window and select the “Default Chart”, (I have used the “Sales Pipeline” chart), then select “Show Chart Only”. Finally click “Set”.


Repeat the steps for the charts that you require, I created a custom view called “All Opportunities” and used this as a filter criteria for the “Deals Won vs. Deals Lost” chart. Adjust your chart formatting until you are happy with the layout and click save.

Step 3 – Add Sub-Grids

For this example I have moved the “Recent Opportunities” sub-grid to my newly created tab in its own section. If you are surfacing related data be sure to check the box for “Only Related Records”, you can also create specific views to use for your sub-grids.

Step 4 – Complete Your Configuration & Test

Finally, choose the appropriate formatting and layout to maximise the number of components added to your tab and set if the tab should be expanded by default or not, (collapsing the tab by default means that the data will only load when it is expanded rather than each time the form loads). Once you are happy hit “Save” and “Publish” your form.


And there you go – easy, no fuss analytics that are filtered to the current record. The results for my example are displayed above, the key to this configuration is data – the more you have the better the results. You can also use JavaScript to hide or show the tab depending on parameters, such as customer type etc.

This is just a quick example of what can be done and hopefully has given you food for thought. Happy CRM’ing folks.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s