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

23rd Oct 2010

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.