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”.
Sections
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.
LabelFormat="#,0.00;(#,0.00);0"
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.
Suppress Zeros
You can suppress zeros, or the negative or positive values depending on your needs. Just add two single quotes in the appropriate section.
Suppressing Zeros
LabelFormat="#,0.00;(#,0.00);''"
Suppressing Negative values and zeros
LabelFormat="#,0.00;'';''"
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”.
Currency
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.
Cheat Sheet
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 Follow @crmchartguy
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
crm chart guy 
This is great! You’ve defitely helped me a lot formatting my charts correctly! Thank you
Love your blog! Very Helpful.
now I’m getting greedy…
Is it possible to change the field used for the Series Label? It currently uses the Goal name field, but in some situations for us I’d like to have it use the Goal Owner field for the label but still keep the goal name as something else, or we’d like to have it reference a custom field.
Hi Dan
I’m assuming you are referring to the labels on the X axis. In that case you can’t change them as they are determined by the GroupBy attribute in the xml. However, if your Views only have one goal for each User, I don’t see why you couldn’t set the GroupBy attribute to the owner instead of the goal name, or another custom field. Downside is you need to be extra sure you only have one of each Owner in your Views.
Hi, Great work as always and thanks for posting these. I was hoping you might be able to offer some guidance on getting schema validation to work for exported charts. No matter what xsd schema I seem to use from the SDK, it always states the “” element is not declared. Any thoughts?
Huge thanks as always!
-Dillon
I’m not aware of any unfortunately. Thanks for the comments.
Would you have any recommended resources for discovering attributes for the visualization elements? A book perhaps, or is the schema based on another Microsoft chart schema?
Cheers,
Dillon
The charts are based on .net charts. Not all the properties from .net charts work, but most, so there’s some trial and error involved.
Hi,
How could I take the sum from 2 separate series, add them together and display as 1 series on a chart? In my case, I am needing to take Billable Hours & Non-billable Hours from a Time Entries entity and show the Total Hours in my chart. I could obviously create the field on the Time Entries entity and write some jscript to do this calculation but only need this requirement for charting. Any thoughts?
Thanks,
Tyler
Hi Tyler
Sounds like a 2 series chart, with both series set as a stackedbar chart would do the trick.
Is there a way when the dategrouping=”week” to have the x-axis labels display the first date of the week or even the actual date range for the week rather than “week 1 of 2013”, “week 2 of 2013”?
Sorry, but to my knowledge the means of changing labels for date values are extremely limited. This seems to be a CRM limitation since .Net charts in general do support customization of date labels.
Hey Chart Guy, I know you answered this back in 2013, but are you aware of any changes in CRM 2016 that will now allow this?
ChartGuy, as always, fantastic info. I am having trouble with formatting labels on a pie chart. I’m unable to get values and % in the same label with formatting on the percent portion. I want to show the label with the percent rounded so it would show up like this: 14% (56). Instead I’m able to get this: 14.34% (56). I am using Label=”#PERCENT (#VALY)” but I realize there is not much formatting you can do with that. I’ve tried the LabelFormat but nothing seems to get it. Any thoughts on this one? Thanks again for everything you do.
Thank you Taylor. I believe you’ve hit a limitation in the charts there. The labelformatting only applies to the value generated by the IsValueShownAsLabel. The other labels you can add with the Label=”#xyz” doesn’t seem to have formatting options, so you kind of have to go with what it gives you. This is extra annoying on Bubble charts and other charts with more than one Y value.
Is there a way to properly show percentages in a stacked chart?
Thank you for posting this. Is there a way to do conditional label formatting? For example, if the value is 1,000,000 or greater, show in millions; otherwise show in thousands?
The label formatting is specific to the series, so you can only vary the formatting between each series.
You could create a series for records that are over 1 million and one series for records under 1 million and give them different formatting.
I have found your postings brilliant in getting to grips with editing chart xml, so many thanks. I have searched everywhere for an answer to this question so I’m really hoping you can help. I am doing a chart which is grouped by OWNER (fullname) and this represents the X Axis. But instead of outputting the full name, i.e. Anita White as the Axis labels, I’d like to output the First Name field only. I know I probably need to do a linked entity to the User table to get this but I can’t figure out how I then get the Axis labels to actually use this value as the labels instead of the grouping value. Any ideas?
Hi Anita – thanks for reading.
If you add the owning users first name to your view, you can use that as your groupby. That will put only the first name on the axis. However, if there’s two people named Anita, then their data will be aggregated into the same column. If the reason to only display the first name is from a design perspective, you could look into putting the full name inside a bar chart like this https://crmchartguy.wordpress.com/2013/09/16/design-options-for-bar-charts-and-top-x-bar-charts/
That’s genius 🙂
Great posting!
I already modified the label format removing decimal cases from the estimated values. Do you know to remove decimal cases from the ToolTip? Because when I mouse over on top of the chart, it still appearing the value with decimal cases.
Thanks
Ricardo
[…] When creating a pie chart, the IsValueShownAsLabel=”True” is already added to the chart XML by default. This is what shows the values inside the chart. These values can be formatted by adding the LabelFormat property to the series. Go here for formatting of values in CRM charts. […]
Hi, great post!
Just one thing, for suppressing zeros… I could not manage to make it work with two single quotes ”, but instead I used a space character and it worked (like this: LabelFormat=”#,0 €;-#,0 €; “)
I’m using CRM 2013.
Fred
Thanks for the heads up Fred. I appreciate it. Must be a new CRM 2013 feature 🙂
I’ll try to add it to the post.
[…] More on where and how to add Format and LabelFormat in MS Dynamics CRM charts here. […]
Hi Chart Guy,
awesome site – thanks a lot for the very comprehensive display!
I am however looking for the solution to a problem, that you might not hev adressed yet (at least I couldn’t find anything).
We are working a lot with charts in Dashboards and especially Management likes the “Drilldown” functionality a lot.
What I was wondering, if it is possible to reduce the possibilities in the drop down – because the list is really unnecessary long.
Thanks for any idea you could contribute!
Hi,
These all work perfectly on Y Axis, but unfortunately none of them work on X Axis, as if CRM is ignoring the “Format” tag on X Axis.
Does it have any specific trick to format numbers on X Axis?!
The x axis is used for the groupby clause, so it is usually either a name or a date group, for which formatting don’t apply. I’m not sure which scenario in CRM would give you numbers along the X axis.
[…] You can read more on label formatting in this post LabelFormat Cheat Sheet […]
Hi,
In a multi-currency setup, how can I add respective currency to the label format dynamically based on currency in system settings instead of default currency?
The currency will always be based on the user’s personal setting in options. You can temporarily switch your own personal currency and see the difference.
Thanks. But can I give something to change the Label Format to SEK123M/USD123M dynamically based on user setting.
[…] 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. […]
In a multi-currency setup I have used the LabelFormat=”C0″; the user has been setup with “SEK” (both on the User & in Personal Options). Still I get the € symbol in my Bar Chart (EUR is my Base Currency).
Any idea what is wrong here?
Check that you are using the standard currency field and not the base currency field. Base currency fields will only show in whatever the base currency is.
I have used the standard fields, not the Base currency fields.
In the chart I am using 3 currency fields (as Series). All 3 values are showing the correct value, but with the wrong symbol “€”.
Obviously, when I remove the LabelFormat it does show “kr” (not rounded).
Hi this post is really helpfull thanks allot. My question is if I could use this format with a sales pipeline graph
Yes, you can format the labels in the series for the funnel chart too.
2 Questions:
1. How do we add a percentage to a pie chart that also shows Revenue?
2. How do I add percentage of Actual Revenue to Est. Revenue on a bar chart along with the Actual and Est figures?
[…] Label format to show values in thousands, increased the font and put them inside and centered. […]
[…] 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. […]
Hi thanks for this blog, it is very helpfull.
I was trying to see if there is a way to format a duration field. By default it shows values in minutes, but I need hours. Any ideas of how to do this?
Thanks for this amazing blog. I have a question if it is possible to change the font size of the Title of the chart and make it bold? What about the View used for the chart, can the font and size be changed at all?
Hi Please Can anyone address this problem. Thank you in advance.
Hi I have a chart which shows time spent on calls in minutes, is there a way to use label format to convert it to hours?
Hi Neil. Unfortunately not. It is one of those formatting features that .NET charts support, but have not been included in Dynamics 365/CRM. If you do have a chart showing the aggregate on a duration field, it will sometimes show it as hours rather than minutes, but the experience is very inconsistent. One option is to have a calculated field that takes the duration in minutes and convert it hours instead. Then use that fields on the charts instead.
Hi thanks for this blog, full of lots of useful info and has really helped me.
I have one question that i hope you might be able to answer. Can i change the format of the labels to “Bold” and change the position?
Any help greatly appreciated.
Hi Colin.
Thanks for reading.
You can’t make the label bold, but you can change the font and increase the size, so that might work instead. There’s an example of changing the font in this post https://crmchartguy.wordpress.com/2015/06/07/annotations-in-ms-crm-charts/
For changing the position you would have to use the custom properties. There’s an example of that here https://crmchartguy.wordpress.com/2013/09/16/design-options-for-bar-charts-and-top-x-bar-charts/
Thanks for the help, greatly appreciated.
Hello!
I your posts are really helpful and i really like this site!
I wonder if there is anyway to force datalabes to always be on top of collumns? My problem is that some of the datalabels are placed inside.
Yes, you can use the CustomProperties section in the series to define the exact position. There’s an example in this post https://crmchartguy.wordpress.com/2013/09/16/design-options-for-bar-charts-and-top-x-bar-charts/
The custom properties can also be modified in the Advanced Chart Editor for the XrmToolBox if you have access to that. https://crmchartguy.wordpress.com/2017/06/10/edit-charts-in-the-xrmtoolbox-for-dynamics-365/
[…] For full details on what you can do with the LabelFormats, check out this cheat sheet. […]
[…] For full details on what you can do with the LabelFormats, check out this cheat sheet. […]
Hi, How to set “Number Scaling – The Ten Thousand Separator and Decimal Point”?
Such as 1000000, how to show 10,0000?
Hi Lee. You define the scaling by adding . and , in the formatting, so you can decide whether to use or not. You can use the example above that is the closes to what you want, and then modify it from there.
Hi! I have a Pie Chart with one Series that have currency values, I want to get something like this “USD 1,123,456 – PercentValue”. It is that posible? Thanks in advance for your help. Regards.
Very useful website. Thanks.
Thanks. It was useful
Hi Ulrik – this is super useful – thanks for making it and sharing with us all!
Any chance you’d know how to convert the scale from minutes to hours when using time?
ie – user enters multiple time entries, time entered per day should show total 8 hours, but shows 480 minutes on the chart instead. Any thoughts on how I can change it? 🙂
I was never able to force durations to a specific format. One thing I have done instead, is use the axis grid to align with hours. I.e. have a gridline for every 60 minutes. Makes it a bit easier to read.
You’re a star for responding!
Given the client specifically wanted hours, we ended up converting the time they entered into a calculated, whole number field hidden on the form and using that in the chart.
Thank you enormously for sharing your knowledge – I’ve used it a bunch – and there’s not a dynamics person I know who doesn’t know who you are! 🙂
Hi Ulrik,
Your blog is very useful. Is there any way to rename x-axis category label, my dashboard value displays as ‘Quarter1 of 2020’, is it possible to make ‘Q1-2020’ ?
Also, is it possible to hide chart name in the dashboards?
Dates and Quarters does not accept formatting. Even if you add in the code correctly in the chart xml it will still render with the default option. You can make changes to this in the system settings, or if your fiscal quarters align with calendar quarters, you can use those instead as you also have some flexibility on how those are named.
Hi there!! I happened to stumble upon your site and found it incredibly helpful. Not sure if this is the correct spot to ask, but i am looking to format the y axis to show a currency on only the top value. Is there a way to do this?