Formatting Values
The Field Properties settings in the report wizards allow you to change how a value is formatted in a report. These settings are accessible by clicking the Field Properties button in step 2 of the Quick Report, Cross-Tab Report, and Label Report Wizards; for the Chart Wizard, the settings are in step 3.
If the Use default format option is turned on, which it is by default, Report Writer uses the defined format for the field. If the format for the field changes in the future, the report uses the new format automatically. If you wish to customize the format for the field, turn this setting off.
If Use default format is turned off, you can specify how the field should be formatted. For numeric fields, choose the desired format from the drop-down list of choices:
Currency, 2 decimal places: this displays the currency symbol, the thousands separator, and two places after the decimal. The format string for this is "{0:c2}." For example, 4132 formatted using this setting displays as $4,132.00 in North America and ?4.132,00 in Germany.
Currency, no decimal places: this displays the currency symbol, the thousands separator, and whole numbers. The format string for this is "{0:c0}." For example, 4132 formatted using this setting displays as $4,132 in North America and ?4.132 in Germany.
Thousands separator, 2 decimal places: this displays the thousands separator and two places after the decimal. The format string for this is "{0:#,##0.00}." For example, 4132 formatted using this setting displays as 4,132.00 in North America and 4.132,00 in Germany.
Thousands separator, 2 decimal places, blank if zero: this displays the thousands separator and two places after the decimal but displays as blank if the value is zero. The format string for this is "{0:#,##0.00;-#,##0.00;""}." For example, 4132 formatted using this setting displays as 4,132.00 in North America and 4.132,00 in Germany.
Thousands separator, no decimal places: this displays the thousands separator and whole numbers. The format string for this is "{0:#,##0}." For example, 4132 formatted using this setting displays as 4,132 in North America and 4.132 in Germany.
Thousands separator, no decimal places, blank if zero: this displays the thousands separator and whole numbers but displays as blank if the value is zero. The format string for this is "{0:#,##0;-#,##0;""}." For example, 4132 formatted using this setting displays as 4,132 in North America and 4.132 in Germany.
Percentage, 2 decimal places: this displays a percentage to two decimal places. The format string for this is "{0:##0.00%}." For example, 0.94 formatted using this setting displays as 94.00% in North America and 94,00% in Germany.
Percentage, no decimal places: this displays a percentage with no decimal places. The format string for this is "{0:##0%}." For example, 0.94 formatted using this setting displays as 94% in both North America and Germany.
You can also choose Custom/empty format. This allows you to enter a custom format string, which takes the form of "{0:format}," where format is a set of format symbols defining how the field is formatted. Commonly used format symbols are:
| Symbol | Description |
|---|---|
| cn | Displays the value using the currency settings based on the Locale setting (for example, "$" as the currency symbol, "," as the thousands separator, and "." as the decimal separator in North America) and the number of decimal places specified as n. For example, 4132 formatted as c2 displays as $4,132.00 in North America and €4.132,00 in Germany. |
| 0 | A place holder for a digit, padded with a zero if necessary. For example, 4132 formatted as 000000 displays as 004132. |
| # | A place holder for a digit, blank if necessary. For example, 4132 formatted as #####0 displays as 4132. |
| , | Displays the thousands separator symbol based on the Locale setting (for example, "," in North America). You only have to specify the character once, not once every three places. For example, 4132 formatted as #,##0 displays as 4,132 in North America and 4.132 in Germany. |
| . | Displays a decimal separator symbol based on the Locale setting (for example, "." in North America). For example, 4132 formatted as #,##0.00 displays as 4,132.00 in North America and 4.132,00 in Germany. |
| % | Multiplies the value by 100 and appends a percentage sign. For example, 0.132 formatted as ##0.00% displays as 13.20%. |
| dn | For a numeric value, left-pads the value with zeros. For example, 10 formatted as d6 displays as 000010. |
| d | For a date/time value, displays the value using the Short Date setting based on the Locale setting. For example, January 10, 2013 2:22:30 PM displays as 1/10/2013 in the U.S. and 10/01/2013 in France. |
| D | Displays a date/time value using the Long Date setting based on the Locale setting. For example, January 10, 2013 2:22:30 PM displays as Thursday, January 10, 2013 in the U.S. |
| g | Displays a date/time value using the Short Date setting based on the Locale setting, including the time without seconds. For example, January 10, 2013 2:22:30 PM displays as 1/10/2013 2:22 PM in the U.S. |
| G | Displays a date/time value using the Short Date setting based on the Locale setting, including the time with seconds. For example, January 10, 2013 2:22:30 PM displays as 1/10/2013 2:22:30 PM in the U.S. |
| t | Displays a date/time value using the Short Time setting based on the Locale setting. For example, January 10, 2013 2:22:30 PM displays as 2:22 PM in the U.S. |
| T | Displays a date/time value using the Long Time setting based on the Locale setting. For example, January 10, 2013 2:22:30 PM displays as 2:22:30 PM in the U.S. |
You can specify how positive, negative, and zero values are displayed by separating the three formats with semi-colons. For example, {0:#,##0.00;-#,##0.00;""} formats positive numbers as #,##0.00, negative numbers as -#,##0.00, and zero as blank.
Other symbols can be used as well; see MSDN (such as http://msdn.microsoft.com/en-us/library/0c899ak8.aspx, http://msdn.microsoft.com/en-us/library/dwhawy9k.aspx, and http://msdn.microsoft.com/en-us/library/az4se3k1.aspx)
For date/time fields, the choices are shown in the following table. Note that not all choices are available for all types of reports; for example, Quarter is only available for cross-tab reports.
| Format | Description | Example |
|---|---|---|
| Date | Displays only the date portion of the date. | 09/25/2015 |
| Date and time, 12 hour format | Displays the date and time in 12 hour format without seconds. | 09/25/2015 1:15 PM |
| Date and time, 12 hour format (seconds) | Displays the date and time in 12 hour format with seconds. | 09/25/2015 1:15:00 PM |
| Date and time, 24 hour format | Displays the date and time in 24 hour format without seconds. | 09/25/2015 13:15 |
| Date and time, 24 hour format (seconds) | Displays the date and time in 24 hour format with seconds. | 09/25/2015 13:15:00 |
| Time, 12 hour (HH:MM:SS) | Displays the time in 12 hour format with seconds. | 1:15:00 PM |
| Time, 12 hour (HH:MM) | Displays the time in 12 hour format without seconds. | 1:15 PM |
| Time, 24 hour (HH:MM:SS) | Displays the time in 24 hour format with seconds. | 13:15:00 |
| Time, 24 hour (HH:MM) | Displays the time in 24 hour format without seconds. | 13:15 |
| Day | Displays the day number. | 25 |
| Day of week | Displays the spelled-out day. | Friday |
| Abbreviated day of week | Displays the abbreviated spelled-out day. | Fri |
| Month | Displays the spelled-out month. | September |
| Abbreviated month | Displays the abbreviated spelled-out month. | Sep |
| Month/year | Displays the spelled-out month and year. | September 2015 |
| Abbreviated month/year | Displays the abbreviated spelled-out month and year. | Sep 2015 |
| Year | Displays the year. | 2015 |
| Hour | Displays the hour. | 10 |
| Quarter | Displays the quarter as a digit | 3 |
| Quarter/Year | Displays the quarter and year. | Q3 2015 |
© IQ reseller, 1996-2020 • Updated: 08/31/16
Comment or report problem with topic