How to avoid cell merging when exporting to Excel in SSRS 2008

Lets be completely direct here. Cell merging in Sql Server Reporting services after exporting to Excel, is a common nightmare.

It happens because the engine that transforms the report tries to do so on a presentation basis.

I have been developing reports in SSRS for a few years now, and here are the best ways around the issue that I have found:

1. Don’t use standalone textboxes for titles, or any non-data elements.
Rather than fiddle with these for hours trying to get them to line up, just insert another row or two as headers above your data driven report element (e.g. table). You can then play with the presentation of the cells to make it look like it isn’t part of the same table. This can be done by colouring certain borders white to give the impression that there is nothing there.

2. Use points and not centimetres when specifying sizes.
The renderer converts all measurements into points anyway, so converting from centimetres can often lead to rounding errors. This is why you still get cell merging sometimes when you have two table opposite each other, with exactly the same sizes. I appreciate that this can be a bit of a hassle, especially if you already have a report that already specifies everything in centimetres. I’d recommend using a hefty bit of search and replacing in the source rdl fie.

I use both techniques in almost all reports that I develop. It keeps the clients happy.

Advertisements

2 comments

  1. Thanks for pointing me in the right direction. I avoided this by aligning ends of textboxes to the column ends.

  2. Reblogged this on Kirk's Place and commented:

    Have you had problems with merged cells in excel exports from Reporting Services reports? I have so I did some hunting around and found some information that looks like its solved my problem.

    Add this post and the information at the following MSDN blog and you should have your problems with merged cells in excel exports from Reporting Services reports nailed.

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s