I'm having troubles exporting an excel file from an oracle BIEE analysis.
Once the file is saved, the data have nothing to do with what I see in the analysis.
Could it be an issue due to the combination of commas, percent symbol and dots
that have a different meaning in BIEE and excel?
Is there a way to change some settings? Because so far the only thing I did is to hit export->excel
Thanks in advance for your help. Have a good one.
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]
We have the same issue with all files.
Oracle Business Intelligence Product Version 126.96.36.199.0 (Build 120104.0800 64-bit)
I think that OBIEE saves the file on the computer in a format that does not depend on what version of excel you have in stalled, isn't it?
That's what I thought, but I don't know how to fix it.
I need decimals and percent symbols in my report, I cannot just get rid of them,
so do you know how to find a way to format those values in an exportable way?
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!!
The export specifically exports the underlying data. The pivot view you show in your analysis would have to be recreated in Excel (or whatever other software you might be using) if that's what you need.
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.
What changes are you expecting .? Is the issue with downloading to excel and having the same format in report to excel download resolved .?
Could you let us know what type of column and issue with data format on the excel you think is getting mismatched with an example. ?