CODEWITHSUNDEEP
sql-serversql-server-2005reporting-servicesreportingservices-2005
zzawaideh
zzawaideh

Reputation: 2011

Reporting Services (SSRS 2005) matrix column group shifting right when exported to xls

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?

Upvotes: 0

Views: 2364

Answers (2)

zzawaideh
zzawaideh

Reputation: 2011

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.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

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.

Upvotes: 1

Related Questions