The 2-for-1 Special in Excel

It inevitably happens when building SQL Server Reporting Services reports that someone will have desire to export the beautifully created report out of SSRS and into some other format.  And sometimes that format just happens to be Excel.

*Presto Change-O*
And quite suddenly the beauty of the report is destroyed by the earth-shattering disease of merged columns in the Excel spreadsheet.  Now this isn’t really a disease, and it’s not a bug, because as the report designer… all of those merged cells are my own fault.  I designed the report to export in that hideous manner.

Now there isn’t <add tons of column=true> property that is set in SSRS to make this happen in the report.  If simply happens because when a report is rendered from SSRS to Excel the left and right edges of every object in the report must line up with the left and right edge of a column in Excel.

In probably one of the worst cases of "merged columns" I worked on, the report I got had three matrices on it.  The first three columns of the matrix had identical width values.  But for some reason the data cell width for each of the matrices varied by 0.01".  Just enough of a variance that when the report was rendered in Excel it managed to cover over 100 columns in less that a page worth of space.

Chris Baldwin covered this topic a few days ago and has some good suggestions on getting around them.