Rollup Queries & Goal Management in Dynamics 365 Sales (CRM)

How would you weigh a ladybug? Do you have the right instrument at home? I don’t. I would need to purchase or find a more-accurate measuring instrument.

When it comes to Dynamics 365 Goal Management, there are times when the measuring scale or ruler needs to be much more precise than what Goal Metrics and Rollup Fields can provide. Thankfully, we have Rollup Queries to provide that precision. And once Goal Metrics, Rollup Fields, and Rollup Queries are created, we can begin building out our actual Goals and monitor them.

This blog is the last in a series that will show you how to understand goals and goal management in Dynamics 365 Sales. It does not emphasize how to set everything up in your system as that can be viewed here.

Rollup Query

Tapping into the Rollup Query gives the Goal administrator the ability to fine-tune exactly what it means to meet a Goal, whether that is actual, in-progress, or custom.

This would be the marking of the 1/8^th inch lines on the ruler where the Goal Metric and Rollup Fields are marking the ½ inch and inch lines. As mentioned previously, state and status can be used at the Rollup Field level, but this is only a broad limitation.

Let’s say, as part of the organization’s goals for salespeople, that a certain number of phone calls need to be made (Status = Completed; Status Reason = Made) to clients (Accounts) that have signed agreements (Won Opportunities) worth $1,000,000 or more (Actual Revenue). Here is how we could set that up:

The Goal Metric handles the Status, Status Reason, and the date field that will be used by the Goal Time Period.

The Rollup Query handles the Phone Calls made to Accounts that have signed agreements worth the stated amount.

After the Rollup Field (Actual) has been created for the Goal Metric and this Rollup Query has been defined, you can then add this Rollup Query to the Goal.

What is the Difference Between a Rollup Field and a Rollup Query?

There are some differences between the two. First, a Rollup Field is connected to one and only one Goal Metric, whereas a Rollup Query is independent of Goal Metrics. The Rollup Field is limited in precision (State, Status, Date) while the Rollup Query is essentially unlimited in precision (Using Advanced Find like features).

You could create one Goal Metric (Number of Phone Calls) with an Actual and In-Progress Rollup Field with State, Status, and Date defined. You could create multiple Rollup Queries that define precise criteria like related records (Accounts and Opportunities), different status (Opportunities), and other fields (Actual vs Estimated Revenue). A Goal tied to the Goal Metric and the Rollup Queries would use the broader measuring and the more precise measuring to produce your end Goal result.

Rollup Fields and Rollup Queries are meant to work together and are tied together through the Goal.

Goal Metric + Actual Rollup Field -> Goal + Rollup Query (Actual)

Goal Metric + In-Progress Rollup Field -> Goal + Rollup Query (In-Progress)

Goal Metric + Custom -> Goal + Rollup Query (Custom)

What is the Difference Between a Rollup Field of Type Custom and a Rollup Query?

The main difference is the Rollup Field of type Custom does not track against Actuals or In-Progress; it is in its own category. Check out the diagram below:

Opportunity Status	Opportunity Column	Goal
Won	Actual Revenue	Actual
Open	Estimated Revenue	In-Progress
Lost	Estimated Revenue	???

Using the custom Rollup Field and/or Rollup Query is a great way to capture “lost” goals concerning Opportunities. It provides definition to the other side of the picture of sales. To illustrate the outcome of a lost goal, observe these Actuals:

Because we are the goal manager, we know the custom value for Actuals is really a negative value, and it provides us valuable information to possibly change some sales processes of the organization.

How Do You See Rollup Query Results?

Rollup Query results are combined with Goal Metric Rollup Fields and will be displayed on the Actuals tab on the Goal record as seen below:

Key Fields for Rollup Queries

Name

Provide a meaningful name for the Rollup Query so it will be easy to identify when associating to a Goal.

Entity Type

Designates a specific entity that ties the query to the Rollup Query result. This should match up with the corresponding Rollup Field Source Record Type.

Filter Criteria

Filter Criteria is not the name of the field, but this is the designation I am giving to the most critical piece of a Rollup Query. It is here where you define the precision of how you want to measure the goal.

It can be broad (Phone Calls where Regarding equals Account) or specific (Phone Calls where Regarding equals Account AND Account has Open Opportunities where the Estimated Revenue is greater than one million).

Goals

Finally, we come to heart of Goal Management: Goals. Everything before this point has provided the building blocks to support development of Goals.

Key Fields for Goals

Parent Goal

Dynamics 365 Goals allow an organization to establish a hierarchy of goals so that the child goals sum up to the parent goal. A common example is that of a sales team manager that is responsible for their team’s goals. Example in the table below:

Name	Goal Owner	Goal Metric	Parent Goal	Target	Actual	In-Progress
Demi	Demi	# Phone Calls		4500	2275	51
Jin	Jin	# Phone Calls	Demi	1500	750	14
Marc	Marc	# Phone Calls	Demi	1500	723	26
Ricardo	Ricardo	# Phone Calls	Demi	1500	802	11

We can see that the actual and in-progress goals of Jin, Marc, and Ricardo sum up to Demi’s goals.

You could use this same strategy and sum up goals by territory, customer types, etc.

