Data is more abundant than ever today, but what good is all that data if you have no means of sorting through the influx? One of the goals of xRM (www.xrm.com) when helping clients deploy Microsoft Dynamics CRM is to ensure that they can easily understand their data by displaying it in ways that make sense.
Business Intelligence (BI) applications are purposed to help people make sense of data. A number of BI applications are available. Microsoft now has its own offering—Microsoft Power BI—which integrates directly with Microsoft Dynamics CRM. One of the benefits of Power BI is the ability to easily create visuals with data from multiple tables. For a brief background on this concept, watch the video, Creating Relationships in Power BI Designer.
However, did you know that we can do something similar to that in Microsoft Dynamics CRM without a BI application? It’s not even necessary to export to Microsoft Excel. We can accomplish it by combining data from related entities on a single view.
To illustrate this, let’s find the total estimated revenue of all open Opportunities, organized by City. The initial obstacle is that the City field is not found by default on the Opportunity entity in Microsoft Dynamics CRM. The City field resides on the related customer entity, Account in this example. The estimated revenue is found on the Opportunity. So the data we need exists in two different entities, Accounts and Opportunities.
Let’s open Advanced Find. The button can be found next to the global search feature in the navigation bar of Microsoft Dynamics CRM 2015.
Advanced Find requires us to choose an entity to search for. Should we start at the Account or Opportunity entity?
To answer that question, we need to think about how the entities relate to one another. Each Opportunity record is a child of a parent customer record, an Account in this example. Opportunities can be related to only one Account (N:1) whereas Accounts can have multiple Opportunities related to them (1:N). Advanced Find will only let us grab data from related entities when it is possible to resolve a single record, which can only be done from the perspective of a child record. A child record can only have one parent. Therefore, we need to begin building this view from the Opportunity entity.
It should be noted that starting from the child record (Opportunity in this example) is not an absolute certainty. A rollup field could be designed and placed on the Account record that calculates all estimated revenue. For the purposes of this example however, we’re assuming this is an unmodified Account entity.
Let’s design the view now in Advanced Find, starting from the Opportunity entity. We can begin from something like the default Open Opportunities view. From here, we need to click the Edit Columns button in the ribbon, thus opening the Edit Columns dialog.
We can then click the Add Columns button which opens the aptly named Add Columns window. Notice the Record Type drop-down (pictured below). By default, it’s set to the starting entity, Opportunity in this example. This means we can include attributes of the Opportunity entity as column headings, Est. Revenue being an example. However, we can also change the record type to a related entity and add attributes from related entities as column headings. This simple, yet often overlooked, feature is how we bring data from multiple entities into a single view.
When we change the Record Type to “Potential Customer (Account)” (see below), the available attributes change to those of the Account entity. We can now check the Address 1: City checkbox to add it to the view. Note that if we were to export the view to a tool such as Excel to plot locations on a map, it might be a good idea to include Address 1: State/Province. That way, Excel would know if we’re referring to Springfield, MA or Springfield, IL.
We can then move the columns as we see fit using the arrow icons back at the Edit Columns window. When we’re done, we can click the OK button to close the window and return to Advanced Find. Typically, it’s a good idea to click the Results button to make sure the view is to your liking. If it is, we can click the Advanced Find tab in the ribbon, click Save As, and name the view.
We now have a view that contains data from multiple entities. We can design a chart based on that view that shows us Est. Revenue by City, something we couldn’t do before we designed this view.
If we wanted to export the data to Excel to take advantage of say the Power Map feature, we can do so in a single export.
For many clients we at xRM work with, the primary goal of the Microsoft Dynamics CRM deployment is to improve their decision making. Knowing how to extract and display useful data from the database is crucial in achieving this. There are add-ons and third-party applications that can help in this regard. Microsoft Power BI is an example of such an external tool. However, it’s important to have a grasp on the features available to us directly in Microsoft Dynamics CRM. Something as simple as the Advanced Find tool can be extremely powerful in surfacing data vital in making key decisions.
If you would like to learn more tips, tricks, and tutorials such as this one, please check out our xRM blog and our Success Portal. If you would like to receive training from our team of experts, please inquire about our QuickStart Training.