output a SSRS report in excel with multiple tabls

How would I create a SSRS report that would automatically output and excel spreadsheet with multiple tabs?
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jim HornMicrosoft SQL Server Data DudeCommented:
Short answer:  You don't.

SSRS reports are intended to be printed on paper or .pdf with a single rendering, and not with real advanced logic beyond that rendering in exporting sets such as an Excel spreadsheet with multiple tabs.  

You CAN create an SSIS package that has multiple connections that are the same Excel spreadsheet/multiple named ranges, and export that way.
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Hello,

Sometimes we get requirement to export data in excel and that too in separate excel tab.

You can achieve this using some configuration of tablix.

I tried to explain the same using below example.  Data is from AdventureWorks showing list of product by Category and sub category in table.
Requirement is to export data but in to separate excel tab.

With normal config means no page break between group, when you export data in excel it will be exported in single tab.

1)  Now to export data in separate first you need to add page break for the group you need separate page. In this, i took category group. Select Page break to Between  when you run report and export it will generate separate excel tab for export.

if you need have tab with specific name. You can set PageName Property as well.

2018-03-30_23-41-42.jpg


I have attached excel sheet and rdl as well for your reference .
SSRSReportWithMultipleExcelTabExport.rdl

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
vbnetcoderAuthor Commented:
ty
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.