Is it possible to consider leading zeros from an XML file in Excel Model BI Publisher?

Kauan_Bohn · February 3

I'm designing a report with two sheets:

One containing a regular table.
Another with a pivot table.

In my XML file, I have the <OP> field displayed as follows:

<G_1>

    <ORD>1</ORD>

    <ORG>0001_OI_CED_MAT</ORG>

    <ITEM>VGA20143</ITEM>

    <DESCRI>VIGA I 6 A572 (W150 X 18.0) C/ 6000MM</DESCRI>

    <OP>0001.2461</OP>

</G_1>

For the first table, I was able to preserve the leading zeros by using the XDO_METADATA file like this:

<?concat(' ', OP)?>

However, I can't use the formatted number in my pivot table, and I'm getting a display error (as shown in the screenshot).

I followed the recommendation from this link to create the pivot table using an Excel Template:
🔗 Adding Native Pivot Charts and Tables to Your Excel Reports

Does anyone know how to keep the leading zeros while ensuring the value remains a number in the pivot table?

Kauan_Bohn · February 14

Hello, in case it helps someone, I found a way to keep the leading zeros while keeping the field as text.

I selected the cell properties and set the format to CEP Code (Brazilian Postal Code).

In this specific case, it worked for my issue and allowed me to proceed with the pivot table.

Thanks

MandeepGupta · February 3

https://community.oracle.com/products/oracleanalytics/discussion/24485/is-it-possible-to-consider-leading-zeros-from-an-xml-file-in-excel-model-bi-publisher

I haven't seen any solution for this yet i.e. to keep the formatting as number and preserving the leading zero's.

As per below MOS doc, it is currently the limitation:

BI PUBLISHER : Leading Zeroes Are Truncated When The Report Is Of Excel Format (Doc ID 760041.1)

Thanks.

Kauan_Bohn · February 3

Thank you for the quick response!

And is there any other solution to preserve the leading zeros using the text type?

Even with this, I encountered this limitation. The only solution I found was to concatenate a blank space in front.

MandeepGupta · February 17

https://community.oracle.com/products/oracleanalytics/discussion/comment/63827#Comment_63827

This is interesting. Thanks for sharing. Did you set it under Special format? I couldn't see CEP code as a value on my excel? If possible please share a screen shot.

Regards.