Export to excel in multiple sheets.

Question

Export to excel in multiple sheets.

dity_29

SSC Veteran

Points: 299
More actions
July 22, 2008 at 3:22 am

#195872

Hi,
I'm using a table in the layout and want to export the report in excel format. Because of large amount of data, it splits into 2 sheets of an excel. My problem is that i'm not able to see the table headers in the second sheet where as they are present in the first sheet.
Please tell me how to get the table headers in the second sheet as well.
Thanks,
Deepti

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 1

i assume you mean that the report exceeds excel's row limit and is split into 2 sheets when you export.

if so, you could add second table and have the first table's query report the first 32K rows using the TOP clause (or row_number()) and the second table's query report the remaining rows. each table in a report will be output as a different sheet. if you have that much data, you could segregate your data based on meaningful data value (such as year, category, etc.) and drop multiple tables to only include that data value.

dity_29 SSC Veteran Points: 299 More actions · Answer 2

Hi,

I could have tried this but the main problem in this case is that i'm calling a SP in this report which is actually populating data and i cant change this SP in database.

Is there any other way of doing this??

Thanks,

Deepti

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

the stored procedure must return some data item by which you can partition.

if you can group by some data item, then adding a page break before/after a group will produce multiple sheets in excel also.

dity_29 SSC Veteran Points: 299 More actions · Answer 4

Hi,

Thanks for the solution as it works but the problem with this is, if there is no data being retrieved by the SP then even the group headers dont reflect in the excel sheet. The sheet is totally blank. I want atleast the column names to appear. So i tried going for table headers as they appear in the sheet even if there is no data but then there is a problem with table headers as they dont repeat wen the data is split into 2 sheets. They just reflect in the first sheet. I tried checking that option of repeat table headers in each page but thats not working.

Can u help me a bit more on this?

Thanks,

Deepti

Mike Nowill SSCrazy Points: 2699 More actions · Answer 5

You can also do a page break in your report that will force then next section onto a new sheet in excel. If you are grouping data for example, you can have RS insert a page break before or after that group. You, probably (I haven't done this but see no reason why it wouldn't work.) force a page break after x rows, as well.

The other thought is that you could export the file as a CSV and open it in Excel and this get you around the row limit of 65K in 2003 or 1M in 2007.

-Mike

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Sorry I am not even a novice with reporting services, and this is a manual solution but could provide what you want.

In the second sheet of the Excel workbook if the first row has data click on the first row and insert a new row, it becomes the first row. Then in that new row in the first column type =Sheet1!A1 followed by a carriage return, in the second column type = Sheet1!B1 followed by a carriage return, 3rd colum =Sheet1!C1 followed by a carriage return and so on and so forth. The forumla will copy the column heading from sheet 1 to sheet 2. A slow manual method but it works.

Sorry I could not be of more help.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

lavanyachimata2000 Newbie Points: 7 More actions · Answer 7

Hi Deepthi,

Can you post the sql query or logic to upload the large data in to multiple excel sheeets.