Number Formatting in XML Publisher

While designing XML Publisher reports, many a times we need to play with numbers and change their formats as part of the client’s requirements. Here is a brief explanation of couple of techniques that should be used while doing any number format change in XML Publisher.
XML Publisher supports two methods for specifying the number format:

• Oracle’s format-number function
• Microsoft Word’s Native number format mask

Now…before going into details please remember few things like:

• To use the Oracle format mask or the Microsoft format mask, the numbers in your data source (ex XML File) must be in a raw format, with no formatting applied.
• Use only one of these methods. If the number format mask is specified using both methods, the data will be formatted twice and it may cause unexpected behavior.
• If you are designing a template to be translatable, using currency in the Microsoft format mask is not recommended unless you want the data reported in the same currency for all translations. Using the MS format mask sets the currency in the template so that it cannot be updated at runtime.

Using the Oracle Format Mask:

To apply the Oracle format mask to a form field:
1. Open the Form Field Options dialog box for the placeholder field.
2. Set the Type to “Regular text”.
3. In the Form Field Help Text field, enter the mask definition according to the following example:

<?format-number:fieldname;’999G999D99′?>

Where fieldname is the XML tag name of the data element you are formatting and 999G999D99 is the mask definition.

Symbol	Meaning
0	Digit. Each explicitly set 0 will appear, if no other number occupies the position. Format mask: 00.0000 (Ex: Change 1.234 to 01.2340)
9	Digit. Returns value with the specified number of digits with a leading space if positive or a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. Format mask: 99.9999 (Ex: Change 1.234 to 1.234)
C	Returns the ISO currency symbol in the specified position.
D	Determines the placement of the decimal separator. The decimal separator symbol used will be determined at runtime based on template locale. Format mask: 9G999D99 (Ex: Change 1234.56 to 1,234.56( For English locale:) or to 1.234,56 (For German locale))
EEEE	Returns a value in scientific notation.
G	Determines the placement of the grouping (thousands) separator. The grouping separator symbol used will be determined at runtime based on template locale. Format mask: 9G999D99 (Ex: Change 1234.56 to 1,234.56( For English locale:) or to 1.234,56 (For German locale))
L	Returns the local currency symbol in the specified position.
MI	Displays negative value with a trailing “-”.
PR	Displays negative value enclosed by <>
PT	Displays negative value enclosed by ()
S (before number)	Displays positive value with a leading “+” and negative values with a leading “-”
S (after number)	Displays positive value with a trailing “+” and negative value with a trailing “-”

Using the Microsoft Number Format Mask:

To format numeric values, use Microsoft Word’s field formatting features available from the Text Form Field Options dialog box. The following graphic displays an example:
To apply a number format to a form field:

1. Go into the Word Properties for the field.
2. Change the Type drop-down box to Number (it defaults in as Regular Text)
3. From the Number format drop-down box choose the format you want.

Native XSL format-number function:

You can also use the native XSL format-number function to format numbers. The native XSL format-number function takes the basic format:

format-number(number,format,[decimalformat])

Where:
number (Required) – Specifies the number to be formatted.
Format (Required)  - Specifies the format pattern. Use the following characters to specify the pattern:

Symbol	Meaning
#	Denotes a digit. Example: ####
0	Denotes leading and following zeros. Example: 0000.00
.	The position of the decimal point Example: ###.##
,	The group separator for thousands. Example: ###,###.##
%	Displays the number as a percentage. Example: ##%
;	Pattern separator. The first pattern will be used forpositive numbers and the second for negative numbers

Decimal format (Optional) – For more information on the decimal format please consult any basic XSLT manual.

Currency Formatting:

This is another feature of XML Publisher that enables you to define specific currency format masks to apply to your published data at runtime.
To utilize currency formatting, you must:
1. Define your currency formats in XML Publisher’s Administration interface.
2. Assign the Currency Format Set as a configuration property at the desired level (site, data definition, or template).
3. Enter the format-currency command in your RTF template to apply the format to the field at runtime.
To use the format-currency command:
In the form field dialog of the field you want to format, enter the following syntax:

<?format-currency:ELEMENT_NAME;’currency-format-code’?>

For example:
<?format-currency:BALACE_TOTAL;’USD’?>
The currency code must correspond to a currency format that is defined in the Currency Format Set to be used with this report. The Currency Format Set can be specified at the site level, data definition level, or template level.
For more information, refer Oracle XML Publisher Administration and Developer’s Guide.