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
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
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.
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.
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