Export to Excel gives Merged Cells

building reports webintelligence
1

I was typing a question to ask why, when only -certain- data is pulled, would a BO report merge cells when exporting to excel. While doing that, I found the answer and thought I would post anyways…

My report needs to export to excel in a format where no merged cells should exist in the excel document.

I had a few column headers in my report set to “Autofit Width” = Yes. This was causing BO to force excel to merge the cells together and throw off my report. Changing this to No fixed the issue immediately.

I couldn’t find anything when I searched the boards that would address this, so maybe this will serve to help someone else that wanders along.

bss (BOB member since 2009-09-10)

2

Thanks for the tip. Just ran into the same issue after an upgrade between r2 and r3.

J.C. :us: (BOB member since 2010-09-21)

3

Glad it helped!

bss (BOB member since 2009-09-10)

4

much appreciated!

thanabhavan (BOB member since 2010-10-07)

5

I’m getting this in 6.5, but can’t see where to switch it off.

wombleuk (BOB member since 2010-09-08)

6

You need to define a column width for each column.

These are done in various ways, simplest being on the report itself by changing it where the mouse pointer changes shape at the column border.

7

Thanks for the quick reply.
I haven’t defined widths for any column, but only two have been merged.
I have 2 date fields, the title of the one being merged was longer than the field width, so I extended the width so the whole tiltle could be seen.
It still gets merged, the other one deosn’t.
I just can’t see what the trigger is for merging a field.

wombleuk (BOB member since 2010-09-08)

8

The trigger for fields being merged is where they overlap.

Say you have 4 columns in a report and a free-standing cell that goes from the middle of column 1 to the middle of column 3.

In Excel you will get the following columns:
A - the first part of column 1
B - the second part of column 1 and the start of free standing cell
C - the whole of column 2 and the middle of fsc
D - the first part of column 3 and the end of fsc
E - the second part of column 3
F - the whole of column 4

Therefore columns 1 and 3 will look merged - you have to consider that BO is doing its best to align the columns - you need to do the same in the report and consider all start and finish points for both columns and free-standing cells.

Regards,
Mark

9

The columns weren’t overlapping as far as I know; I just selected them and copied them into the report.
I thought it looked as though it does it where data fields are longer than the column width, but that doesn’t appear to be the case.
I keep resizing columns and different ones get merged. Still can’t work out why.

wombleuk (BOB member since 2010-09-08)

10

Thinking back, 6.5 could be a bit fiddly when saving as Excel.

Do the reports have to go as Excel or could they be PDF?

11

No, they need to manipulate the data.
That’s what raised the issue, they want to sort it.
I can’t see what makes BO choose one column over another.

Thanks for your help on this.

wombleuk (BOB member since 2010-09-08)

12

Given that it’s going over to Excel, expand your column widths and make sure that they are lined up. Move the report title up in to the page header so that it doesn’t export and merge cells for you.

13

Have you ever tried flipping the option under your Preferences? There are 2 Save as Excel options - data vs presentation, presentation is the default. If you go to the option for data processing, a lot of these merge issues go away (although the layout may suffer a little).

Just a thought - sometimes that’s an easy fix.

Brent

bdouglas :switzerland: (BOB member since 2002-08-29)

14

Mark, moving the report title up into the header seems to have done the trick thanks.

Brent, thanks, but I don’t think that’s a 6.5 option.

Cheers,
Graham

wombleuk (BOB member since 2010-09-08)

15

Wanted to post because this thread helped me.
Thank you!

keywords I used:
too many columns excel business objects

BoBn00b :puerto_rico: (BOB member since 2012-08-31)

16

We had this problem when saving to Excel - our solution was to take the (major) offending report tabs and make sure all the columns were set to the same width.

Problem mostly solved - we couldn’t do anything about the tabs with the pretty tables but the massive source data tabs now don’t have 2 or 3 times the number of columns they should…

Maddye :uk: (BOB member since 2009-01-09)

17

Hi Folks -

I’m not sure whether this is thanks to FP2 for XI3.1 SP6, but it looks like Webi has now changed to Not merging cells when exporting to Excel, and instead showing each and every column, no matter how small. It doesn’t appear that the preference change “priority for saving to MS Excel” makes a difference either.

This is particularly an issue for reports that have been around a long time (often converted from Deski) with a variety of free-floating cells, etc. Other than fixing the report (which of course is the best idea, except that there are hundreds of variations), has anyone found a way to get to a cleaner export in Excel?

Thanks,
Amy

Amy Miller :us: (BOB member since 2002-06-07)

18

Was facing the same issue of cells getting merged when exported to Excel.
The free standing cell not aligned with the table column-width was the reason.
Thank you Mark P… :slight_smile:

mehak83 (BOB member since 2011-06-28)

19

hello everybody,

we use a Java tool, which uses the built-in web-service functions to schedule the generation and creation of the report.

unfortunately the problem with the merged cells is present.

is there a possibility to use a API-function to save the report in a clean way?is there an Option to use the idea of “data vs presentation” in a function?

thanks for you Input!

bobnoob (BOB member since 2010-09-16)

20

What would you expect the API function to do?

You have to go into the report and make sure that cell edges are aligned.