Trailing zeroes in OBI (Office apps)

When working with OBI (Oracle Business Intelligence), ability to export to many different formats is one of the most usable features. Idea to save data in PDF, Excel, Word, CSV and all without a single line of code, made this application famous. However, there might be some problems. In this topic I'd like to explain one problem which may arise some frustrations-problem with trailing zeroes when exporting to MS applications.

The problem

Let's suppose that we have a column with fixed number of decimal points (in this case 2 decimals):

Accented part is one that would shown as a problem-value with trailing zeroes.

When you want to export data from answer report to Excel, you do that through link placed at bottom of the page.

Result is classical Excel file. In this case after opening, monitored value looks like:

So , regardless, formatting in Answers report, as you see, trailing zeroes are lost!

Cause

You might think that problem is in OBI and it's functionality-but it is not! For proving this here are steps that shows that:

1. Open Notepad, and write 1,25000 (1.25000 if different Regional Setting)
2. Select that value and copy value in Windows clipboard
3. Open new Excel worksheet
4. Paste value from clipboard-you get 1,25 (1.25) zeroes are gone

The same may be tested if you export to PDF file (when BI Publisher is involved-no MS influence)-trailing zeroes are here!

The solution

The solution was found From HTML to Excel blog post. Translated to OBI syntax, it is performed in a way that you change CSS style of problematic column property:

Crucial part is
mso-number-format:"\@"
part which is written under "Use Custom CSS Style" option.
If you run export now, in Excel worksheet, zeroes are saved:

This workaround has been successfully tested on Office 2010 client and OBI 10.1.3.4 (Build 080726.1900) server side.

The End

But when you run the same Excel file on Excel 2003 or 2007 client, you'll notice problems in columns where decimal and integer values are mixed. In such a column integer values on mentioned Excel versions will be shown a date values in format "mon-yy" (i.e. Jan-11).

For that problem I do not have solution in this moment.

Cheers!