I have an SSRS matrix report that has 4 column groups with the last group set to hide some of its columns if they contain no data.
The report works perfectly, however when exported to excel an empty column header appears and all the headers shift right. It can happen multiple times on the same sheet.
For examples instead of seeing something like
v1 | v2 | v1 | v2 | v1 | v2
on the last column group. It shows as
v1 | v2 | __ | v1 | v2 | __ | v1
did anyone ever see anything like this before?
The stock Excel export is WYSIWYG. In order to accommodate the report layout, cells in Excel get merged and positioned accordingly. Excel won't allow sorting until merged columns are removed.
You will need to create a custom rendering extension in the RSReportServer.config - Copy the Excel one, and update it. You'll need to provided the override name so it will show in the export list properly. Here is a list of the Excel Rendering options - SimplePageHeaders should be set to false because the footer is never exported to Excel. If neither header or footer export to excel, then only the actual data will be presented. You should be using header and footer sections, if you aren't already.
Ok. I figured it out. I was hiding columns that had only zeros in them and somehow excel didn't like that. I switched the code so that it filters columns that are zero inthe filter tab of the group and that resolved the issue.