This content has been marked as final. Show 22 replies
Please post the details of OBIEE version and the Excel version you are using.
Is the issue with one file or all files?
There are many issues with Excel AND OBIEE reported on MOS website (as you see below) so it would be more helpful if you could provide more details.
Downloading to Excel 2007 from OBIEE 10.1.3.4 The File you are Trying to Open is in a Different Format than Specified by the File Extension - Contains Features not Compatible [ID 980627.1]
Obiee 22.214.171.124 - Export To Excel Issue [ID 1333391.1]
OBIEE 11g: When you Export a Pivot Table to Excel, the Decimal Format is Lost [ID 1479859.1]
OBIEE 11g: When Exporting OBIEE Reports to Excel, Numeric Columns with Action Links Have a Trailing Space [ID 1363474.1]
I've run into this problem many times. I recommend exporting your report in a tab delimited file and opening that in Excel. If this still doesn't give you clean results try opening a new Excel file and importing the contents of the tab delimited file instead of opening it directly, which gives you more control on how the data is handled.
Why dont you give the custom format in the column formula then you will export the data what you have in the report.
column properties->style->custom CSS->give the format..
this is the default format mso-number-format:"\@"..you can use the custom format also.
The different CSS formats are available you can google it based on your requirement.
mark if helpful/correct..
how can a .css affect the value of the data into an excel file??
I think it is used for formatting xml or html files, not for checking the values of the cells in excel..But please tell me if I'm wrong.
Also, if I export a TAB separated values file, the pivot table is treated as a flat table,
while I don't want it to happen.
Thanks for any help!!
Also .css files cannot affect values. They are strictly about formatting.
Conceivably you could use a .css file to specify a font that switches letters/symbols around (an evil idea), but that would only be cosmetic and would only work on machines that had that font installed (I can't imagine why they would, but still...). Anything you exported would not use that font, and therefore would appear correctly.
Microsoft Excel is auto-formatting the VARCHAR2 fields as a numeric format, and stripping off the leading zeros when it is opened.So Excel automatically eliminates these leading spaces/zeros and you would have to provide Excel specific cell formatting to prevent this.
Now OBIEE cannot directly pass formatting changes to excel. So this change MSO-NUMBER-FORMAT:\@ -will convert all the numeric columns in your report to text format in the report. When you download to excel , MS Excel will treat this column as text format.
Other workaround would be to include a single quote, you can do this is logical layer by concatenating.
Hope this helps.! pls mark if it does.
Go to Answers–>Column-> Column propeties–>custom CSS style options(bottom of the window)–>check the custom css style –>Give the following date format
—-mso-number-format:”mm\/dd\/yyyy\ hh\:mm\:ss AM\/PM” or ” mso-number-format:\@” ( here is the list of formats : http://cosicimiento.blogspot.com/2008/11/styling-excel-cells-with-mso-number.html)
Change the format based on you requirement.
Hope this helps. Pls mark if it does.
Looks like nothing changed..
Is there anything I have to restart ?? Certainly not..
I did what you said step by step, but right above the form where I wrote mso-number-format:"\@"
it actually says FOR HTML ONLY.. that's probably why..
Any ideas on how to make this work??
Or, any ideas on other methods?
And thanks in advance.