Goal Metric

Each Goal is tied to one and only one Goal Metric.

As far relationships go, Goal Metric is a parent to Goal, and that means that one Goal Metric (parent) may have 0 or more Goals (children). Here is an interesting possibility:

Goal	Goal Metric	Time Period	Goal Criteria/Rollup Query
2021 Phone Calls	Number of Phone Calls	1/1/2021 to 12/31/2021	N/A
2021 Phone Calls to Tier 1 Clients	Number of Phone Calls	1/1/2021 to 12/31/2021	Phone Calls to Accounts WHERE Account Category equals Tier 1
2021 Phone Calls – Front Desk	Number of Phone Calls	1/1/2021 to 12/31/2021	Phone Calls by Users that are assigned to the Front Desk Team.
2021 Phone Calls – Accounts with Active Cases	Number of Phone Calls	1/1/2021 to 12/31/2021	Phone Calls to Accounts that have Cases WHERE Status equals Active

Notice that the same Goal Metric, Number of Phone Calls, is being used for several different purposes.

Remember Goal Metric only determines what is being measured; in this case, the number of phone calls (In-Progress and Actual). It does not have any time element or other filtering restrictions.

It is the Goal that defines the time element (Jan to Dec 2021), and it is the Goal Criteria/Rollup Queries that further defines what is being measured.

Goal Owner

Goal Owner is important in that it dictates what records will be considered for calculation for that User or Team. If the Goal Metric Rollup Field or the Goal Rollup Query identify a table in which the Goal Owner is the Owner or Team Owner of the matching record and all other criteria is matched, those records will qualify for calculation.

Goal Metric Rollup Field

Rollup Fields identify table as Phone Call.

Goal Rollup Query

Goal Rollup Query identifies table as Phone Call.

Goal Owner

Goal Owner is identified as Gary Harrison.

Owner

Actual Phone Call record is owned by Gary Harrison; therefore, it will be calculated as part of the Goals.

Goal Period Type

Either Fiscal Period or Custom Period are the choices. The former lines up with the Fiscal Year Settings configured in Business Management, and the latter can be any date range desired. It could span multiple years (5-year plan) or one day (Big sale event).

Target

Since we are discussing Goals, it is probably a good idea to identify the actual mark of the Goal. The Target field does exactly that, whether it is money ($300,000 in closed Opportunities), decimal (3.5 million pounds of Products sold), or integer (3,500 Phone Calls). When viewing Actuals, you will view the percentage completed in the specified Goal being met.

Roll Up Only From Child Goals

If No is selected, you may add any applicable Rollup Query; otherwise, this Goal will depend on all Child Goals to define the rollup criteria.

Record Set for Rollup

I predict most Goals created will have this field set to Owned by goal owner as management would like to know who is responsible for what. However, if there is a need to develop company-wide goals, regardless of who actually owns the record used for calculating a goal, All should be selected instead.

Rollup Query Lookup Fields

Depending on the Goal Metric Rollup Fields identified (In-Progress, Actual, Custom) associated to the Goal, there will be a matching Rollup Query lookup to allow for a further filtering of data on which records will be used to calculate the goals.

Parent Goals

Utilizing Parent Goals is a great way to visualize a summary of goal objectives met within a given period. Before showing how Parent Goals really work, we will discover how they do not, and this discovery may come as a surprise.

As a Goal Manager, I want to view a summary of goals either by a series of time periods or a range of entities. For example, it is the organization’s desire to view quarterly goals rolled up into an annual goal. Q1, Q2, Q3, and Q4 goals would “rollup” into a 2020 goal; that would be nice. Or the organization would like to see the number of phone calls made, number of emails sent, number of tasks completed all rolled up into an annual goal.

Let’s start with the first scenario. A “child” Goal is created to capture the first quarter of phone calls made.

Goal Metric is set to Number of Phone Calls.

Time Period is set to Fiscal Period, Quarter 1, FY2020.

Q2 through Q4 Goals are also created.

Now, here comes the fun part. A Parent Goal is created with the same Goal Metric.

Because Goal Period Type doesn’t include all quarters, it is changed to Custom Period and then From and To dates are changed to encompass the entire year.

Navigate back to one Number of Phone Calls – Q1 and assign to Number of Phone Calls – 2020 Parent Goal.

Say what? Why are we getting this error message? The error specifically states, in this situation, that the time period of the current goal does not match. Okay, that’s a bummer. Let’s change the Q1 from a Fiscal Period to a Custom Period and then set the From and To dates accordingly.

But, lo and behold, we get the same error. What can we learn from this? We learn that the Parent Goal and all Child Goals MUST HAVE the exact same Goal Metric and Time Period settings. The same error message will display if the Parent Goal Goal Metric is set to something different than the Child Goal Goal Metric.

What can we learn from this? A Parent Goal and all Child Goals only capture goals of the exact same entity type (Phone Calls, Emails, Opportunities, etc.). While it is possible to have one Goal Metric to have an Actual Rollup Field capturing one type of entity and an In-progress Rollup Field capturing another, it would be comparing apples to oranges.

