Count Distinct or Unique items in MS CRM Charts

Ever had the need to count the unique number of items in MS CRM 2011, or MS CRM 2013 for that matter.

For example, not only show how many Activities were created in a given month, but also how many different people owned those Activities.

The chart editor in CRM 2013, which is remarkably similar to the one in CRM 2011, allow the following options for aggregates.

• Average (avg)
• Count:All (count)
• Count:Non-empty (countcolumn)
• Minimum (min)
• Maximum (max)
• Sum (sum)

The option to only count unique items is added to the chart xml and it only works with the countcolumn aggregate.

• distinct=”true”

Cases and Customers

To show how to use it, I’ll use the case entity and create a chart with cases created per month, but also show how many different customers those cases belong to.

As usual I’ll try to use the chart designer to do most of the work for me.

For the Customer field, I’ve selected Count:Non-empty, which is the only aggregate that works with distinct=”true”.
Notice that both columns have the same height.

Export the chart xml and open it up in an editor.

Find the attribute for the customers, which is also the only one that have the countcolumn aggregate. On this line insert distinct=”true”.

Before importing the chart back into MS CRM, check the series and delete references to a secondary Y axis if there are any.

Delete YAxisType=”Secondary”

Now we are ready to import the chart and here’s the result.

Cases for the month and how many unique customers those cases belong to.

Notes:

When using distinct=”true” make sure you are using it on a field which has an ID. For example, opportunityid, accountid, contactid, systemuserid, etc. If you use distinct=”true” on a name or topic field, then items with the same name count as one. So two contacts named John Smith would only be counted as one.

Hope you liked the post. Please sign up for either the email newsletter or follow me on Twitter  Follow @crmchartguy