Format Numbers, Dates, and Currencies
This section provides details for formatting numbers, dates, and currencies.
It contains the following topics:
Format Numbers
You can use two methods for specifying the number format.
-
Oracle's format-number function (recommended).
-
Microsoft Word's Native number format mask.
You can also use the native XSL format-number function to format numbers. For information, see Format Native XSL Numbers.
Use only one of these methods. If the number format mask is specified using both methods, then the data is formatted twice, causing unexpected behavior.
The group separator and the number separator are set at runtime based on the template locale. If you are working in a locale other than en-US, or the templates require translation, use the Oracle format masks.
Data Source Requirements
To use the Oracle format mask or the Microsoft format mask, the numbers in the data source must be in a raw format, with no formatting applied (for example: 1000.00). If the number has been formatted for European countries (for example: 1.000,00) then the format won't work.
The Publisher parser requires the Java BigDecimal string representation. This consists of an optional sign ("-") followed by a sequence of zero or more decimal digits (the integer), optionally followed by a fraction, and optionally followed by an exponent. For example: -123456.3455e-3.
Localization Considerations
If you are working in a locale other than en-US, or the templates require translation, then use the Oracle format masks.
The Microsoft format masks can generate unexpected results in templates run in different locale settings.
Do not include "%" in the format mask because this fixes the location of the percent sign in the number display, while the desired position could be at the beginning or the end of a number, depending on the locale.
Use the Microsoft Number Format Mask
To format numeric values, use Microsoft Word's field formatting features available from the Text Form Field Options dialog.
To apply a number format to a form field:
- Open the Form Field Options dialog for the placeholder field.
- Set the Type to Number.
- Select the appropriate Number format from the list of options.
Supported Microsoft Format Mask Definitions
You can use several format mask definitions to standardize the report output.
The following table lists the supported Microsoft format mask definitions for pixel-perfect reports.
Symbol | Location | Meaning |
---|---|---|
0 |
Number |
Digit. Each explicitly set 0 appears, if no other number occupies the position. Example: Format mask: 00.0000 Data: 1.234 Display: 01.2340 |
# |
Number |
Digit. When set to #, only the incoming data is displayed. Example: Format mask: ##.#### Data: 1.234 Display: 1.234 |
. |
Number |
Determines the position of the decimal separator. The decimal separator symbol used is determined at runtime based on template locale. Example: Format mask: #,##0.00 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56 |
- |
Number |
Determines placement of minus sign for negative numbers. |
, |
Number |
Determines the placement of the grouping separator. The grouping separator symbol used is determined at runtime based on template locale. Example: Format mask: #,##0.00 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56 |
E |
Number |
Separates mantissa and exponent in a scientific notation. Example: 0.###E+0 plus sign always shown for positive numbers 0.###E-0 plus sign not shown for positive numbers |
; |
Subpattern boundary |
Separates positive and negative subpatterns. See the Note that follows the table. |
% |
Prefix or Suffix |
Multiply by 100 and show as percentage |
' |
Prefix or Suffix |
Used to quote special characters in a prefix or suffix. |
Subpattern boundary: A pattern contains a positive and negative subpattern, for example, "#,##0.00;(#,##0.00)". Each subpattern has a prefix, numeric part, and suffix. The negative subpattern is optional. If absent, the positive subpattern prefixed with the localized minus sign ("-" in most locales) is used as the negative subpattern. That is, "0.00" alone is equivalent to "0.00;-0.00". If there's an explicit negative subpattern, it serves only to specify the negative prefix and suffix. The number of digits, minimal digits, and other characteristics are all the same as the positive pattern. That means that "#,##0.0#;(#)" produces precisely the same behavior as "#,##0.0#;(#,##0.0#)".
Use the Oracle Format Mask
You can use the Oracle format mask in form fields.
To apply the Oracle format mask to a form field:
Format Dates
Publisher supports three methods for specifying the date format.
-
Specify an explicit date format mask using Microsoft Word's native date format mask.
-
Specify an explicit date format mask using Oracle's format-date function.
-
Specify an abstract date format mask using Oracle's abstract date format masks. (Recommended for multilingual templates.)
Use only one method. If both the Oracle and MS format masks are specified, the data is formatted twice, which causes unexpected behavior.
Data Source Requirements
To use the Microsoft format mask or the Oracle format mask, the date from the XML data source must be in canonical format.
Format:
YYYY-MM-DDThh:mm:ss+HH:MM
where
-
YYYY is the year
-
MM is the month
-
DD is the day
-
T is the separator between the date and time component
-
hh is the hour in 24-hour format
-
mm is the minutes
-
ss is the seconds
-
+HH:MM is the time zone offset from Universal Time (UTC), or Greenwich Mean Time
An example of this construction is:
2005-01-01T09:30:10-07:00
The data after the "T" is optional, therefore the following date: 2005-01-01 can be formatted using either date formatting option.
If the time component and time zone offset are not included in the XML source date, then Publisher assumes it represents 12:00 AM UTC (that is, yyyy-mm-ddT00:00:00-00:00).
Use the Microsoft Date Format Mask
To format date values, use Microsoft Word's field formatting features available from the Form Field Options dialog.
To apply a date format to a form field:
- Open the Form Field Options dialog box for the placeholder field.
- Set the Type to Date, Current Date, or Current Time.
- Select the appropriate Date format from the list of options.
If you don't specify the mask in the Date format field, then the abstract format mask "MEDIUM" is used as default.
Supported Microsoft date format masks:
- d - The day of the month. Single-digit days don't have a leading zero.
- dd - The day of the month. Single-digit days have a leading zero.
- ddd - The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
- dddd - The full name of the day of the week, as defined in DayNames.
- M - The numeric month. Single-digit months don't have a leading zero.
- MM - The numeric month. Single-digit months have a leading zero.
- MMM - The abbreviated name of the month, as defined in AbbreviatedMonthNames.
- yy - The year without the century. If the year without the century is less than 10, the year is displayed with a leading zero.
- yyyy - The year in four digits.
- gg - The period or era. This pattern is ignored if the date to be formatted doesn't have an associated period or era string.
- h - The hour in a 12-hour clock. Single-digit hours don't have a leading zero.
- H - The hour in a 24-hour clock. Single-digit hours don't have a leading zero.
- HH - The hour in a 24-hour clock. Single-digit hours have a leading zero.
- m - The minute. Single-digit minutes don't have a leading zero.
- mm - The minute. Single-digit minutes have a leading zero.
- s - The second. Single-digit seconds don't have a leading zero.
- ss - The second. Single-digit seconds do have a leading zero.
- f - Displays seconds fractions represented in one digit.
- ff - Displays seconds fractions represented in two digits.
- fff - Displays seconds fractions represented in three digits.
- ffff - Displays seconds fractions represented in four digits.
- fffff - Displays seconds fractions represented in five digits.
- ffffff - Displays seconds fractions represented in six digits.
- fffffff - Displays seconds fractions represented in seven digits.
- tt - The AM/PM designator defined in AMDesignator or PMDesignator, if any.
- z - Displays the time zone offset for the system's current time zone in whole hours only. (This element can be used for formatting only)
- zz - Displays the time zone offset for the system's current time zone in whole hours only. (This element can be used for formatting only)
- zzz - Displays the time zone offset for the system's current time zone in hours and minutes.
- : - The default time separator defined in TimeSeparator.
- / - The default date separator defined in DateSeparator.
- ' - Quoted string. Displays the literal value of any string between two ' characters.
- " - Quoted string. Displays the literal value of any string between two " characters.
Default Format Mask
If you do not want to specify a format mask with either the MS method or the Oracle method, you can omit the mask definition and use the default format mask. The default format mask is the MEDIUM abstract format mask from Oracle.
To use the default option using the Microsoft method, set the Type to Date, but leave the Date format field blank in the Text Form Field Options dialog.
To use the default option using the Oracle method, do not supply a mask definition to the "format-date" function call. For example:
<?format-date:hiredate?>
Oracle Abstract Format Masks
The abstract date format masks reflect the default implementations of date/time formatting in the I18N library.
When you use one of these masks, the output generated depends on the locale that is associated with the report.
Specify the abstract mask using the following syntax:
<?format-date:fieldname;'MASK'?>
where fieldname is the XML element tag and
MASK is the Oracle abstract format mask name
For example:
<?format-date:hiredate;'SHORT'?>
<?format-date:hiredate;'LONG_TIME_TZ'?>
<?format-date:xdoxslt:sysdate_as_xsdformat();'MEDIUM'?>
The following table lists the abstract format masks and the sample output that would be generated for the US locale.
Mask | Output for US Locale |
---|---|
SHORT |
2/31/99 |
MEDIUM |
Dec 31, 1999 |
LONG |
Friday, December 31, 1999 |
SHORT_TIME |
12/31/99 6:15 PM |
MEDIUM_TIME |
Dec 31, 1999 6:15 PM |
LONG_TIME |
Friday, December 31, 1999 6:15 PM |
SHORT_TIME_TZ |
12/31/99 6:15 PM GMT |
MEDIUM_TIME_TZ |
Dec 31, 1999 6:15 PM GMT |
LONG_TIME_TZ |
Friday, December 31, 1999 6:15 PM GMT |
Display the System Date (sysdate) in Reports
To correctly display the sysdate, use the function xdoxslt:sysdate_as_xsdformat() with the <?format-date:?>
command.
For example:
<?format-date:xdoxslt:sysdate_as_xsdformat();'MEDIUM'?>
<?format-date:xdoxslt:sysdate_as_xsdformat();'LONG'?>
<?format-date:xdoxslt:sysdate_as_xsdformat();'LONG_TIME_TZ'?>
<?format-date-and-calendar:xdoxslt:sysdate_as_xsdformat();
'LONG_TIME';'ROC_OFFICIAL';?>
Format Currencies
Publisher enables you to define specific currency format masks to apply to the published data at runtime.
Apply a Currency Format to a Field
Follow these steps to understand the parameters for the format-currency function and to apply a currency format to a field.
The parameters for the format-currency function are as follows:
<?format-currency:Amount_Field;CurrencyCode;displaySymbolOrNot?>
where
Amount_Field
takes the tag name of the XML element that holds the amount value in the data.
CurrencyCode
can either be set to a static value or it can be set dynamically. If the value is static for the report, then enter the ISO three-letter currency code in single quotes, for example, ‘USD’.
To set the value dynamically, enter the tag name of the XML element that holds the ISO currency code. Note that an element that contains the currency code must be present in the data.
At runtime, the Amount_Field
is formatted according to the format you set up for the currency code in the report properties.
displaySymbolOrNot
takes one of the following values: true
or false
. When set to true
, the currency symbol is displayed in the report based on the value for CurrencyCode. If you do not want the currency symbol to be displayed, then you can either enter false
or simply do not specify the parameter.
Example: Display Multiple Currency Formats in a Report
The table here provides an example that assumes you've set up the various currency formats in the report properties.
Currency Code | Format Mask |
---|---|
USD |
9G999D99 |
INR |
9G99G99G999D99 |
In this example, you need not set the currency code dynamically. You've the following elements in the XML data:
<TOTAL_SALES>
<US_SALES>8596526459.56</US_SALES>
<INDIA_SALES>60000000</INDIA_SALES>
</TOTAL_SALES>
You want to display these two total fields in the template.
For US_SALES, the syntax in the Publisher properties field is as follows:
<?format-currency:US_SALES;'USD'?>
At runtime, the fields are displayed as shown in the following figure:
Example: Display Multiple Currency Codes in a Single Report
This simple XML code includes an element that contains the Amount (Trans_amount) and an element that contains the ISO currency code (Cur_Code).
<ROW>
<Trans_Amount>123</Trans_Amount>
<Cur_Code>USD</Cur_Code>
</ROW>
<ROW>
<Trans_Amount>-456</Trans_Amount>
<Cur_Code>GBP</Cur_Code>
</ROW>
<ROW>
<Trans_Amount>748</Trans_Amount>
<Cur_Code>EUR</Cur_Code>
</ROW>
<ROW>
<Trans_Amount>-987</Trans_Amount>
<Cur_Code>JPY</Cur_Code>
</ROW>
To display each of these amounts with the appropriate currency symbol, enter the following in the template for the field in which you want the amounts to display:
<?format-currency:Trans_Amount;Cur_Code;'true'?>
The following figure shows the multiple currency report that is generated: