Hidden column after export to excel when table has less than 4 columns (SSRS)

Question

I faced following problem with exporting ssrs report to excel. When I create a table having 4 columns and then exporting it to excel I get the proper layout:

But when I remove at least one of the column in ssrs designer (when table consists of less than 4 columns), after exporting it to excel, one column is being hidden:

When I manually changed the width (by typing width in properties window) to at least 3 cm, after exporting it to excel the additional column disapeared (even if the table has less than 4 columns).

Does anyone know why this happens? Any help will be appreciated.

Answer 1

Late to the game, as usual. But I played around with this for a while, and it looks to me like it's just going to end up being fairly random. Sometimes I can get them to go away, sometimes I can't. I don't always have the ability to change things to nice round numbers that represent an even number of points. And even when I do, it still doesn't always work. But I did notice this. If you zoom in closely, you can see that the lines are just slightly misaligned. Believe me when I tell you that mathematically that shouldn't be happening. But I wondered if that might help explain the problem.

Answer 2

Easy solution: Work in POINTS, not Inches or Centimeters. Excel renders in points, so this prevents all roundoff in positioning. It is that easy.

Answer 3

After struggling with this for a few days this blog post helped me out:

How to eliminate merged and hidden Cells issue from Excel Export of SSRS report - MSDN

The problem seems to be that pixels and centimetres (or inches) don't line up nicely. When you drag the columns around they get non-exact approximations of the value. When you put a nice round number (even like 3.25cm) they seem to play nicer. You'd probably want to use pixels or points (what Excel uses to store cell size) to prevent the last column compressing.

I hope this helped a bit...

Answer 4

It's an issue with the header.

To avoid this all together insert a row in your tablix (above everything else) and set the "RepeatOnNewPage" tablix property to 'True' (Advanced Mode >> select the static row groups you need to repeat >> RepeatOnNewPage).

This approach does the same thing as a header and you won't have to deal with those pesky extra columns any longer!

Answer 5

I find that if you have headings in your reports above the actual tablix, you will have to extend the width of the heading to match the width of the tablix (you should see the blue lines showing the alignments) and ensure that the headings also align with where the tablix begins. Basically, just make sure that everything is aligned. Hope this helps.

Answer 6

It works fine for me ... what ver of excel do you use ? Can you post here rdl file or code from file ? Are you using report builder or Visual Studio ? - Do you have any more objects on the report or just that table ? D.

Answer 7

This is common problem in SSRS - the way how I removing this problem is to use rectangle's . Insert rectangle then CUT your table/matrix PASTE inside rectangle - make sure there is not empty spaces. If you using more than one table/matrix , then insert rectangle inside rectangle etc. ( my record is 14-th depth ) - this is VERY annoying but I know only this way. - ALSO - fit everything to left side corner

Answer 8

Try rectangle in to rectangle. If this do not work - insert a list and insert your object in to list. This should help - don't know why is that - but sometimes it take a lot of attempts and scenarios to work this out for one report... just keep trying with rectangles and lists - this is what I always do.