1 2 Previous Next 22 Replies Latest reply on Mar 25, 2013 4:09 PM by Turbokat

    oracle BIEE export to excel

      Hello everyone.

      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.

        • 1. Re: oracle BIEE export to excel
          Hussein Sawwan-Oracle
          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 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 - 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]

          • 2. Re: oracle BIEE export to excel
            Sasi  Nagireddy
            Could it be an issue due to the combination of commas, percent symbol and dots

            What does it mean? Is that the report contains these all !!!!

            • 3. Re: oracle BIEE export to excel
              We have the same issue with all files.

              Oracle Business Intelligence Product Version (Build 120104.0800 64-bit)
              Excel 2003

              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?

              • 4. Re: oracle BIEE export to excel
                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?

                Many thanks
                • 5. Re: oracle BIEE export to excel
                  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.

                  • 6. Re: oracle BIEE export to excel
                    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..

                    • 7. Re: oracle BIEE export to excel
                      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!!
                      • 8. Re: oracle BIEE export to excel
                        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.
                        • 9. Re: oracle BIEE export to excel
                          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.
                          • 10. Re: oracle BIEE export to excel

                            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.

                            • 11. Re: oracle BIEE export to excel
                              sounds perfect.. can you write a little example?

                              Sorry I'm new and in a hurry..

                              Many thanks :)
                              • 12. Re: oracle BIEE export to excel

                                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.

                                • 13. Re: oracle BIEE export to excel
                                  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.
                                  • 14. Re: oracle BIEE export to excel
                                    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. ?

                                    1 2 Previous Next