Archive for category Format
I get a lot of questions on how to change the date format on the labels. A common one is changing the week grouping from “Week 35 of 2016” to “Week of Aug 29.”
Coming from Europe, I think week numbers are an awesome way of keeping track of the weeks, but like the metric system it just never caught on here in the US, regardless of how much sense it makes.
So even though week numbers are the better option, like column/bar charts over funnel charts, let’s have a look at how we can work with the date formatting on labels.
First up, MS Dynamics CRM does not support changing the date labels on an axis. If you change the xml for the labels, the chart will either just show you the standard date formatting, or the chart will show a render error, even though the xml is correct. For this reason, I’ve previously stated that this requirement cannot be met with charts in Dynamics CRM.
However, unlike the axis, you can actually format the dates on the label for the series. So to address the question on changing the week numbers on the axis label, I will try and remove those labels and use a series label instead to show the date in the desired format.
So the end result will look like this:
Rather than this standard labeling:
It is not perfect, so if you can live with the drawbacks I mention throughout this post, it will probably work for you.
Build the Base Chart
Let’s create the foundation. In the chart editor, we will need two series, and of course, the week we are grouping by.
We need two series. One to show the actual count of opportunities, and one to hold the label for the custom date formatting.
Export the Chart XML
Open the chart xml in Notepad ++ and get ready to make some changes.
In the CRM chart xml below, I have updated all the auto generated aliases to something more meaningful in this context.
In the xml, my changes and additions are in green.
Besides updating the aliases, I made one significant change in this section. In the attribute for the “dateformat,” I changed the aggregate to “min” for minimum.
Also note that I have the “dateformat” before the “countofopportunities” in the category collection. This way the count, which is the number we want, is drawn last and on top.
Before you import, scroll down on your chart xml and make this VERY IMPORTANT change.
In LabelStyle for the first Y axis, add Format=”CRM Chart Guy” as above.
If you do not add this format you will get a rendering error. This seems to be a bug in how Dynamics CRM renders charts, but this is the way to get around it.
Now you can import the chart and make sure everything is still working.
So far, so good. You should now be able to see that one of the bars contains the date, while the other still has the count.
As you have probably figured out by now, the date that we are going to get may not be the Monday of that given week. It will be the earliest date of that week, where data was present. So use with care as it could be misleading if your first date happens to be the Friday of a given week. However, for daily events, it should be fairly accurate, or at least close enough.
Edit the Series
Go back to the chart xml and locate the two series. We need to make the following updates:
- Remove the legend items – not needed in this case
- Draw the bars on top of each other instead of side by side
- Make the date series bar invisible/transparent – we only want the label
- Give the bar displaying the count a brighter color so it is easier to read text on it
- Change the date label format
- Move the date label so it is left aligned on the chart
Here are the additions to the chart xml in the same order as above:
- IsVisibleInLegend=”False” to both series
- DrawSideBySide=False to one of the series (this custom property will automatically apply to all the bar series)
- Color=”Transparent” to the dateformat series
- Color=”LightBlue” to the countofopportunities series (more on colors in CRM Charts here)
- LabelFormat=”‘Week of’ MMM dd” on the dateformat series
- BarLabelStyle=Left to the dateformat series custom properties
Import the xml back in to MS Dynamics CRM
Custom Date Format
The LabelFormat=”‘Week of’ MMM dd” is what gave the label the “Week of Aug 29” label, or whichever date was the lowest for that group.
Here are the most common options you can use:
- “d” = The day of the month, from 1 through 31
- “dd” = The day of the month, from 01 through 31
- “ddd” = Abbreviated name of the day; Mon, Tue, Wed etc.
- “dddd” = Full name of the day
- “M” = The month, from 1 through 12
- “MM” = The month, from 01 through 12
- “MMM” = Abbreviated name of month, i.e. Jan, Feb, Mar etc.
- “MMMM” = Full name of month
- “yy” = The year, from 00 to 99
- “yyyy” = The year in four digits
Pay attention to the case of the letters. “m” is for minutes, while “M” is for months.
The full set of options can be found on MSDN here.
As in this post, you can combine the options like “dd MMM yy” for “31 Aug 16”.
Also, when adding leading text like ‘Week of’, put it in single quotes to avoid some letters being displayed as a value instead.
Chart Area and Axes Modifications
Ok, let’s fix those axes and the labels that just say CRM Chart Guy.
This is actually really simple. I just deactivated them by adding Enabled=”False” to both AxisY and AxisX.
Fun fact: I think this is my first post where I left the secondary Y axis alone 🙂
Now, I did a little more than that. Right before the AxisY, I made a few modifications to the ChartArea.
- Changed BorderColor from “White” to “165,172, 181” (I just copied the gray color from the major tick mark)
- Added BackColor=”10, 59, 59, 59″ to give the background a little more substance. (Copied the label ForeColor and added a lot of transparency)
Import and your result should look a little like this.
Notice there may be some conflicts between the date label and the actual value, when the value is low. That can be alleviated by either simply removing the value, it will still appear in the hover text, or giving the chart some more horizontal space if it is on dashboard.
The context where I usually use custom date formatting is on Key Figures Charts.
Using the custom date formatting, I can write the dates in whatever format desired and can even leave out irrelevant pieces.
Add Some Corporate Branding
Finally, add this section right before the </Chart> closing tag.
Looks great! You can read more about Elev8 Solutions and our innovative immersive approach to CRM projects here. Which also happens to be where I work.
As always, thanks for reading and please sign up for my newsletter on this page and follow me on Twitter Follow @CRMChartGuy
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!
The 100% stacked bar chart is great to display the relative amounts within a series.
However, for some reason the 100% stacked bar chart comes without labels to indicate what the percentage is for each group. You can eyeball it, but that’s not very accurate.
It is a little tricky to get to the accurate result, so I will go through the process of getting there.
The obvious thing to try first, is to export the chart xml and add IsValueShownAsLabel=”True” to the series.
This gives us the sum of each individual phase. This could be useful in its own right, but I really want the percentage on the label.
The next obvious thing to try, is to put in Label=”#PERCENT” and set IsValueShownAsLabel=”False”. Why not? It works great for pie charts.
Now we get some great looking percentages, but the values add up to 100 on the second series. Not across like we would expect.
The magic combo to add to the series xml for percentage labels on a 100% stacked bar chart in MS Dynamics CRM is:
IsValueShownAsLabel=”True” LabelFormat=”#.00′ %'”
Finally we have proper percentages across on the bar chart as we would expect.
A couple of other optimizations for a percentage bar chart.
LabelFormat=”#.00 ‘%'” in the series to show the percentage sign and always have 2 decimals.
Format=”0’%'” on the Y-axis to remove decimals and add percentage sign.
More on where and how to add Format and LabelFormat in MS Dynamics CRM charts here.
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
Formatting the labels on CRM charts can be a little tricky at times.
That’s why I’ve created a little cheat sheet, so I can always find a custom formatting option that fits, and copy it in to my CRM chart xml. Jump to the bottom of this post, if you want to go straight to the cheat sheet.
LabelFormat, or Format, can be used in two places. Either for the values displayed inside the chart, i.e. to indicate the exact value of a column, or the values on the axes, usually the Y axis. The formatting controls how values are displayed in charts. See examples below.
At the end of this post, I will explain where to insert the formatting and which property to use, depending on whether you want to format the values on the axis, or the values inside the chart.
I’ve tried to “format” my examples as code, so they can be cut and pasted directly into a crm chart xml. Hopefully I succeeded.
Disclaimer: This post contains a ton of commas and decimal points, and I’m sure I misplaced some of them. As Murphy’s Law will have it, it will be in the one you might want to copy. So always check that the results are what you want.
First I’ll go through how the formats are structured.
Important LabelFormat Characters
0 – Zero placeholder. Shows all digits incl. zeros.
# – Digit placeholder. Shows all digits except zeros, unless the zero is significant.
. – Decimal point
, – Thousand separator and number scaling
; – Section separator
The difference between # and 0 is how zeros are handled. Note the difference in the following two formats when displaying the value “0.30”
LabelFormat="#.##" = .3 LabelFormat="0.00" = 0.30
Or for example, the zeros in 30 and 105 are significant, but they are not significant in 0.50 and 11.00 (and 0 itself) and therefore not displayed.
Number Scaling – The Thousand Separator and Decimal Point
The thousand separator, or number scaler, is helpful if you want to shorten large numbers. Such as showing numbers in millions. Adjusting the amount of thousand separators allow you to adjust how much the number is scaled. Adding a decimal point and a placeholder, allow you to control the amount of decimals after the number has been scaled. See the following examples for the value 3,456,852.50
LabelFormat="#,#,," // = 3 LabelFormat="#,#,,.##" // = 3.46 LabelFormat="#,#,,.###" // = 3.457 LabelFormat="#,#,.#" // = 3,456.9
Now the same formats for the value 456,852.5
LabelFormat="#,#,," // = (nothing) LabelFormat="#,#,,.##" // = .46 LabelFormat-"#,0,,.###" // = 0.457 LabelFormat-"#,0,.#" // = 456.9
Note the # placeholder was changed to a zero in the last examples. That way the value can have a leading zero instead of just a dot.
Values are always rounded.
Add Text or Characters
You can add characters, like currency symbols, and spaces to the format so it is easier to read. Example value 3,456,852.50
LabelFormat="$#,#,,M" // = $3M LabelFormat="$#,#,,.##M" // = $3.46M LabelFormat="$ #,#,, M" // = $ 3 M LabelFormat="United States Dollars #,#,,.## M" // = United States Dollars 3.46 M
Caution: There’s no check that you are adding the right number of thousand separators or adding the correct currency symbol to the format. In a multi-currency setup, you could easily create a chart that displays 500 Euros as “$ 500 k”.
The semi-colon can be used to add more sections to your format. You can have up to three section in your format string.
- 1st section – Positive Values
- 2nd section – Negative Values
- 3rd section – Zero Values
This means you can format positive, negative and zero values individually and/or suppress some of them. So yes, that means it is possible to suppress zeroes in MS CRM charts.
If no extra sections are added, all values are displayed using the one format provided. Negative values are displayed with a minus sign -.
Example: Positive value is displayed with two decimals, negative values are in a parenthesis, and zero values are just a single zero.
Value 24.5 shown as 24.50
Value -24.5 shown as (24.50)
Value 0.00 shown as 0
If you do not add a parenthesis or a minus sign, or something, to the negative value, it will display just like the positive values with no indication of being negative aside from its position on the axis.
You can suppress zeros, or the negative or positive values depending on your needs. Just add two single quotes in the appropriate section.
Suppressing Negative values and zeros
When adding text such as “$” and “k” to your format, it is good use all three sections and handle the zero separately. Otherwise zero values can appear as “$ k”.
Not really a setting, but there are a few things worth mentioning about labels and currency.
- If no format is added, the chart automatically uses the correct currency symbol
- When using the base currency field, it shows the value in the base currency
- When using the normal currency field, the chart shows the values in the Users default currency, and properly calculated based on the exchange rates in the system.
If you do not need to scale the number, you can use “C” followed by a specifier to indicate the amount of decimals. If you do not add a specifier, decimals will be the same as the default in your CRM.
LabelFormat="C" // Currency with default decimals LabelFormat="C0" // Currency with no decimals LabelFormat="C3" // Currency with 3 decimals forced
A negative value with “C” format is displayed in a parenthesis, unlike the default format which uses a minus sign.
These cannot be combined with the custom formats, so you can’t use the currency setting and scale the number at the same time. My guess is scaling is the main reason you are reading this post.
The Percentage Sign %
If you add a percentage sign % in your format, the number will automatically be multiplied with 100.
LabelFormat="#%" // will display 0.5 as 50%
This is different from changing the label property to Label=”#PERCENT”. That is not really number formatting, so I will need to leave “#PERCENT” , “#AXISLABEL” and other keywords for a future blog post.
LabelFormat="$#,0" // No scaling, No decimals, leading zero LabelFormat="$#,0,K" // Thousands, No decimals, leading zero LabelFormat="$#,0,,M" // Millions, No decimals, leading zero LabelFormat="$#,0,,,B" // Billions, No decimals, leading zero LabelFormat="$#,0,,,,T" // Trillions, No decimals, leading zero LabelFormat="$#,0.00" // No scaling, Two decimals, leading zero LabelFormat="$#,0,.00K" // Thousands, Two decimals, leading zero LabelFormat="$#,0,,.00M" // Millions, Two decimals, leading zero LabelFormat="$#,0,,,.00B" // Billions, Two decimals, leading zero LabelFormat="$#,0,,,,.00T" // Trillions, Two decimals, leading zero LabelFormat="$#,0.##" // No scaling, Up to two decimals, leading zero LabelFormat="$#,0,.##K" // Thousands, Up to two decimals, leading zero LabelFormat="$#,0,,.##M" // Millions, Up to two decimals, leading zero LabelFormat="$#,0,,,.##B" // Billions, Up to two decimals, leading zero LabelFormat="$#,0,,,,.##T" // Trillions, Up to two decimals, leading zero LabelFormat="$#,#.##" // No scaling, Up to two decimals, no leading zero LabelFormat="$#,#,.##K" // Thousands, Up to two decimals, no leading zero LabelFormat="$#,#,,.##M" // Millions, Up to two decimals, no leading zero LabelFormat="$#,#,,,.##B" // Billions, Up to two decimals, no leading zero LabelFormat="$#,#,,,,.##T" // Trillions, Up to two decimals, no leading zero
Inserting the LabelFormat in the chart XML
In a Series
If you want to change the format for the values inside the chart, you have to insert the LabelFormat property in the Series collection.
If the IsValueShownAsLabel=”True” not present already, then insert it as well.
Note that this is done per series, so you can have different formatting for each series if you need to.
On an Axis
If you want to change the formatting along the axis, usually the Y axis, insert it in the LabelStyle section in the appropriate axis.
Note that the property here is called Format and not LabelFormat, but the structure of the format string is the same.
Hope you liked the post. Feel free to sign up for the email notification or follow me on Twitter
The reference for Custom Numeric Format String can be found on the Microsoft website here http://msdn.microsoft.com/en-us/library/aa719871(v=vs.71).aspx