exporting more tha 65k rows to excel

hi all,

I wanted to know how to export a report that returns more than 65,000 rows of data to MS Excel, it will fail because excel has a limit of 65K records per excel sheet.

What i want to know if there is some way to use multiple sheets (in the same excel file) to show more than 65K records

Eg we have 105K rows of data ...can we export 65K records in the first sheet, and the remaining 40K in the second sheet (of the same excel file)?

Appreciate the Help,

Siddharth

Hello Siddharth,

Using a Table, you can force an explicit page break after a certain number of rows. Because the Excel renderer creates a new worksheet for every explicit page break, you can use this technique to make sure no more than 65,536 rows are exported to a single sheet.

Create an outer table group using this group expression: =Int((RowNumber(Nothing)-1)/65000). Set Page break at end on the group.

Keep in mind that there isn't a one-to-one relationship between SSRS table rows and Excel rows unless the table is the only report item in the report body. So, if you have other report items in addition to your table you will have to also take that into account.

-Chris

|||

Hi Chris,

I knew page break created a new sheet in excel but didnt know how to do it. thanks a lot for thehelp its woking. appreciate it.

siddharth

sql

Exporting Data from Reporting Services into Multiple Excel Worksheets

Hi,

Please help me.

I need to export around 1 million records to excel. As we know that there is a limit of approx 65,000 rows in one worksheet so the exporting functionality is failing. One way is that I should be able to configure the Reporting Service in such a way so that once 65,000 records gets populated in the first worksheet, then the rest 35,000 records gets popultaed in the second worksheet.

I am not able to find out how this can be achieved in Reporting Services. Please help me in this.

Thanks in advance.

Regards

Raman

Hello Raman,

Take a look at this link, in the 'Page Breaks' section, it may help you.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

It requires that you set a group with a group expression on X number of rows, then you page break on the group.

Hope this helps.

Jarret

Export to Excel fails - ASPNET_WP.EXE consumes 60% RAM

I'm trying to export a report to Excel that contains 60,000 rows (but not
more than Excels 65,000 limit) and I get an "internal server error".
When I check the event log on the server it states that aspnet_wp.exe
terminated the report process because it had consumed more than 60% of the
servers availible RAM (1Gb total).
PDF and CSV work fine. Does anyone know anything about this'
--
Cheers
NickExporting to Excel is quite intensive on resources. The problem is that MS
is trying for very high fidelity on what the data looks like. For instance
if you have drill down it creates a spreadsheet with drilldown. For that
amount of data I suggest that you use CSV in ASCII format (the default is
Unicode). Excel doesn't recognize Unicode CSV and puts it all in one column.
With ASCII format Excel properly puts them in the correct columns. Another
tip, for your table in the report, click on each field of the detail and
make sure it has the name that you want on Export. The CSV export uses this
name. To get CSV in ASCII you need to use a jump to URL and include ASCII in
the deviceinfo (or something like that, doing this from memory from home).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nick Prudhoe" <NickPrudhoe@.discussions.microsoft.com> wrote in message
news:3F7F032F-9416-462B-89BC-F6C90E64FA5A@.microsoft.com...
> I'm trying to export a report to Excel that contains 60,000 rows (but not
> more than Excels 65,000 limit) and I get an "internal server error".
> When I check the event log on the server it states that aspnet_wp.exe
> terminated the report process because it had consumed more than 60% of the
> servers availible RAM (1Gb total).
> PDF and CSV work fine. Does anyone know anything about this'
> --
> Cheers
> Nick|||Thanks. I have already managed to get the same report to export to CSV. I'm
trying to prove reporting services out before my company goes down that
track. The performance on exports to Excel is disapointing, i would have
hoped it was better than PDF as it's an MS technology.
--
Cheers
Nick
"Bruce L-C [MVP]" wrote:
> Exporting to Excel is quite intensive on resources. The problem is that MS
> is trying for very high fidelity on what the data looks like. For instance
> if you have drill down it creates a spreadsheet with drilldown. For that
> amount of data I suggest that you use CSV in ASCII format (the default is
> Unicode). Excel doesn't recognize Unicode CSV and puts it all in one column.
> With ASCII format Excel properly puts them in the correct columns. Another
> tip, for your table in the report, click on each field of the detail and
> make sure it has the name that you want on Export. The CSV export uses this
> name. To get CSV in ASCII you need to use a jump to URL and include ASCII in
> the deviceinfo (or something like that, doing this from memory from home).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Nick Prudhoe" <NickPrudhoe@.discussions.microsoft.com> wrote in message
> news:3F7F032F-9416-462B-89BC-F6C90E64FA5A@.microsoft.com...
> > I'm trying to export a report to Excel that contains 60,000 rows (but not
> > more than Excels 65,000 limit) and I get an "internal server error".
> >
> > When I check the event log on the server it states that aspnet_wp.exe
> > terminated the report process because it had consumed more than 60% of the
> > servers availible RAM (1Gb total).
> >
> > PDF and CSV work fine. Does anyone know anything about this'
> > --
> > Cheers
> > Nick
>
>|||Yeah...one would think...although I have seen a significant performance
improvement in service pack 2.
Nick Prudhoe wrote:
> Thanks. I have already managed to get the same report to export to CSV. I'm
> trying to prove reporting services out before my company goes down that
> track. The performance on exports to Excel is disapointing, i would have
> hoped it was better than PDF as it's an MS technology.
>