Update 4/6/2017: Post updated and applies to all versions of Dynamics CRM from CRM 2011 to Dynamics 365.
One of my pet peeves about sorting data in charts is, that the proper order for the viewer, may not always be alphabetic or numbers ascending or descending. A prime example of this is Territories. I often see three territories used. West, Central and East. When viewing them on a chart, they often appear like this.
At first glance that seems ok, but a part of visualizing data is making it easy to decode for the viewer. Especially in demo situations. If looking at the names of a few geographically dispersed territories like this, you would assume the data is being presented in the same order as if you were looking at them on a map. Unfortunately, there’s no way to alphabetically sort Central, East and West so they appear in the preferred order of West, Central, East.
Now, we could just add an introducing letter or number to each name so they would sort like we wanted, but having “A. West”, “B. Central” and “C. East” just doesn’t look good and we don’t want to add irrelevant information to our charts. Besides, that would just be way too easy.
There are two methods to achieve this so you can pick the one that fits your needs the best.
1. Custom Sort Field on the linked entity
Place a Sort Order field on Territory entity of the type Whole Number. Then specify the desired sort number for each record.
Export the standard Sales Territory chart and add the new field in the fetch collection.
I then get a chart that looks like this.
The Territories now appear in the proper order, but the chart does look a little funky because of the extra series, so let’s see if that can be fixed.
Now what I want to do is
- Remove the Legend (1,2,3) as we do not need to display the sort order
- Change the chart type to StackedColumn
- Remove the funky coloring
After import, my chart should look like this.
That may just work in case I want to have different colors to my territories. The different colors show up because the chart is coloring according to the Sort Order field, so they are actually representing 3 different series.
However, if I have read this blog post, I can easily add some custom colors to my chart, and I’ll just ensure that the first three colors are the same.
So in the end, the chart should look like this, with a non-alphabetical, but logical, order on the territories.
2. Utilize the Option Set value sorting
This method utilizes the fact that if an Order is not specified in the XML and an Option Set is used, then the Chart orders your data based on the Value of the Option Set and not the Label.
Update: Dynamics 365 now sorts according to the label.
In the Territory example, I’ve added a new option set to replace the name field. I’ve added all the Territory names and I can now set the name for each Territory record through the Option Set.
All I need to make sure is that the Values are in the order that I desire.
After exporting my original chart, I modify the fetchcollection attribute pointing the Territory name, to point to the new option set instead. In this case, the new schema name is “new_name”.
That’s all and after importing my chart now sort according to the label values and not their names.
Again, now a much more pleasant order of Territories.
This method is definitely easier and quicker to implement, but it also assumes that you are working with reasonably few names and that you can implement the Option Set and ensure its values are updated properly. Do note that CRM 2013 and previous versions, there was a bug on proper sorting according to option sets.
Hope you found the tip useful. As always please sign up for my newsletter and follow me on Twitter. Follow @CRMChartGuy
crm chart guy 
[…] Palettes Order data based on a linked entity […]
Nice, post(s), super helpful.
You said: “All I need to make sure is that the Values are in the order that I desire.”
This is definitely the ideal way to define the sort. (It is so frustrating that the charts don’t default to the order the Option Sets are configured to display or even have a basic sort option available in the gui).
But editing Values is only for pre-production situation, correct? These values cannot be changed without losing the data references already in existing records, I assume.
If this is true, it is strange the system would allow the Value to be edited.
You are right – those values should ideally be changed in pre-production. Or at least VERY carefully to ensure there’s no loss of data. You could create a new set of values, move the data to those, and finally remove the old option set values. Still not recommended in a live environment though.
Your blog is really helpful. Thank you very much. I’d like to ask for your advice.
For example, in the ‘Case’ entity, the value of the field ‘Status reason’ is not user-changeable. It’s automatically defined when you add a new status reason name. After setting up the CRM, we added more ‘Status reason’ in the pick list. Now, we’d like to sort it with a proper in the chart. Unfortunately, it doesn’t seem to be possible to do that with the value. Do you have any idea on this? For example, first one should be ‘value 1,000,100’, second one should be ‘value 4’, third one is should be ‘value 2’ in the chart. .
Hi!
I read:
“Update: Dynamics 365 now sorts according to the label.”
But that doesn’t seem to be the case with me. Anything I could add to my XML so that it orders by the label?
Following your suggestion here:
“Place a Sort Order field on Territory entity of the type Whole Number”
I dit exactly this. My chart already has 2 groupby clauses:
1. Per territory
2. Per year
So when I try to add a new one based on “new_sortorder”, I get the following error:
“A measure collection cannot use more than two Group By clauses”
Is there a workaround to this?