Posts Tagged CRM Chart Labels

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 Comments