LabelFormat Cheat Sheet

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.

Before LabelFormat is applied on MS CRM Charts for MS Dynamics CRM 2011 in the chart xml
Standard labels for currency values in MS CRM Charts
After LabelFormat is applied in the chart xml for MS CRM 2011 Dynamics charts
With a custom format added to the chart xml, the values shorter and easier to read.

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.
Series insert LabelFormat
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.
Axis Format Insert
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

49 thoughts on “LabelFormat Cheat Sheet

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

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

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

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

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

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

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

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

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

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

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

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

      • Thanks. But can I give something to change the Label Format to SEK123M/USD123M dynamically based on user setting.

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

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

  13. Hi this post is really helpfull thanks allot. My question is if I could use this format with a sales pipeline graph

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

  15. […] 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. […]

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

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

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

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