Custom Format Masks

A custom format mask provides additional options for formatting a column that contains numeric data (for example, revenue or billed quantity). To set a custom format mask for a column that contains numeric data, you use the Treat Numbers As field in conjunction with the Custom Numeric field. See "Column Properties dialog: Data Format tab" for additional information.

Table 7-1 shows the mask character, a description, and an example of a numeric field that uses the mask.

Table 7-1 Custom Format Mask for Numbers

Mask Character	Description	Example
#	Use a pound sign or hash mark for an optional digit (preceding and trailing zeroes will be removed)	Specifying #,###,##0.### as the mask for 12345.67, yields 12,345.67.
0	Use a zero for a required digit	Specifying #0.##0 as the mask for 12.34, yields 12.340.
" "	Use double quotations to display anything verbatim	Specifying "++++" as part of a mask, yields ++++.
.	Use a decimal point to separate the whole part from the fractional part of the number	Specifying ##0.## as the mask for 123.45, yields 123.45.
,	Use a comma to separate thousands	Specifying #,##0.00 as the mask for 1234, yields 1,234.00.
;	Use a semicolon to separate positive, negative, and null formats	Specifying "+"#0.000;"-"#0.000
[$]	Use as a locale-specific currency symbol	Specifying [$]#,##0.## as the mask for 5000.48, yields $5,000.48 for a user having English-United States selected as the locale, and £5,000.48 if English-United Kingdom is selected as the locale.

A custom format mask allows you to override both the negative and null format by providing separate masks that are separated by semicolons. A fully defined mask might appear as positivemask;negativemask;nullmask. See Figure 7-6.

If you omit the null mask, then a blank displays for a null value.
If you omit the negative mask, then the positive mask displays for the negative mask.
You must use a negative mask, if you want to use a null mask.
If you use all #'s as the mask, the value zero (0) displays as blank.

Figure 7-6 shows a table of revenue and a "year ago revenue calculation" by year and quarter for the Americas. The same custom format mask was used for both measure columns, and the text "null" was set to display for the measure calculation (see "Understanding Null Suppression" for additional information on null suppression). The custom format mask used in this table for the two columns is: "+"#0.000;"-"#0.000;"null".

A plus sign with a two decimal position mask displays for a positive number.
A minus sign with a two decimal position mask displays for a negative number.
If the value of the measure is null, the word null displays.

Figure 7-6 Example of a Fully Defined Custom Mask

This image is described in surrounding text.

Open the analysis in which you want to edit formatting in the "Analysis editor: Criteria tab".
Add or locate the column to modify.
Click the Options button to the right of the column name in the Selected Columns pane.
Click Column Properties. The "Column Properties dialog" is displayed.
Click the "Column Properties dialog: Data Format tab".
Select the Override Default Data Format check box.
Select Custom from the Treat Numbers As list.
Enter the mask for the numeric field in the Custom Numeric Format text box and click the OK button.