Microsoft Dynamics® CRM Training
Calculating Aggregates 01
An error occurred while loading the video. Please try again.
This is the first video of a two-part lesson. In Microsoft Dynamics CRM, Goals are traditionally used to track progress towards a designated target. However, if you get a little creative, they can be used to calculate aggregates. For example, if you want to know how much all-time revenue an Account has generated for you, or how many Case records you’ve resolved in a region.
In part one of this tutorial, we create a Goal that simply measures an aggregate. In part two, we relate the Goal to a parent record so that it can be used to run a calculation and populate a field in the parent record. That way, we don’t have to open a separate record in another entity to see the aggregate number.
The scenario for this lesson is that I want to be able to calculate the all-time sales generated by a specific Account called “Adventure Works”. A solution for this would be to create a Goal with no time-restraint, and no target. All this Goal will do is calculate actual revenue, not in-progress revenue.
I’ll start by navigating to the Goal entity in the Sales module and clicking New. Naming this Goal carefully is important. I know that a single Goal will be designated for a single Account, in this scenario at least. I could potentially have a separate Goal for each Account, therefore, the Name needs to be specific. I’ll name it, “Adventure Works All-Time Sales”. This allows me to create more Goals of this type and categorize them into a single View that filters Goals with the words “All-Time Sales” in the name. I’ll then designate myself as the Goal Owner.
The next thing I need to do is select a Goal Metric. I am not going to use the default metric “Revenue” because is includes in-progress money. I just want actual money. I’ll create a new metric by clicking the search icon in the Goal Metric field and then click New in the Look Up Records dialog.
In the Goal Metric form, I’ll name it “Actual Money”, select “Amount” in the Metric Type field, and then select “Money” in the Amount Data Type field. I now need to define the rollup field, but I need to save the record first. When it refreshes, I’ll click in the rollup fields box and then click Add New Rollup Field in the ribbon.
The Rollup Field form opens. This is where I define which field will be used to calculate the metric. I’ll select Actual (Money) in the Rollup Field dropdown. Now I have to specify the details of this rollup. I know that revenue is calculated through Opportunity records, and I only want actual revenue to be measured. This means I want to take the number recorded in the Actual Revenue field of any Opportunity that is in a state of “Won”. Set the following fields as follows: Source Record Type = Opportunity; Source Field = Actual Revenue; Source Record Type = Won; Record Type = Opportunity; Date Field = Actual Close Date.
Now that I’ve configured the Rollup Field settings, I’ll click Save & Close in the ribbon to return to the Goal Metric form, where I’ll once again click Save & Close in the ribbon. I’m back at the Goal record. The next sections of the form are Time Period and Targets. Since I want this Goal to simply be a measurement of all-time sales for a single Account, I don’t want to set a time period. It is however required, so I’ll select Custom Period and set the From and To fields to arbitrary dates that I won’t have to worry about reaching, like between the years 1990 and 2500. I’ll then leave the Target set to zero because there is no target.
Now I have to set the Goal Criteria, which is down the form. Since I want all Opportunity records related to the specified Account to contribute to the sales numbers, I’ll select “All” in the Record Set for Rollup field. No matter who the owner of the Opportunity is, as long as it is related to the designated Account, the revenue will contribute to the sales number.
The last thing I want to do is set the Rollup Query, which will dictate the potential customer for whom we are calculating the aggregate. I’ll click the lookup field. When the Look Up Record dialog opens, I’ll click the New button. When the Rollup Query form opens, I’ll name it “Adventure Works Sales” because this query will be specific to this Account. In the Entity Type, I’ll select “Opportunity”.
Now, I have to set the query for this rollup. Since I want Opportunities related to the Adventure Works Account, I’ll set the query to “Potential Customer, Equals, Adventure Works”. The query is set; I don’t have to add any additional criteria, like what the status should be, because it’s already defined in the Goal Metric. Now, I can click Save & Close.
I have now configured a boiled-down Goal that has no target and an arbitrary time period. I can click Save in the ribbon, and then Recalculate to see how much money this Account has generated for my company in the Actual (Money) field.
Remember, a Goal that calculates aggregates is not restricted to revenue. Just like any other Goal, you can define your own metrics and rollup queries. As long as there is no target and you set an arbitrary time period, you’ll be able to track totals like in this example.
In the part two of this lesson, I demonstrate how to place this aggregate number in the parent record using a Workflow. That way, I don’t have to navigate away from the Account record to see its all-time sales, I can just read it on the record.
For more tips, tricks, and tutorials, please refer to the Success Portal and blog.xrm.com.