Export more than 65000 records
I have a report in SSRS 2008R2 that will generate more than 65000 rows when exported to Excel 2007, and from what I understand this version of SSRS exports in the XLS (2003) format thereby limiting the output 65K rows. Is there a way around it, I have been reading some information regarding using page brakes but I am not sure how to go about that, and if there are any better alternatives.
etech0
Can you export it to csv?
ASKER
Haven't tried that, so export it to CSV then import it into Excel, correct, will it maintain formatting?
If you can export to a .csv file, you can then launch Excel 2007 and open the file.
If the file needs to be opened using Excel 2003, then you can put in a VBA macro in the Excel file that will do the import and automatically add a new worksheet each time it hits 65,000 rows.
If the file needs to be opened using Excel 2003, then you can put in a VBA macro in the Excel file that will do the import and automatically add a new worksheet each time it hits 65,000 rows.
ASKER
I am using Excel 2007, I have been reading that I can group in SSRS telling it to create a page break every X rows =Ceiling(RowNumber(Nothing
What kind of formatting? Try exporting to csv, open it in excel, and see how it looks.
ASKER
Will do...
Rows fewer in number than the defined ceiling should cause no problems.
It may simply be easiest however to export to .csv and then open the file with Excel. The import wizard does provide for some formatting. If that doesn't suit your needs, then a little VBA macro should do the trick.
It may simply be easiest however to export to .csv and then open the file with Excel. The import wizard does provide for some formatting. If that doesn't suit your needs, then a little VBA macro should do the trick.
I have been reading that I can group in SSRS telling it to create a page break every X rows =Ceiling(RowNumber(Nothing
I'll explain how you can get that done. The first step is to add a new Parent Group on top of any Row Groups you've currently got. When creating the group, use that expression you mentioned in comment #a39555872
After the group is added you'll notice that an additional column appears on the left side of the existing columns. Delete it but keep the group (Delete columns only).
Now open the Group Properties of that new group. Activate the Between each instance of a group checkbox on the Page Breaks page. Switch to the Sorting page and remove the sort.
Run the report and you should see your data split up in pages of 55000 items. Each page will get a different sheet when exported to Excel.
... and what about when the report generates less than the defined page break rows?
You'll just get one page/sheet with all the records.
I'll explain how you can get that done. The first step is to add a new Parent Group on top of any Row Groups you've currently got. When creating the group, use that expression you mentioned in comment #a39555872
After the group is added you'll notice that an additional column appears on the left side of the existing columns. Delete it but keep the group (Delete columns only).
Now open the Group Properties of that new group. Activate the Between each instance of a group checkbox on the Page Breaks page. Switch to the Sorting page and remove the sort.
Run the report and you should see your data split up in pages of 55000 items. Each page will get a different sheet when exported to Excel.
... and what about when the report generates less than the defined page break rows?
You'll just get one page/sheet with all the records.
ASKER
The user who requested this report does not want page breaks because he feels it will be confusing to the people who ultimately receive it, as there are related rows that may get broken up and wind up on a different page, are there any 3rd party apps that can output the whole thing to excel, upgrading to SQL Server 2012 is not an option.
Have you tried exporting to CSV? What happens then?
ASKER
CSV wont work because it loses the formatting
are there any 3rd party apps that can output the whole thing to excel
I guess the Aspose.Cells extension could be an option (not free though!): Aspose.Cells for Reporting Services
I haven't used it myself so far but according to the description it should be able to do the trick:
I guess the Aspose.Cells extension could be an option (not free though!): Aspose.Cells for Reporting Services
I haven't used it myself so far but according to the description it should be able to do the trick:
Aspose.Cells for Reporting Services allows you to deliver native Excel reports in Reporting Services on Microsoft SQL Server 2000, 2005, 2008 and 2012, as well as in Microsoft Report Viewer 2005 and 2008. Unlike the native renderer for Excel based reports, Aspose.Cells for Reporting Services provides an extended feature set and allows RDL/RDLC reports to be converted to: Excel 97-2003 binary XLS, SpreadsheetML, the Excel 2007 XLSX and the Excel 2007 XLSM, CSV, HTML and TXT formats.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It was the only viable option