Skip Headers
Oracle® Business Intelligence Enterprise Edition Help
11g Release 1 (11.1.1)
  Go To Table Of Contents

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.


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:

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)

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.


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".

Figure 7-6 Example of a Fully Defined Custom Mask

This image is described in surrounding text.
  1. Open the analysis in which you want to edit formatting in the "Analysis editor: Criteria tab."

  2. Add or locate the column to modify.

  3. Click the Options button to the right of the column name in the Selected Columns pane.

  4. Click Column Properties. The "Column Properties dialog" is displayed.

  5. Click the "Column Properties dialog: Data Format tab."

  6. Select the Override Default Data Format check box.

  7. Select Custom from the Treat Numbers As list.

  8. Enter the mask for the numeric field in the Custom Numeric Format text box and click the OK button.