Exporting reports into excel

Question

Exporting reports into excel

vinay4444

SSC-Addicted

Points: 476
More actions
May 14, 2012 at 11:21 am

#260823

Hi I have a requirement where I want to export all the reports in a SSRS project into one excel with specific tab names.
Can some help me on best way to achieve this?

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

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

I have no clue how to do this but it sounds very interesting and I'd like to learn how to do this as well. The only thing I can offer is that this post will serve to "bump" your post back onto the first page where some crackerjack might find it and give us both a lesson. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ant-Green SSC Guru Points: 113563 More actions · Answer 2

unsure if this is one option.

Create subscriptions which run the report to a shared drive and export in excel format, then use something like an excel file merger http://ask.metafilter.com/106144/Combining-a-ton-of-Excel-files-into-one-Excel-file (just did a quick google search for "merge excel files") would of thought that this would of been quite easy to do in SSIS.

Create a server wide schedule for this purpose, set the subscriptions to use that schedule, then disable the schedule on the job, then use SSIS to execute sp_start_job, then use a script task to merge them together after the job has run.

Might be an option.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

I don't know much about SSRS but I did some digging. There's some suggestion that adding a page break before a report will start a new tab in the target Excel workbook. Combining that information with Anthony's idea of using a script task to merge all the reports might do the trick. Rumor also has it that you can name the tabs during the page break drill. What I Googled was "Export multiple SSRS reports to multiple Excel Tabs".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinay4444 SSC-Addicted Points: 476 More actions · Answer 4

Thanks guys

Page Break is for items in same report , I am talking about multiple reports in a project into an excel spreadsheet in different ( with custom tab names ).

Any easier option do do this please?

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

vinay4444 (5/15/2012)
Thanks guys
Page Break is for items in same report , I am talking about multiple reports in a project into an excel spreadsheet in different ( with custom tab names ).
Any easier option do do this please?

I know that. They suggested that it would also work for separate reports when you do a merge of the reports.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

Page breaks will also work with sub reports. This may choke your server, so I would start at a reasonable size and then slowly increase the number of subreports and test along the way.

Make a single report that is nothing but subreports with all your other reports as subreports. Put page breaks between them and you will get different tabs.