Errors in Goal Recalculation

Platform: Dynamics CRM 2013 On-Premise SP1

Entity: SalesOrderDetail

Goal Metric: Extended Amount (sum)

Rollup Query: Product Group

Time Range: Custom

Symptom: Goal Progress totals are inaccurate (when two or more records have the same extended amount, product group and creation date.)

I noticed that after recalculating the above mentioned goal, the resulting progress total amount was inaccurate.  Started a SQL profile, and found out that the supporting SQL statement, yields less results than a direct query.

A direct query like the following one yields 82 rows:

However, the SQL generated by the goal recalculation yields less records:

If we look at the first few rows in both result sets, we realize that since the goal recalculation uses a UNION statement, it excludes records with the same information (thinking that they are duplicates). For example, the first sales order has 2 lines with basically the same data (amount 46.51), but in the second set, we appreciate that only one is accounted for.

The problem happens when you have 2 or more salesorderdetail records with the same “extended amount” and the same product group. Since your SQL script uses a UNION statement, it thinks that those records are duplicates. The goal recalculation SQL statement fetches the following columns:

ExtendedAmount, new_productgroupid, ownerid, createdon, ownerbusinessunit, salesorderid

I am not sure why doesn’t it fetch salesorderdetailid (the primary key), which would resolve the problem in the recalculation.

The MSFT Dynamics CRM product team needs to look into this.