Search This Blog

Friday, February 17, 2012

BI/XML Publisher: Leading and Trailing zeroes truncated for excel reports

Microsoft Excel is too smart and it identifies whether the value in the cell is a Text or number and applies formatting accordingly. This sometimes becomes an issue for us when we are trying to generate an excel report. For example item number 0003463262360 has all the numbers and starts with zero, this when printed in excel report displays it as 3463262360. Hence all the leading zeroes are truncated. Same issue happens when we have decimal and trailing zeroes.

FO formatting options can be used to get away with this problem. Below is the syntax for same.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?ITEM_NUMBER?>
</fo:bidi-override> 

9 Comments:

Anonymous said...

No, this doesn't resolve the trailing zeroes problem.

I was Expecting to get:
1,000.00
100.00
-1,000.00
-100.00
1,000.10
100.10

However what I got was:
1,000.00
100
-1,000.00
-100
1,000.10
100.1

karthik prabhu said...
This comment has been removed by the author.
Paddy Canning said...

How would you get this to work with etext templates?

Chaz Ayers said...

If I changed the Type to "Regular Text", instead of "Number" and also choose "Force LTR" (same as typing in the code in the post), then it retained trailing spaces for me. So, if you have the luxury to format your numbers prior to pulling them into the BI Publisher report, you can use this method to retain trailing zeros on numbers.

Thanks a lot for your help!

Note: I'm using Oracle BI Publisher Template Builder for Word 10.1.3.4.1 Build 130

Anonymous said...

This is working. But, when we copy the value into Oracle Form (Let's say Invoice Number), and click on Find, it is not returning any value. Looks like it is inserting hidden characters on both ends.

Thanks
Rama Rao

Anonymous said...

Hi Suresh,

Thanks for sharing. It works perfectly !

Bogdan

Anonymous said...

Thx Suresh, it worked

Suresh Ram said...

Excellent Solution. Its works perfectly.
Thanks Suresh.V

Thanks & Regards,
Suresh.R

AdityaSoftware said...

Thanks for sharing. It works perfectly
Please guide us how to format excel cell as TEXT
This cell is format as general, we want't as TEXT formatting.

Copyright (c) All rights reserved. Presented by Suresh Vaishya