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/data dump to flat file is too slow

I am trying to export a table with ~ 10 Million rows to a flat file and it is taking for ever with SQL2005 export functionality. I have tried creating an SSIS package with a flat-file destination and the results are the same. In each case it does the operation in chunks of about 9900+ rows, and each chunk takes ~1-2 minutes which sounds unreasonable.

I tried bcp, and it fails after a few thousand rows. I tried moving the data to SQL2000 first then to flat file from SQL2K, but the move from SQL2005->SQL2000 was going at the same rate as above.

So, the bottleneck seems to be data going out of SQL2005 no matter what the destination is. I'm wondering if there is some setting that Iam missing that would make this run in a reasonable amount of time?

Never mind. I was outputting it to a share, and that is the reason. When I modified it to do a bcp on the local drive on that server, it was flying like it is supposed to. I'll deal with copying it across the network later. Thanks.