Order data based on a linked entity

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

In the Territory example I’ve added a new option set to replace the name field. I’ve added all the Territory names and 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 sorts accoring 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.


, , , , ,

  1. #1 by jimlatimer668 on November 2, 2013 - 3:30 pm

    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.

    • #2 by CRM Chart Guy on November 11, 2013 - 1:26 pm

      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.

  2. #3 by JD Shin on February 22, 2016 - 3:17 am

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

  1. Order (or Sort) your charts | crm chart guy

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: