Solved

SSRS 2008

Posted on 2014-03-07
6
618 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 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

19 Experts available now in Live!

Get 1:1 Help Now