Oracle BI Publishing: forcing leading zeros

Problem

You want to make sure that your leading zeros are displayed when exported to spreadsheets.

There are ways to do this in your template, but if like me you are having to use basic word functionality because of BI template builder issues, then this is how you do it in SQL.

Solution

Prefix your values with =" and follow with a "

Like this

select
       '="'||nvl(pv.num_1099,pv.individual_1099)|| '"'  TIN,
       pv.segment1 ACCOUNT_NUMBER...

Result

Note that the =" does not paste when copying (which is good)

Note also , that this should not be used for numbers, just text fields that hold number. that's because you can't sum these field without doing some fiddling in excel.

Acknowledgement

http://stackoverflow.com/questions/18335486/oracle-bi-publisher-how-to-format-numbers-as-text-so-that-leading-zeroes-dont