Introduction

SAP BusinessObjects Web Intelligence (WebI) allows its documents content to be exported as Microsoft Excel files, either in XLS or XLSX format. This functionality exports each report of a document as an individual tab into an Excel file.

Important Differences between WebI and Excel

Before exporting WebI data to Excel, it is important to understand some important differences between WebI and Excel.

In WebI, each table in a report is truly independent. Tables are distinct objects, each of them with its own format, number of columns and rows. They can be optionally related to each other in terms of relative position or filtering (element linking), but this is as far as it goes in terms of dependency.

In Excel, there is in fact only one large table in each spreadsheet (tab). Users can group their data in distinct areas in this table but these areas are not distinct and they cannot, for instance, have the same size and a different number of rows or columns…

What this major difference means is that when exporting a report with several tables, vertically aligned and of different widths and number of columns, WebI will have to create additional columns in order to match the vertical cells alignment in Excel and then merge the cells of the larger columns:

A similar operation occurs when the WebI report contains several horizontally aligned tables and these tables have rows of different heights. In that situation and for the same reason, WebI will need to create additional rows to match the horizontal cells alignment in Excel and merge the cells of the larger rows.

Exporting with Focus on Format or on Data?

There are two mutually exclusive options when exporting WebI data to Excel: either the export is done with a focus on the format or the focus is on the data.

The difference between these two options is that when focus is on the format, WebI will add as many columns and rows as necessary, to best match the format of the source WebI report.

On the opposite, when focus is on the data, WebI will prevent as much as possible the addition of extra rows or columns. Typically, small column or row size differences between distinct tables will be ignored. This second option is called “Prioritize easy data processing in Excel” and this is the option to choose when exporting WebI data for further processing.

Best Practice Rules when Exporting Data for Further Processing

If the goal of exporting WebI reports to Excel is to further process the extracted data, then by adopting the following rules, WebI report designers will make sure that they always get the same number of columns and rows in their Excel spreadsheets:

Rule #1: One table per WebI Report!

This is the best way to export data to Excel for further processing. With only one table per report, the export to Excel will result in a single table per spreadsheet with a one-to-one cell matching between WebI and Excel. No additional columns, no additional rows…

Rule #2: No vertical alignment of tables in WebI, same rows height

If several tables need to share the same WebI report, then it is better to align them side-by-side rather than on top of each other. With horizontally aligned tables, WebI will not need to create additional columns and merge cells in Excel.

Further, if the side-by-side tables have identical rows height, then WebI will not need either to create additional rows and merge cells in Excel.

To make sure the table’s rows keep the same heights after a data refresh, the report designer should not use the “auto fit” parameter. Instead, all rows should have a fixed height value.

Rule #3: If you really need to vertically align tables, then they must have identical columns width

If despite the above rules, multiple tables are vertically aligned in the same WebI report, then these tables should have columns of equal width. This will prevent WebI from creating additional columns and merge cells in Excel.

Again, to make sure the table’s columns keep the same width after a data refresh, the report designer should not use the “auto fit” parameter. Instead, all columns should have a fixed width value.

Rule #4: No overlap of report elements!

This is by far the worst situation…

WebI will move apart each element, in order to try to fit them in the Excel spreadsheet. But then, each data refresh will potentially add new values, both horizontally and vertically, thus leading to the creation of additional rows and columns in the exported Excel data.

WebI cannot guarantee a stable Excel format output in that case.

Additional Notes

  • WebI report headers and footers are not exported to Excel.
  • WebI free cells do not generate additional rows or columns. They make use of whatever space is available in the Excel spreadsheet.
To report this post you need to login first.

27 Comments

You must be Logged on to comment or reply to a post.

    1. Pascal GAULIN Post author

      Thanks 🙂

      No, there is no difference besides the client user interfaces, since the export is implemented in WebI server.

      Pascal.

      (0) 
  1. Tanya G.

    Hi Pascal.

    Very informative. I still have an issue in when Exporting from WEBI(4.2) to excel. When I export or create an instance the Report that is generated or exported in Excel has a blank column.

    To be precise the exported report has a first column as blank. I would appreciate any suggestions.

    Thanks

    (0) 
    1. Dan F

      Have you aligned the table or chart hard left i.e. align>relative position and set it to be 0 from the Left of Report?

      (0) 
        1. Pascal GAULIN Post author

          Hi Tania,

          There must be something in the report which forces the addition of a blank column because when I try on my side (in version 4.2) with a simple vertical table, I don’t get this extra column:

          Export.jpg

          Regards,

          Pascal.

          (0) 
            1. Mark Prosser

              Tanya, I’m trying to emulate this in 4.1 SP3 – is this a simple report with a single section? Or is it wider and affects all reports?

              (0) 
    2. Mark Prosser

      Tanya,

      While not directly relevant to you, my comments may help the SAP guys – this last happened in 6.5.1 – we installed a service pack that introduced a “feature” whereby BO exported as Excel and even though the report was aligned 0,0 the Excel sheet started at B2. I’m only mentioning this because there *might* be someone around still at SAP who remembers this bug and how it was fixed (c. 2005)

      Sorry it’s not of direct help but it might be in future!

      Regards,

      Mark

      (1) 
  2. Mark Prosser

    Pascal, what are the rules governing date field exports? As in, does the data have to be in a certain format for it to retain that format in Excel (indeed does it apply to just xls or just xlsx exporting)?

    Places I’ve worked prefer different date formats, including 2016-01-13, 13-JAN-2016, 13/1/16 and so on; as such, I was wondering if there were any “rules” that we needed to follow and what expectations need to be managed when saving as Excel.

    Thanks,

    Mark

    (0) 
    1. Pascal GAULIN Post author

      Hi Mark,

      I believe this depends on the locale of the computer where Microsoft Excel is being used.

      Typically: my testing environment is based on the US locale and, when I type in CurrentDate() into a WebI report, I get a date in the US format.

      Now, when I export this report to Excel on my computer (I’m located in France), the same date gets converted into the French format.

      Regards,

      Pascal.

      (0) 
        1. Mark Prosser

          I see it now. right click in the section header and choose format section. Make sure it starts 0 from the left margin.

          Also right click on the edge of your table and choose format table and check how that is positioned within the layout tab. It will probably say 0.0 from the Left Edge of Section.

          (0) 
          1. Tanya G.

            Hello Mark

            I tried formatting Table before posting the issue  and yes the report is wide and not just on one report it appears in every report I generate.

            (0) 
  3. Tanya G.

    Hello Pascal

    Thanks I see that you do not have the column I have uploaded my exported report. Honestly I am just curious so as to why I would get this.

    (0) 

Leave a Reply