So, we have learned that Goal Metric and Time Period must be the same between Parent Goal and Child Goal. What is the advantage of having Child Goals then? Child Goals are great for capturing achieved goals, spread out across different sales team members or different “projects.”

We will create several Child Goals under the Number of Phone Calls – 2020 Parent Goal.

When orchestrated like this, we will be able to view the individual goals as well as the overall Parent Goal.

“Parent Goals – A parent goal is used to group goals. A manager might (for example) have twenty direct reports. Each of these people would have a goal that they own. But all are grouped under the parent goal to see how the group collectively performs. Meaning the parent role becomes an aggregated version of the child goals.” Source.

Goal Management

All the Goals are created, and now you need to see results against them. The main Goals table list page is available with several views.

Charts are available.

Personal Goals are available via Dashboards.

Actuals

Details are available on the Goal record.

Participating Records

Records that make up the goal numbers are available via Participating Records.

Reports

A report is available from the opened Goal that provides numerical and graphical results.

Exporting Goal Actuals

If desired and not using Power BI, Goals may be exported into Excel and then visualized with PivotTables and PivotCharts.

Key Learnings

Rollup Fields Pave the Way for Rollup Queries

Rollup Fields provide the first layer of measurement and are associated to the Goal Metric. Once a Goal is associated to a Goal Metric (with its Rollup Fields), the respective Rollup Query lookups will then appear on the Goal.

This Goal is associated to the Number of Phone Calls Goal Metric.

The Number of Phone Calls Goal Metric has an In-Progress and Actual Rollup Field.

The Annual Phone Calls Goal displays the respective Rollup Query lookups.

They are not required, but they become available. If you find that you need to further define a Goal with a Rollup Query but do not see the right lookup, add a respective Rollup Field to the Goal Metric.

Rollup Query Cannot be Assigned to a Goal That Doesn’t Have a Rollup Field

On the flipside of Rollup Fields and Rollup Queries, if there are no Rollup Fields associated to the Goal Metric of which Goal is associated, there will be no Rollup Query lookups available.

Goal Metric that has no Rollup Fields:

Goal that has no Rollup Query fields available:

Rollup Query Fine-Tunes the Goal Metric Measurement

If you remember, the Goal Metric Rollup Field identifies the table, Status, Status Reason, and the date field; it is minimal in scope. The Rollup Query, on the other hand, can be quite intricate in development, can include other tables with 1:N, N:1, or N:N relationships, and can even include rollup and calculated fields. The Rollup Fields are the 1-inch marks on the ruler, and the Rollup Queries are the 1/16^th, 1/8^th, and 1/4^th marks.

For example, the Goal Metric determines that In-Progress for Phone Calls is where Phone Call Status equals Open, and it revolves around the Created On column. The Rollup Query assigned to the Goal that uses this Goal Metric determines that only Phone Calls that have a Priority of High should qualify for calculation.

Rollup Query is Additive

As seen with the SQL above, Rollup Queries are “in addition to” any Goal Metric Rollup Field.

Goal May Only be Associated to Only One Goal Metric

You can have only one Goal Metric per Goal, meaning that the Goal will only measure one entity’s worth of data, defined by the Goal Metric’s Rollup Fields.

Different Goals May be Associated to the Same Goal Metric

You can have many Goals for the same Goal Metric, meaning you can use the same Goal Metric across various Goal needs (different users, different fiscal periods, different targets).

Two Goals for the same Goal Metric:

Goal With no Rollup Queries and Uses a Goal Metric That Has no Rollup Fields Will Not Provide Results

Goal Metric has no Rollup Fields:

Goal has no Rollup Queries:

No results:

Parent and Child Goals Time Period Must Match

If a Goal is assigned a Parent Goal value, the Time Period will automatically match that of the Parent Goal, and the fields become locked.

Gotchas

Goal management can be simple or complex, and there are certain undesired possibilities that may occur if all the goal-management items are not established properly. Below are some areas to consider:

Rollup Fields Could Contradict Themselves

When developing a Goal Metric and Rollup Fields, there is an assumption that the same item should be measured across In-Progress, Actual, and Custom. In reality, you could define three different tables, one for each Rollup Field and apply them to the same Goal Metric, but that wouldn’t make any sense, right? I did Phone for Actual, and I did Task for In-progress, and they worked together.

Changing Record Ownership Changes Goals

Observe that the Goal is tied to the Number of Phone Calls Goal Metric which is tied to the Phone Call table.

But the same Goal is associated to the Number of Contacts – Actual Rollup Query which is associated to the Contact table.

As expected, unpredictable or no results will occur.

Changing Fiscal Year Settings Will Impact Goals

If, for whatever reason, the system Fiscal Year Settings are modified after Goals have been developed and implemented, the Actuals will adjust accordingly. For example, if the Fiscal Period Template was changed from Quarterly to Semiannually, the Goal Time Period Fiscal Period options would change.

This is the end of our blog series on Goal Management. We have covered Goal Metrics and their Rollup Fields, Rollup Queries, Goal Management and other related topics. Hopefully, more clarity has been provided to you to understand how they all work together so you can develop the best goal-management system for your organization. If you have any questions about Dynamics 365 for Sales, please contact us.