?
Solved

SSRS 2008

Posted on 2014-03-07
6
Medium Priority
?
633 Views
Last Modified: 2014-04-03
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
0
Comment
Question by:BeyondBGCM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39914170
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$]')
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39914284
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.
0
 
LVL 27

Expert Comment

by:planocz
ID: 39914689
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BeyondBGCM
ID: 39931547
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. :)
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 1500 total points
ID: 39933605
Please see the attached example that uses "Section A" and "Section B" as sheet names. I've used the pagename propery of the Rectangle and the Tablix to set the sheet names. Also see this article for more information about page names:
http://msdn.microsoft.com/en-us/library/dd255278(v=sql.105).aspx
You can't set the pagename of the subreport so you'll have to place the subreports in a Tablix or Rectangle.
ExcelExport.rdl
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question