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 and a description. The third column is a specific example of a numeric field that uses the mask for the United States with a language-base of English.

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 Note: If you use the double quotations mask character to represent numeric data, you must remove the explicit mask character prior to exporting the results of the analysis to Excel because Excel cannot translate the mask character.	Specifying "++++" as part of a mask, yields ++++.
;	Use a semicolon to separate positive, negative, and null formats	Specifying "+"#0.000;"-"#0.000;"nulls" as the mask for 467482.18, yields +467482.18. See Figure 7-6.
[$]	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.

The decimal and thousands separator characters that you use in the Custom Numeric field to define the format mask are always the:

Period — Used to separate the whole part from the fractional part of a number
Comma — Used to separate thousands

However, the separator characters that are actually displayed in analyses and dashboards are dependent upon the country and language in which you are running Oracle BI EE.

Table 7-2 shows examples for specific countries and language combinations. Note that the examples shown in this table are not all inclusive, and are correct at the time of writing.

Table 7-2 Locale-specific Custom Format Masks for Numbers - Decimal and Thousands Characters

Format to Display	Use This Format Mask in BI EE	Pre-defined Country (Language) Separator Character	Typically Used in This Country (Language)
Decimal: 123,45 Thousands: 6.789	Decimal: ###.## Thousands: #,###	Decimal: , (comma) Thousands: . (period)	Argentina (Spanish), Denmark (Danish), Ecuador (Spanish), Germany (German), Greece (Greek), Italy (Italian), and Liechtenstein (German)
Decimal: 123.45 Thousands: 6,789	Decimal: ###.## Thousands: #,###	Decimal: . (period) Thousands: , (comma)	Australia (English), Egypt (Arabic), Guatemala (Spanish), Israel (Hebrew), Japan (Japanese), Singapore (Chinese), and the United States (English)
Decimal: 123.45 Thousands: 6'789	Decimal: ###.## Thousands: #,###	Decimal: . (period) Thousands: ' (apostrophe)	Switzerland (French, German, and Italian)
Decimal: 123,45 Thousands: 6 789	Decimal: ###.## Thousands: #,###	Decimal: , (comma) Thousands: (space)	Finland (Finish), Poland (Polish), and Sweden (Swedish)

Format to Display

Use This Format Mask in BI EE

Pre-defined Country (Language) Separator Character

Typically Used in This Country (Language)

Decimal: 123,45

Thousands: 6.789

Decimal: ###.##

Thousands: #,###

Decimal: , (comma)

Thousands: . (period)

Argentina (Spanish), Denmark (Danish), Ecuador (Spanish), Germany (German), Greece (Greek), Italy (Italian), and Liechtenstein (German)

Decimal: 123.45

Thousands: 6,789

Decimal: ###.##

Thousands: #,###

Decimal: . (period)

Thousands: , (comma)

Australia (English), Egypt (Arabic), Guatemala (Spanish), Israel (Hebrew), Japan (Japanese), Singapore (Chinese), and the United States (English)

Decimal: 123.45

Thousands: 6'789

Decimal: ###.##

Thousands: #,###

Decimal: . (period)

Thousands: ' (apostrophe)

Switzerland (French, German, and Italian)

Decimal: 123,45

Thousands: 6 789

Decimal: ###.##

Thousands: #,###

Decimal: , (comma)

Thousands: (space)

Finland (Finish), Poland (Polish), and Sweden (Swedish)

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.

Note:

In Excel, a fully defined mask might appear as positivemask;negativemask;zeromask;text. When you export a column that uses a null mask, the null mask is dropped because Excel does not support it. In addition, Excel's zero mask is not used as Oracle BI EE does not support a zero mask.

Figure 7-6 shows a table of revenue that includes a "year ago revenue calculation" and a "quarterly compounded yearly growth calculation" by year and quarter for the Americas. The same custom format mask was used for all 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 three 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.