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.
skull52IT director Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

etech0Commented:
Can you export it to csv?
0
skull52IT director Author Commented:
Haven't tried that, so export it to CSV then import it into Excel, correct, will it maintain formatting?
0
primeConstantCommented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

skull52IT director Author Commented:
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)/55000) But I am not sure how to do that and what about when the report generates less than the defined page break rows?
0
etech0Commented:
What kind of formatting? Try exporting to csv, open it in excel, and see how it looks.
0
skull52IT director Author Commented:
Will do...
0
primeConstantCommented:
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.
0
ValentinoVBI ConsultantCommented:
I have been reading that I can group in SSRS telling it to create a page break every X rows   =Ceiling(RowNumber(Nothing)/55000) But I am not sure how to do that ...

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.
0
skull52IT director Author Commented:
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.
0
etech0Commented:
Have you tried exporting to CSV? What happens then?
0
skull52IT director Author Commented:
CSV wont work because it loses the formatting
0
ValentinoVBI ConsultantCommented:
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:

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.
0
skull52IT director Author Commented:
I went with a third party option Office Writer by Software Artisans, worked well.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52IT director Author Commented:
It was the only viable option
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.