Avatar of BeyondBGCM
BeyondBGCM asked on

SSRS 2008

how can i export data from a report into multiple sheets , and also give each sheet the name i want

consider a case where i have a SSRS report, and now i want it to export data to Excel sheet such that , one Sub report of the report goes to one sheet of the SSRS , and another Sub report goes to another sheet and also it takes the name of subreport as the name of Excel sheet it goes to.

I am using SSRS 2008 R2
SSRSMicrosoft SQL Server

Avatar of undefined
Last Comment
Nico Bontenbal

8/22/2022 - Mon
dbaSQL

I am not 100% certain, but I wonder if you can implement OPENROWSET in your SSRS export.  Something like this:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Directory\ExcelFileName.xls', 'SELECT * FROM [Sheet1$]')
Nico Bontenbal

I didn't test it but it looks like you can get multiple sheets by setting page breaks after a table. So this might work with subreports also. Search Google on:
"In SSRS, After Export into excel how to give Sheet name"
(including the quotes). This describes how to get multiple sheets, but also tells you the sheets are always named 'sheet 1', 'sheet 2'. But the article is rather old so maybe this was fixed.

You can find a completely different and much more complex technique when you search Google for:
"Changing the Sheet names in SQL Server RS Excel: QnD XSLT"
This technique uses an XSLT to create an XML that represents an Excel workbook. A lot of work but very powerful.
Howard Cantrell

You might try this...
At the start and end of each sub-report  make a rectrangle and place a page break inside it.
This should force a regular page break at that point.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Monica P

ASKER
BeyondBGCM

Can you provide a working solution , which you have tried yourself , i have searched these links on google , myself , and they are not working for me .

I request to provide a solution which you have tried with yourself ,i search google links and after that i post my question here. :)
ASKER CERTIFIED SOLUTION
Nico Bontenbal

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question