How to Display Leading Zeros in XMLP Report – Excel Output

By | July 5, 2013

Microsoft Excel has a tendency to display number format based columns with no prefix of ZEROs. For example, if there is a value ‘007’ excel displays it as ‘7’, excel display it as ‘007’ only if the column is set in text format. You can see the difference in the below screenshot
String vs Number in Excel

Please go through the below URL for an example to generate an XMLP report:
http://oracleappsdna.com/2013/07/plsql-script-to-generate-xml-tags-for-xmlp-report/

In the example mentioned in the above URL you could see the output as shown in the below screenshot
Emp Report Output

But if you observe the data in the emp table, you could see three rows have leading zeros for employee number( Please note, I have updated the data of seeded emp table with prefix of 00 and also changed the data type of empno column to varchar2(6) for the sake of example.)
EmpTable

As the output is of excel type the empno column is considered as Number column (as the entire column consists of number format data) and the leading zeros are removed in display.

We have multiple ways to resolve this, I have listed them below.

Method 1

  1. Open the RTF Template in MS Word.
  2. Go to Data -> Load XML Data.
  3. Once the data is loaded successfully, double click on that field.
  4. Under field properties window set the field formatting type as “Regular Text” and set the check box “Force LTR”.

Force_LTR_TO_Show_Leading_Zeros_for_a_Number

After following the above said steps, save the template and preview the output which is as shown below
Correct Output

Method 2

Add Ctrl+Shift+Space after/before emplyee number on template to create a non-breaking space. The non-breakable space converts the number column to string column
Emp_RPT_shift+ctrl+space

Disadvantage of this method is that the non-breakable space is visible in the output as well, you can see in the screenshot below
space in emp number

Method 3

This is the best method I have found, In this method we need to use an equal-to symbol before the field and enclose the field in double quotes for example:- =”ENUM”

This works only in excel however it will allow you to cut and paste (ie to use the value to search in Oracle) and also to do vlookups.
Enum with equalto and quotes

The theory is that excel will concatenate the values together because it has quotes around it, it will treat it as a string rather than simply a value.

For any other formats this method will not work.
Final Emp Output

You can see in the above screenshot that Enum column values are turned to blue color as the content is explicit converted to text format from number format.

Hope this article is useful for those who are in need to show leading zeros for number columns in XMLP reports. If you have any best solution, please leave a comment and share with the readers.

Credits:- Thanks to my friend Kiran Reddy for helping me to get this knowledge.

UPDATE: on 20-OCT-2015

Method 4

Here is another way to set format style ‘Force LTR’ in a programmatic way to prevent Excel from suppressing leading zeros:

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?XML_ELEMENT?>
</fo:bidi-override>
WOW! Did you like this post? We'll send more interesting posts like How to Display Leading Zeros in XMLP Report – Excel Output to you!
Enter your Email Address:
  • Vibha

    The FORCE LTR is not working for me. I save it and then when I preview it there is no change. Also the force LTR is not getting saved. Everytime I open it, the Force LTR setting is not there.

    • Nisha

      yes i am facing the same issue.
      I tried the third method as well but column is coming as =”01234E” in excel.