Solved

SSRS 2008

Posted on 2014-03-07
6
628 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

825 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