Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How To Format Numbers As Text So That The Report Output Displays Leading Zeros Without Leading Space
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.
Answers
-
Try the below:
- Use normalize-space() in the RTF Template
Wrap your field expression like this:
xml - 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 - Use Excel’s TRIM() or CLEAN() Functions
If you can't change the template, apply this in Excel:
excel
=TRIM(CLEAN(A1)) - 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 Ali0 - Use normalize-space() in the RTF Template
