How To Format Numbers As Text So That The Report Output Displays Leading Zeros Without Leading Space

Anil Pandey-Oracle

On : 5.6.3 version, Designing Layout Templates

Have custom reports that need to be generated in either PDF or EXCEL format.
In the XML rtf template, in order to preserve the leading ZEROs in the ACCOUNT_NUMBER when generating EXCEL format, define ACCOUNT_NUMBER as 'Regular text' and use Force LTR option.

For example: The xml tag is defined using the below syntax:
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?ACCOUNT_NUMBER?></fo:bidi-override>

Although neither the XML rtf template nor the XML output file for the pdf have any spaces, found that when generating selecting EXCEL as an output the ACCOUNT_NUMBER has an invisible leading and trailing space which cause the VLOOKUP function to fail.

Riyaz Ali-Oracle

Ho @Anil Pandey-Oracle,

Try the below:

1. Use normalize-space() in the RTF Template
   Wrap your field expression like this:
   xml
2. Avoid fo:bidi-override Unless Necessary
   While fo:bidi-override helps with left-to-right formatting, it can introduce Unicode control characters. If your data is already left-to-right (e.g., numeric account numbers), try removing it:
   xml
3. Use Excel’s TRIM() or CLEAN() Functions
   If you can't change the template, apply this in Excel:
   excel
   =TRIM(CLEAN(A1))
4. Set Field Type Explicitly in Excel Template
   If you're using an Excel template (not RTF), define the field as Text in the metadata sheet and format the cell as "Text" to preserve leading zeros and avoid auto-formatting.

<?xdoxslt:normalize-space(ACCOUNT_NUMBER)?><?ACCOUNT_NUMBER?>

Thanks,
Riyaz Ali