When creating custom reports in the report designer, the number formatting of a field is handled using a format string. The format string for example will determine if a number is displayed as a percentage, how many decimal places will be shown and what format to use for negatives.


The Calxa report designer just utilises the .NET Framework 4.5 Format Strings and while this support note will provide a basic understanding of format strings and provide some examples, for a more detailed explanation you can also refer to the Microsoft Documentation - Custom Numeric Format Strings.


By clicking on a field control in the Detail section of any custom report a toggle appears in the top right hand corner of the field AccountCell. When you click this toggle, it will expand to display the Table Cell Tasks properties.  There is a field called Format String which allows you to control the number formatting of the field contents in the resulting report.


Note: Conditional Formatting such as making the number appear red when negative or bold under some other condition is handled using the Formatting Rules and not the Format String.


TableCellTasks



What does each part of a format string mean?


To help explain the default Format String of {0:#,#;(#,#);0} for decimal numbers has been broken down below:


{0: - This is required at the start of the format string for formatting purposes.


; - This is required to separate each of the format strings and breaks them up into 3 sections - positive number format, negative number format and the zero number format.


#,# - This is the positive value format that uses a “,” as a 000 (thousand) separator.  The # represents a placeholder for a digit if one is present.


(#,#) – This is a negative value will display and using the “,” will represent the 000 (thousands) separator and brackets around the value. The # represents a placeholder for a digit if one is present.


0 – This is how a zero value will display.


} – The closing bracket is required at the end of the string for formatting purposes.



Examples:


Format String: {0:#,#;(#,#);0}
Raw Value: 12345.67
Formatted Output: 12,345


Raw Value: -12345.67
Formatted Output: (12,345)


Raw Value: 0.00
Formatted Output: 0


Format String: {0:#,#.00;(#,#.00);0.00}
Raw Value:  12345.67
Formatted Output: 12,345.67


Raw Value: -12345.67
Formatted Output: (12,345.67)


Raw Value: 0.00
Formatted Output: 0.00


Raw Value: 12345.60
Formatted Output: 12,345.60


Note: if you use .00 for the decimals in a format string it always put 2 decimal places, even if it is a zero. If however you use .## for the decimal places, it will drop trailing zero’s (example below).


Format String: {0:#,#.##;(#,#.##);0.00}
Raw Value: 12345.60
Formatted Output: 12,345.6



What other format strings are available?

To view the default format strings that are available click on the three dots Dots on the right hand side of the field to open the FormatString Editor.


FormatStringEditor



There are a number of categories to select from:

  • DateTime
  • Number
  • Percent
  • Currency
  • Special
  • General

Each of these categories contain available defaults that can be used by selecting the relevant format and clicking OK.


If you need help on formatting percentages please refer to this specific help note: Change Percentage Formatting.