When the numeric formatting is selected, a frame with a format string is available. By default, the database default is selected.
The 'Format string’ option provides a text field and a drop box. The drop box contains several predefined formatting options. The text field shows the selection from the drop box.
A custom format may also be entered according to the numeric value string format for MDX or the custom format in Microsoft Excel, as shown in the following example.
Some of the formatting characters are shown in the following table; for further information please refer to the MDX or Excel documentation.
A user-defined format expression for numbers can have anywhere from one to four sections separated by semicolons. If the expression contains:
§ One (1) Section, the format expression applies to all values
§ Two (2) Sections, the first section applies to positive values and zeros, the second to negative values
§ Three (3) Sections, the first section applies to positive values, the second to negative values, and the third to zeros
§ Four (4) Sections, the first section applies to positive values, the second to negative values, the third to zeros, and the fourth to null values.
If the 'Advanced format' link is clicked in the numeric formatting frame, each section may be specified individually and the result is displayed in the 'Format string' drop box.
The ‘Advanced format’ procedure is shown in the example below – In this example, square-brackets ([$ … ]) will be used to format and indicate a negative value instead of the traditional parenthesis ( ($ … ) ) symbols for the ‘Amount’ measure fields (i.e., values & totals) in the selected object.
Note: Only Measures that are present in the selected Analytics object will appear in the Number Formatting Dialog for format editing.
The following illustration shows how numeric formatting is performed using Analytics dialogs to accomplish this example.
The following table describes the Numeric Formatting Symbols and how they may be applied to specify custom formats.
|
Numeric Formatting Symbol |
Numeric Digit Formatting Symbol Description |
|
0 |
Represents a digit placeholder that displays a digit or a zero (0). § If the number has a digit in the position where the zero appears in the format string, the formatted value displays the digit. § Otherwise, the formatted value displays a zero in that position (e.g., 0.0 represents a number followed by one digit after the decimal point: 1234.5).
|
|
# |
Represents a digit placeholder that displays a digit or nothing. § If the expression has a digit in the position where the number sign (#) appears in the format string, the formatted value displays the digit. § Otherwise, the formatted value displays nothing in that position (e.g., #.# represents a number followed by one decimal digit after the decimal point: 1234.5 or no decimals when there are no decimals in the original number: 1234).
|
|
. |
The period (.) represents a decimal placeholder that determines how many digits are displayed to the left and right of the decimal separator (e.g., ‘#.##’ formats as 1234.56). § If the format expression contains only number sign (#) characters to the left of the period (.), numbers smaller than 1 start with a decimal separator. § To format a leading zero displayed with fractional numbers, use zero (0) as the first digit placeholder to the left of the decimal separator. Note: In some currencies/languages, a comma is used as a decimal separator. |
|
, |
Represents a thousand separator that separates thousands from hundreds within a number that has four or more places to the left of the decimal separator (e.g., ‘#,#.#’ formats as 1,234.5). § Standard use of the thousand separator is specified when the format contains a thousand separator enclosed in digit placeholders (0 or #). Note: The thousand separator may also be used to round thousands (e.g., the number format ‘0’, rounds the number 1234567890 to 1234568 and 0,, rounds it to 1235 and so on. |
|
% |
Represents a percentage placeholder. The expression is multiplied by 100. The percent character (%) is inserted in the position where the percentage appears in the format string (e.g., #.00 % formats the value 0.12 for display as 12.00 %). |
|
“AB” |
Displays the literal character string inside the double-quotation marks (e.g., “Profit Percentage”). |
|
For example, a combination of the formatting features listed above applied to a value of 0.21: "Revenue Percentage = " #.00 % will produce a result formatted as: Revenue Percentage = 21.00 %
|