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:sqlok

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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: