Excel rendering - merged columns etc.

Hi guys,

i have a client who is dissatisfied with the merged columns and rows produced when SSRS renders to Excel, it prevents them from easily copying and pasting data into another workbook.

Is there going to be any work done in this area of the product in the near future? My understanding is that this 'issue' has been around for quite some time, and shortly after the SSRS2005 RTM release the team were going to look at tidying up the Excel rendering. Can anyone from MS make an official comment on this?

Thanks,

sluggy

Hello,

This is a well known issue that people often encounter when rendering to Excel. First, I suggest reading this blog post which describes the problem and suggests several ways to reduce cell merging:

http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

There will be some improvement in this area in the next major release of Reporting Services. We have made some refinements to the renderer to prevent any adverse merging that is a result of internal rounding issues. This means that overall you should see a reduction in the number of merged cells, but you will still need to proactively make sure that your report items align properly as described in the referenced blog article.

Best regards,
Chris

|||

Chris Baldwin - MSFT wrote:

There will be some improvement in this area in the next major release of Reporting Services. We have made some refinements to the renderer to prevent any adverse merging that is a result of internal rounding issues.

Thanks Chris, that was the answer i was after, i can now tell the customer with some certainty that there will be an improvement in the future. In my particular case, i don't care about the header of the reports, i just care about the merged cells in the data area; i have seen cases where one table column spans three excel columns (merged into one), but when those excel columns are unmerged the data in the first few rows could be in colunmn 3 and for the rest of the rows it will be in column 2, with column 1 containing the column title - merged cells would be easier to live with if the data (and title) was in consistent columns once unmerged.

Thanks for you answer :)

sluggy

Excel rendering - Merged cells issue

Hi,
My rendered Excel reports have lot of merged cells (because of the layout I
made in the designer). Therefore when I open it, the excel takes lot of
minutes to open. So the question is: what can I do in order to avoid having
merged cells in the rendered excel file.
TIA,
Gastón.-
PS: I've opened the excel file, unmerged all cells, save, reopen it, and the
problem disapear.Are you using table or matrix?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gastón Pírez" <gaspirez@.adinet.com.uy> wrote in message
news:O28R3kekFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My rendered Excel reports have lot of merged cells (because of the layout
> I made in the designer). Therefore when I open it, the excel takes lot of
> minutes to open. So the question is: what can I do in order to avoid
> having merged cells in the rendered excel file.
> TIA,
> Gastón.-
> PS: I've opened the excel file, unmerged all cells, save, reopen it, and
> the problem disapear.
>
>|||I'm using table.
Thx,
Gastón.-
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23DTU3CfkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Are you using table or matrix?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Gastón Pírez" <gaspirez@.adinet.com.uy> wrote in message
> news:O28R3kekFHA.1948@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> My rendered Excel reports have lot of merged cells (because of the layout
>> I made in the designer). Therefore when I open it, the excel takes lot of
>> minutes to open. So the question is: what can I do in order to avoid
>> having merged cells in the rendered excel file.
>> TIA,
>> Gastón.-
>> PS: I've opened the excel file, unmerged all cells, save, reopen it, and
>> the problem disapear.
>>
>|||OK, here is what I did. Do not have any textboxes above your table. Instead,
add additional header rows. You can merge cells together, put in text and
center so usually I could still get it where it looked similar. For some
reason even though you are merging cells it does it differently when
exporting to Excel. After doing this you will be able to select the data
region and sort the data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gastón Pírez" <paytrue@.newsgroup.nospam> wrote in message
news:uyDMNJfkFHA.1464@.TK2MSFTNGP14.phx.gbl...
> I'm using table.
> Thx,
> Gastón.-
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23DTU3CfkFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Are you using table or matrix?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Gastón Pírez" <gaspirez@.adinet.com.uy> wrote in message
>> news:O28R3kekFHA.1948@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> My rendered Excel reports have lot of merged cells (because of the
>> layout I made in the designer). Therefore when I open it, the excel
>> takes lot of minutes to open. So the question is: what can I do in order
>> to avoid having merged cells in the rendered excel file.
>> TIA,
>> Gastón.-
>> PS: I've opened the excel file, unmerged all cells, save, reopen it, and
>> the problem disapear.
>>
>>
>

Excel Merged Cells

Hi

I'm trying to eliminate merged cells when exporting a report to Excel. My problem being my report textbox above my table.

I've searched and found that making the textbox the same width as the first column eliminates the merging. Perfect, it does. But when the report is viewed in the report viewer, the textbox can only expand vertically. So the title looks terrible because column one is not wide.

I've read that expand horizontally is not an option I don't seem to be able to tell it not to output that text box, which would be an option if there is no other answer I can't have the text box the size I want it, due it creating merged cells I don't want to export it as a CSV

Are there any other options available or am missing something?

Cheers

You can try making the text box the same size as the first and second column or first second and third column if you need your textbox to be larger to accomodate its contents. It's not necessarily the size as much as that it aligns perfectly with one of the columns. I have had a really bad time with the merging of cells also. Sometimes I have to just handle it on the excel side by deleting the title out and then unmerging the merged columns.|||

Thanks for the reply KimberlyL. I tried this and it didn't work. I've tried it again today, making my title text box the width of 5 columns in my table and it still doesn't work, I get merged cells. If I make it the width of 4 columns then I don't get merged cells. So I've reduced the font size so it fits.

It's very strange, I wish I understood why, but at least it works for now

Cheers

|||

If you are exporting to Excel you can't really get rid of the cell merging, not unless you want to write your own renderer. I have read that the RS team is going to look at this problem sometime in the future.

I also had a customer who complained about this, he was trying to copy report values from one spreadsheet to another. In the end i just advised him on the quickest way to unmerge cells: press Ctrl+a, ctrl+1, select the Alignment tab, and deselect the merge cells checkbox. This has kept him quiet

|||

That is what customers had been doing with this report too. But it's not ideal is it! I've also had to remove an image that was placed at the top right of the page header as alligning that up with columns, or anything else I can think of, still adds extra cells and merged cells.

But it does what I want it to do for now. I can see me revisiting this, I just hope the problem has been looked at before I need to!

|||I remember seeing on one of the blogs that sometimes no matter what you do that you can still end up with merged cells. Something about the internal math and rounding that the renderer does. Yes it is very frustrating. I hope that this is fixed in a future release.