Format Numbers, Dates, and Currencies

This section provides details for formatting numbers, dates, and currencies.

It contains the following topics:

• Format Numbers

• Format Dates

• Format Currencies

Format Numbers

Publisher supports two methods for specifying the number format.

• Oracle's format-number function (recommended).

• Microsoft Word's Native number format mask.

Note:

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:

1. Open the Form Field Options dialog for the placeholder field.
2. Set the Type to Number.
3. Select the appropriate Number format from the list of options.

Supported Microsoft Format Mask Definitions

Several format mask definitions can be used to standardize output.

The following table lists the supported Microsoft format mask definitions.

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.

Note:

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:

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.

   The following illustration shows an example Form Field Help Text dialog entry for the data element "empno".

   
   Description of the illustration xdo_oraclemask.gif

   Supported Oracle number format mask symbols and their definitions:

   • 0 - Digit. Each explicitly set 0 appears, if no other number occupies the position. Example: Format mask: 00.0000 Data: 1.234 Display: 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. Example: Format mask: 99.9999 Data: 1.234 Display: 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 is determined at runtime based on template locale. For example: Format mask: 9G999D99 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56
   • EEEE - Returns a value in scientific notation.
   • G - Determines the placement of the grouping (thousands) separator. The grouping separator symbol used is determined at runtime based on template locale. For example: Format mask: 9G999D99 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56
   • 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 "-".

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.

This format is:

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.

Note:

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:

1. Open the Form Field Options dialog box for the placeholder field.
2. Set the Type to Date, Current Date, or Current Time.
3. 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.

Use the Oracle Format Mask

Use the Oracle format mask to specify how date and time displays.

To apply the Oracle format mask to a date field:

1. Open the Form Field Options dialog box for the placeholder field.
2. Set the Type to Regular Text.
3. Select the Add Help Text... button to open the Form Field Help Text dialog.
4. Insert the following syntax to specify the date format mask:

   <?format-date:date_string; 'ABSTRACT_FORMAT_MASK';'TIMEZONE'?>

   or

   <?format-date-and-calendar:date_string; 'ABSTRACT_FORMAT_MASK';'CALENDAR_NAME';'TIMEZONE'?>

   where time zone is optional. The detailed usage of format mask, calendar and time zone is described below.

   If no format mask is specified, then the abstract format mask "MEDIUM" is used as the default.

   Example form field help text entry:

   <?format-date:hiredate;'YYYY-MM-DD'?>

   Supported Oracle format mask symbols:

   Note:

   Excel2007 output (.xlsx) doesn't support the following format masks: E, EE, W, WW, X, XX. If you use these in an RTF template and generate Excel2007 output, dates with any of these formats won't be viewable. Some combinations of these format masks may result in an error when the Excel2007 (.xlsx) output is opened.

   • - / , . ; : "text" - Punctuation and quoted text are reproduced in the result.
   • AD A.D. - AD indicator with or without periods.
   • AM A.M. - Meridian indicator with or without periods.
   • BC B.C. - BC indicator with or without periods.
   • CC - Century. For example, 2002 returns 21; 2000 returns 20.
   • DAY - Name of day, padded with blanks to length of 9 characters.
   • D - Day of week (1-7).
   • DD - Day of month (1-31).
   • DDD - Day of year (1-366).
   • DL - Returns a value in the long date format.
   • DS - Returns a value in the short date format.
   • DY - Abbreviated name of day.
   • E - Abbreviated era name.
   • EE - Full era name.
   • FF[1..9] - Fractional seconds. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. Example: 'HH:MI:SS.FF3'
   • HH - Hour of day (1-12).
   • HH12 - Hour of day (1-12).
   • HH24 - Hour of day (0-23).
   • MI - Minute (0-59).
   • MM - Month (01-12; JAN = 01).
   • MON - Abbreviated name of month.
   • MONTH - Name of month, padded with blanks to length of 9 characters.
   • PM P.M. - Meridian indicator with or without periods.
   • RR - Lets you store 20th century dates in the 21st century using only two digits.
   • RRRR - Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then simply enter the 4-digit year.
   • SS - Seconds (0-59).
   • TZD - Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond to the region specified in TZR. Example: PST (for Pacific Standard Time), PDT (for Pacific Daylight Time)
   • TZH - Time zone hour. (See TZM format element.)
   • TZM - Time zone minute. (See TZH format element.) Example: 'HH:MI:SS.FFTZH:TZM'
   • TZR - Time zone region information. The value must be one of the time zone regions supported in the database. Example: PST (Pacific Standard Time)
   • WW - Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
   • W - Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
   • X - Local radix character.
   • YYYY - 4-digit year.
   • YY Y - Last 2, or 1 digit(s) of year.

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.

To utilize currency formatting in the RTF template:

1. Set up the currency formats in the runtime configuration properties. The currency formats can be defined at the system level or at the report level.

   When you set up the currency format property, you define the format to be used for a specified currency, using the International Standards Organization (ISO) currency code. A sample is shown in the following figure:

   
   Description of the illustration xdo11g_rep_prop_curr_form.gif
2. Enter the format-currency command in the RTF template to apply the format to the field 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:

Description of the illustration xdo11g_curr_form_ex2.gif

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:

Description of the illustration xdo11g_currency_format.gif