Archive for category Order charts
I usually stick to the standard chart types in my blog posts, even though Dynamics CRM supports many other types such as radar, polar, error bar and bubble charts. So it is time to venture out a little bit.
In this post I will work with the bubble chart and show how to build one using only Dynamics CRM charts and some chart xml editing.
Here is the bubble chart I will build.
A couple of details on the setup. The X axis along the bottom has the Est. Close Dates, with the nearest close date on the left. The Y axis goes from 0 – 100 based on the Probability of the opportunity. In an ideal scenario, each opportunity, or bubble, will move from the lower right quadrant, to the upper left quadrant of the chart. As the Est. Close Date comes nearer, the Probability should also increase. This also makes it easy to identify opportunities that fall outside the intended path, such as having a very low probability, but set to close very soon, or a very high probability on an opportunity that is a year out.
Before you start making bubbles, we need to have a look at how Dynamics CRM renders the chart.
- X axis is defined by the GroupBy and Category in the xml.
- Y axis is the first Y value defined.
- Bubble size is the second Y value defined.
Bubble Charts initially look great, but they do have some limitations in Dynamics CRM. Hopefully I will provide enough information for you to decide whether or not bubble charts work for your scenario. I will also have some suggestions on how to overcome some of the limitations.
This post also details how to create charts that use multiple Y values, which a number of the custom chart types do.
Chart Editor – Create the base chart
Let us get started and create a base chart on the opportunity entity we can use to customize the chart xml.
Add the two series, Y Values, we need.
- Probability (height on Y axis)
- Est. Revenue (bubble size)
Add Est. Close Date as the Category to get that on the X axis.
Now export the chart xml and let’s have a look.
Create a measure with two Y values in the chart XML
In the screenshots below, I have changed the aliases to something that reads easier than the auto-generated ones.
The important part is to identify the measurecollection. In the chart xml we exported there are two measurecollections, one for each of the two series we added.
Bubble charts need one series with two Y values. Not two series with one Y value each.
We need to modify the measurecollection so we instead have one collection with two measures, i.e. one series with two Y values.
Now that we only have one measurecollection, we also need to remove one of the two series that is in the xml. Remove everything related to the secondary Y axis at the same time as well.
Finally, change the chart type in the remaining series to “Bubble”, not depicted in the screenshot above, and import the chart xml into Dynamics CRM.
Squares!! So far, so good.
Style the Bubbles!
Squares may not be exactly what we were looking for. The bubble, or marker, is defined the MarkerStyle options which goes in the series. See all the MarkerStyle options on MSDN.
“Circle” is the obvious choice for bubble charts.
We also need to use some of the custom attributes for bubble charts. Here is an overview of bubble chart options on MSDN.
Here are the changes I made in the series of the chart xml. Full XML sample is available at the end of the blog post.
- Font=”Arial, 16px”
I removed the legend because it is not needed for this chart and would be a waste of space.
IsValueShownAsLabel, LabelFormat, Font, LabelStyle and BubbleUseSizeForLabel are all related to creating the label with value of the Est. Revenue below the bubble in thousands. The last piece, BubbleUseSizeForLabel, is for displaying the second Y value as the label. Otherwise it would display the first Y value, which is the Probability. See the image with the squares for an example. More on label formatting in charts here.
Import the updated chart xml and your result should look like this.
Now we have a bubble chart with nice labels. All the bubbles have the same color because they are all in the same series. That leads me to some areas that you need to be careful with when creating bubble charts.
Notable drawbacks about the bubble chart:
- Cannot identify individual opportunities
- Cannot do drill-down
- Opportunities with the same Est. Close Date will be grouped in to one bubble!
These could be some pretty serious drawbacks, but I will show you some tricks to get around them. The solutions are definitely not foolproof and should be used with care. It all really boils down to your specific scenario. However, these tricks do work best with a limited number of opportunities.
Add Bubbly Colors
First step to make this a little better is to give each bubble its own color. We can do this by adding a palette to the series itself.
I use SemiTransparent as it has some nice bubbly colors (pun certainly intended).
Adding a Palette to a series will give each datapoint its own color. It can be a useful to distinguish columns etc. in charts where only one color is used. You can only use one of the named palettes. That means you cannot create your own color scheme for this. However, there is a number of named palettes to choose from. Click here for a full list of palettes with samples.
Import the xml with the palette and you should get something like this.
Note that I also cleaned up the axes titles and intervals a little at the same time. Full chart xml sample available at the bottom of the post.
Create a “Legend Chart”
So far so good, but that does not solve the issues that I mentioned earlier. I can’t tell which bubble belongs to which opportunity. To solve this, I will make another chart, with the same color coding displaying the names of the opportunities. Basically, I am creating a chart which will serve as a legend.
This “legend chart” is build from a stacked bar chart. I will not cover the layout customization as they are detailed this post: Design Options for Bar Charts
The most important part of this chart, is that the order has to match the bubble chart perfectly. Otherwise, the colors will not match. The bubble chart is grouped by, and therefore also sorted by date on the “Est. Close Date” field.
That means you need to use the Est. Close Date for the aggregation and then manually insert an order by that alias.
This is my starting point in the chart designer to create the base chart.
Here is the fetchcollection with changes highlighted. Full sample is available at the bottom of this post.
The order has been added manually and the aggregate has been changed to “max”, which allows us to group by the opportunity and not the date. Of course, also insert the same SemiTransparent palette in the series.
Bubble Chart and “Legend Chart” together
Here is the bubble chart and the “legend chart” in a dashboard next to each other. The opportunity on top of the “legend chart” matches the color of the Est. Close Date that is nearest. You can also use the combination of the two charts to check that you are not impacted by some of the bubble chart limitations. If the bubble chart had grouped some opportunities into one bubble, the first and last colors of the bubble chart and “legend chart” would not match.
Here is the final version in a dashboard.
Thanks for reading. Please sign up for my newsletter and follow me on Twitter Follow @crmchartguy
Bubble Chart XML Sample
As always, samples are provided for fun and training purposes only. Do not use in production without proper testing, and in this case, very carefully reviewing the mentioned drawbacks and limitations on this chart type.
Color Code for Bubbles – “Legend Chart”
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
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.
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.
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.
The Order of which a series is displayed can be a little tricky to work with. If no order is defined in the chart, the chart will display your data according to the sort order defined in the View that you are using. So excluding the order clause from the chart can give you a more dynamic way of adjusting your charts, as you won’t need to do xml customizations. Relying on the view also limits your sorting options to name fields or dates. I.e. you cannot use the View settings to order on aggregated fields.
However, it is often needed to specify one or more order clauses to make sure your data is presented properly.
To specify how data is ordered, or sorted, we utilize the Order clause in the fetchcollection in the xml.
In this example I have added two order clauses to a case chart.
The first line is the primary sort order and the following line is the secondary sort order. That way you can control the sort orders by adding the order clauses in the correct order. That was too many “orders” for one sentence.
- Order clauses can only be set for fields on the primary entity
- When using option sets as the basis of a series, the series get ordered according to the value of the option set and not its label. However, it seems that this only applies if an order clause has not been added to the fetchcollection
- More info on MS website can be found here http://msdn.microsoft.com/en-us/library/gg309565.aspx#orderby_aggregate
- If you need to sort on a linked entity, please see this blog post