Technical Articles
Webi “Header / Report Title” not getting exported to Excel spreadsheet – fix/workaround.
You would have landed in this page, while searching for why the hell – report title or heading is not getting exported to excel/csv/spreadsheet when using the export to feature in Webi, correct ?
I have done enough research on the same and thought of providing my feedback and workaround for the problem, so that it will be helpful to see the frustrated people who are pulling their hair out to get it fixed.
There may be many articles or queries in SDN on the same, but I atleast could not find a single page with a summary of the problem and the fix with steps.
Here you go..based on the real incident in my project.
I have got a frustrated user in my project, where he was not able to get the report title when the generated report is exported to excel. He needs to send that report to a set of business users, where the report title is very much useful which describes the month/year of data its referring to. This will perfectly work while exporting to PDF, however its impossible to get it in whatsoever option you try from the below combinations.
Choose whatsoever combination from the below and I bet you will never be able to get it as this is the feature of excel – its a list rendering tool as it handles the headers and footers differently, so don’t waste your time if you have landed here ..go right to the resolution below.
Reproducing the Issue
-
Create a report based on any source, using only the object <Year/Month/etc>.
-
Set the report in Layout mode so that the margins, header and footer are visible. (In Web Intelligence, use Structure mode)
-
Drag the report title into the header area.
-
Save the report as Excel – the report title cell is not visible in Excel.
Cause
- Not a limitation of Business Objects, but a feature (characteristic) of Excel.
- BusinessObjects and Adobe Acrobat are ” page layout rendering tools “, whereas Excel is a ” list rendering tool “.
- Headers and footers are handled differently in Excel: it is not possible to translate this type of format of BusinessObjects headers and footers into Excel
- With Adobe Acrobat, the visual page layout gets copied
- For detail information, please refer to following link: Exporting to Microsoft Excel
Resolution
- Place header and footer cells inside report page.
- If you have reports with many pages, click Format Cell > Alignment > Repeat on every page.
I am showing you the example of my report, where the report title is moved to the header below and HOLA !! we were able to get it in the “export to excel” option.
after the above fix, choose whichever option you choose from the 1st screenshot above and you will get it in the excel. Please see the below option after the report is exported to excel.
User was happy after the fix, and requested to do the fix for all the reports in PROD, which is very time consuming task as there are a lot of delivered controlled reports and it will be tedious to do the cosmetic change for all of the reports, promote it to Quality and Production.
We have come to a conclusion that if any changes are being done to any of the delivered Webi reports in Dev, the report title need to be moved to the body of those respective reports ;).
Hope this helps .. go ahead and do it. Don’t research any other sites/articles/SAP notes etc. No other fix for it, unfortunately 😀
Using webi to export to excel, so that further analysis or data manipulation can happen there is like using a microscope to hammer nails.
Why purchase one of the best analytics products on the market to use it as a simple export tool?
If user base is soo in love with Excel - why not use Analysis or Office instead ?
Mate, not all the requirements are to use Excel for data manipulation. There could be multiple scenarios where it need to be sent to the 3rd party vendors /external users /other cost centres /suppliers who don't have webi portal access etc.,..then the report need to be exported to Excel and sent on email.
Cheers.
Yes, printing is also a nice way to send reports to satellite offices.
I am giving a solution to the existing problem or issue. Not question about the origin of the problems. Take it easy. Chill.
I'm just trying to add a different point of view.
Sometime addressing a root of the problem, in this case "usage model" can yield a better solution in the long run.
Sorry,if you felt I was negative about your blog,it wasn't my intention.
It seems that Denis does not know much about how the departments are working,
and what challenges they have, with answering all the daily, ad-hoc business-questions
WebI is not a replacement for Excel but a great companion.
I know both and I also love both, with all of their features.
Everyone has a hammer and (usually) knows how to work with,but only a few people have
a microscope and know how to handle this.
Thank you!