Archive for category Groupby
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”
Comparing sales performance this period vs. the same period last year is a fairly standard metric. However, to create a chart that compares the period this year, with last years in the same date range can be tricky. For example, today is June 21st. I want to see my actual sales for this year to date, compared to my actual sales last year, until June 21st. Or Year to Date vs. Last Year to Date (YTD vs. LYTD).
Or maybe I want to compare my sales this year and last year by month.
In this post I will detail how I created both of these charts.
Compare YTD to LYTD in MS Dynamics CRM
Before I start to create the chart, I need to get some components ready first.
- View of Won Opportunities – This and Last Year
- Fetchxml filter for YTD
- Fetchxml filter for LYTD
Create the View for the chart
Open Advanced Find on opportunities and create the following view:
Name it “Won Opportunities: This And Last Year.” Always use this view with the charts in this post.
After saving it, keep Advanced Find open and use it to create the fetchxml filters we need to modify the chart xml with.
Create the following filters and download the fetchxml. You can download the fetchxml without saving the View first.
Filter for YTD
Filter for LYTD
Using the Older Than X Years will make sure we only get the LYTD date range for the won opportunities.
Note: Are you on a version earlier than CRM2015, you can use “Older Than X Months”, with X being 12, and get the same result.
Time to open up the chart editor.
Save a chart similar to the settings here.
The two series for actual revenue, is what will be modified to YTD and LYTD using the filters we just downloaded from Advanced Find.
Now the chart xml is ready to export and we can have some fun!
The two attributes for the actual value series will need to be edited so they use the filters we just downloaded.
Here is the fetchcollection with the additions made and the filters downloaded from Advanced Find highlighted in yellow.
- Created link-entity outer joins so the series only includes the filtered data.
- Added the YTD and LYTD filters downloaded from Advanced Find.
- Renamed the aliases from _CRMAutoGen_aggregate_column_Num_0 or similar to more sensible names.
- If renaming the aliases, remember to update them in the categorycollection too.
- Make sure the series are in the same order in both the categorycollection and in the series section
- Custom LegendText
- Custom LabelFormat
- Format the Y axis numbers
The categorycollection is where you can adjust if YTD or LYTD should come first. In this example YTD comes first.
The new formatting is designed to show amounts in the thousands with negative and zero values suppressed. See this post for details on chart labels and formatting.
Import the XML and this should be your result.
Here is the chart and we can see how our performance is compared to the same period last year.
If you would like to see the data grouped by something else, such as customer or owner, you can open the editor and change the category.
Just keep in mind that if you make any changes to the legend entries you will lose your xml customizations.
Compare to Last Year by Month
For better insights on our sales development, we can group the sales by month instead.
Start with the chart we just made and use the editor to change the groupby to Actual Close Date and Month.
Click Save As and save the chart under a different name.
The result should look like this.
Now we see the same data spread out per month instead. What I would like to do is include all of last years data and then have the month columns next to each other, so March of this year is next March of last year, etc.
In short, what I will do is remove the Older-Than-1-Year filter so we get all of last year. Next I will split the data onto two separate X axes. One for each year and set the chart to display them in sync with each other so the months line up.
Note: We are working with data by month. If data points are missing for a certain month, that month will not be included. For this chart, we are relying on having 12 data points for each year. If you do not have a data point for each of the past months, then use dummy records to ensure a data point exists for the missing months. These can be of zero value and will not interfere with the calculations. You do not need records for future months.
Update filter and add secondary X axis
Export the chart xml.
First remove the “older than” clause in the fetchcollection.
Now scroll down and add the secondary X axis. Both in the Series and Axis section.
I added XAxisType=”Secondary” to the series for this year to indicate which series should go on the secondary X axis.
The AxisX2 section is identical to AxisX except for the “2” so you can copy it and add the number.
Import the chart xml back into MS Dynamics CRM.
Notice that the secondary X axis has been added to the top of the chart. The blue columns are actually bound to the top (secondary axis) and the orange are on the bottom axis.
Next step is to make the two axes overlap so it is easy to compare a month this year, to the same month last year.
We have 24 data points. One for each month of the two years. The series for last year is displayed on Axis 1 and should show points 0 – 12 (Jan-Dec of 2014). Axis 2 has the data for this year and should show points 12-24 (Jan-Dec of 2015).
To achieve that, I add Minimum and Maximum values to each axis so they are aligned.
Here are the chart xml axis edits.
IntervalOffset=”1″ is added so the axis does not show the zero value (i.e. Dec 2014 on the 2015 axis).
Interval=”1″ is added to make the intervals consistent between the two axes and show every month.
Notice the maximums are set to 12.5 and 24.5. That is to make sure the data for the last month is displayed in full. If set to 12 and 24 only half the column would display.
Import the xml and this should be the final result.
The chart type on Last Year can be changed to Line, ChartType=”Line”, to get this result.
As always, hope you enjoyed the post and found something useful. Please follow me on Twitter Follow @crmchartguy or sign up for the newsletter to be alerted on new posts.
N:N Relationships, or Many-to-Many Relationships, can be a little tricky to get information out of.
As an example competitors out-of-the-box have an N:N relationship with opportunities. Let’s say we wanted to look at a list of competitors and see how many active opportunities they are currently engaged in. With the chart editor and views, the best bet is to open each competitor record and count how many active opportunities it is related to. We can make that much easier with a chart like this.
Before I get started on how to build the chart, let’s first have a look at how N:N relationships are structured in MS Dynamics CRM.
I will use the competitor opportunity relationship as an example, but this applies to all system and custom N:N relationships.
While it is not apparent in the solution designer, the relationship is controlled by an intersect table, in this case called “opportunitycompetitors”.
We will need this exact name of the intersect table for the fetchcollection in our chart xml.
To find the name of the intersect table, open up competitors for customization in the solution editor.
Click N:N Relationships and open the relationship to opportunity.
Here, find the “Relationship Entity Name” towards the bottom of the form and make a note of the exact name for later.
Now that we have the name of the intersect table, on to building the chart. A full sample of the chart xml can be downloaded at the bottom of this post.
Build The Base Chart
As always, I start by creating a base chart I can use for editing the chart xml. I am starting on the competitor entity.
A simple bar chart counting competitors, grouped by competitor. Not very useful yet.
Simple – Count Opportunities Per Competitor
Export the xml and open it in your xml editor (Notepad++ perhaps).
Here’s the fecthcollection. I have renamed the obscure aliases to something more sensible. This is where we will do most of the work. If you change the aliases too, remember to also change them in the categorycollection.
Now I want to change the chart xml, so I get a link to the intersect table and can count the number of relationships the competitor has to opportunities.
I have now created a link to the intersect table and placed the count aggregate inside of it. The groupby is still directly on the competitor entity. I also changed the name of the count attribute to “competitorid”. This is because the intersect table only holds the id fields.
Import to MS Dynamics CRM and look at the result.
Now we have a list of competitors including a count of how many opportunities they are competing on.
On import you may run into this error message.
If that happens, just change the width of the chart or refresh the page and it will work.
Advanced – Count and Sum of Estimated Revenue from Opportunities
That was charting on N:N relationships in its simplest form. However, as always we would like to do more. How about including the sum of the estimated revenue of those opportunities, and also filter it to only include active opportunities.
In order to achieve that, we need to add an extra link-entity to get all the way to the opportunity entity, so we can access fields there directly. In the first example we stopped on the intersect table.
Sample of fetchcollection which links from competitor, via the intersect table, to the opportunity entity.
We now have two sets of <link-entity>, linking through the intersect table to the opportunity entity.
Notice the pattern in the links are name=”intersectable” from=”field with id from intersect table” to=”field with id from starting entity” and in the next line it is name=”the other entity” from=”field with id on other entity” to=”field with id on intersect table”. Since the id fields are mostly the same, once you have the name of the intersect table, these are fairly straight forward. Marketing lists are an exception to this.
Inside the <link-entity> to the opportunity entity, I have added a filter so we only include active opportunities. As usual, I just created a view in Advanced Find, downloaded the fetchxml, and copied the part with the filter.
I also added an extra attribute to include the sum of the estimated revenue for the opportunities.
Here’s what the final chart looks like.
I also made these optimizations to the chart, just to touch it up a little.
- In the Series
- LabelFormat=”C0″ – so values in the chart have no decimals – that’s the letter “C” followed by a zero
- DrawingStyle=Cylinder – in the custom properties just add some shading
- In the Axes
- Format=”C0″ – so dollar values on axis have no decimals
- YAxisType=”Secondary” – on the series containing the sum to get count and sum on different axes
- IsReversed=”True” – on the X axis so it is alphabetical from the top, not the bottom
A full sample can be found below with these changes included.
As a note, charting on N:N relationships also works on Marketing Lists (static only). That means you can create a list of users with a chart of how many marketing lists they are associated with. However, you will need to use a metadata browser to get the names of the intersect table (listmember) and its id field (entityid), which is different in this case.
Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter Follow @crmchartguy
Sample chart xml for count and sum of opportunities per competitor
Here’s a sample chart xml ready for import to the competitor entity. It is made in an environment with no customizations so it should be easy to import. As always, samples are provided for fun only. Do not use in a production environment without proper testing.
If you made it this far. You’re awesome! Seriously. Tweet about it!