I have a SSRS report that works fine but has an issue exporting to Excel or CSV formats.
I tried exporting to excel but errors out saying it has more than 256 columns.
So I was hoping I could just export it to CSV format. But with CSV I noticed that it adds up unwanted 'textbox1', textbox2 etc and also does not display the header that I actually added. Instead it would display the actual field name as header. I figured I could edit the individual properties to show Header Names. But the textboxes in the exported sheet is still an issue.
On the other hand I was trying if I could export it to excel but limit 100 columns per sheet or rest of the columns after 256 to next sheet that would be great. I saw few posts on google breaking by group. But in my case I do not have columns to be grouped. Only need to break first 100 columns to sheet1 and next 100 to sheet2 or the 256 columns to sheet1 and the rest to next sheet. No luck in both ways. Could you please help with this?
Error: "Excel Rendering Extension: Number of columns exceeds the maximum possible columns per sheet in this format; Columns Requested: 264, Max Columns: 256"
This is a very common issue when you work with SSRS 2008 R2. If your reports have columns more than 256, then it doesn't export to Excel.
Try to understand this technically. Technicality is, SSRS reports by default install 2003 office component on Report server. When your report give a call to export data into excel, then report server internally give a call to office component. And if you will see, then you will find that in office 2003, you have maximum of 256 columns in a sheet. So in any case, you cannot export more than that using your existing infrastructure.
Options:
SSRS 2012 or SSRS 2014. This will also update your office component to 2007 or 2010 where you can export up to 16,384 columns.break-down your reports such that it never exceeds to 256 columns. PDF. But when you do so, then it disturbs the UI. So I don't see this as a very feasible solution.