How can I stop excel from merging my SSRS report columns?

0
votes

I have an SSRS report that the end user is exporting to excel. When exported, this report seems to merge a couple columns and add a "blank" column within a column group in the report.

Here is the column group in SSRS: enter image description here

Which for some reason becomes (Notice [2015 Expenditure] is merged into columns C and D in excel. Also column F [Expenditure] shouldn't even be there! it is not associated with a year, which the group is grouped by, and there are definitely no values in this "column": enter image description here

For the extra column, I've considered that has to do with how I've set up the column group - but I can't really figure anything else out to change with it. It is a pretty basic grouping in SSRS.

2
excelreporting-servicesssrs-2008
This might help you since it looks like you have some sort of image and other things going in the header area. stackoverflow.com/questions/37310893/… – R. Richards
It is preserving the alignment of all the objects when you export it. The edges of all your other elements outside the table should align along the column borders. – StevenWhite

2 Answers

0
votes

You need to remove all report headers, footers, labels and images etc. from your report so that the ONLY data displayed is the grid/matrix and those column headings.

This will guarantee that your cells will be displayed correctly.

Once you have done that you can experiment with adding back the Report Headings and any other formatting step by step.

In my experience the less formatting the more chance you have of getting a usable spreadsheet.

0
votes

I would just get the user to export to XML and then open in Excel.

Just to add onto what BIDeveloper has said, make sure the location of the grid/matrix is set to 0,0 coordinates in the properties window. This guarantees no columns to the left will appear.