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.
- SAP Managed Tags:
32 Comments
