Solved

SSRS 2008

Posted on 2014-03-07
6
624 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 10

Expert Comment

by:Monica P
ID: 39917113
0
 

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now