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.
