Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

Exporting a report to multiple Excel tabs

Sometimes, SQL Server Reporting Services users like to see what could be considered multiple reports in a single SSRS report. In most cases we can easily accomplish this using more than one data region in the report. If not, we can always use a subreport.

Let's say we have a report called the Top 10 Report. In it, we wish to see the top ten products sold by order quantity. We'd also like to see the top 10 resellers of our products, again by order quantity. This could be accomplished using multiple data regions or using two subreports.

We'll use two table data regions for this demonstration. This is shown in the following figure.

ExcelExport1-2008-11-12

Previewing the report produces the following results.

ExcelExport1a-2008-11-12

This generally works well. Our users can view the single report and examine the details from its two data regions. If we choose, we can use conditional formatting techniques to dynamically show or hide each data region. But I digress; back to our example.

When our Top 10 Report is exported into Microsoft Excel format, both data regions are exported to the same worksheet as shown in the figure below.

ExcelExport2-2008-11-12

This causes some users angst. They wish each data region to be exported to a separate worksheet or tab.

We can easily do this. Examine the properties for the first table data region. You'll notice that there is a boolean property named PageBreakAtEnd. By default, this value is set to False. Changing its value to True will cause a page break to be inserted after the table.

 ExcelExport3-2008-11-12

A page break at the end of a data region will cause a new tab to be created when the report is exported to Microsoft. The first data region, the Top 10 Products Sold by Order Quantity, appears on the first tab.

ExcelExport4-2008-11-12

The Top 10 Resellers by Purchase Quantity report appears on the second tab of the Excel workbook.

ExcelExport5-2008-11-12 

So using this technique we can effectively combine multiple reports in the one Reporting Services report, while giving our users the ability to export them to different tabs in Microsoft Excel.

Got a good tip or trick for Reporting Services? I'd love to hear about it.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Wednesday, November 12, 2008 5:06 PM | Filed Under [ Reporting Services ]

Feedback

Gravatar

# re: Exporting a report to multiple Excel tabs

nice sharing.. i use it a lot on my job. But still there is a small thing on my mind, can we change the name of sheet, such example we named the sheet with "top 10 Product" and "top 10 resellers" rather than "sheet 1" and "sheet 2"?

thanks a lot.

regards,
markus
11/16/2008 8:45 PM | markus
Gravatar

# re: Exporting a report to multiple Excel tabs

I would also like to know how to change the names of the tabs...
11/17/2008 8:27 AM | Kelly
Gravatar

# re: Exporting a report to multiple Excel tabs

Good tip. I will use this a lot.

How about creating a pivot table (in a new tab) based on one of the existing data regions?

Thanks
11/17/2008 4:18 PM | Keith
Gravatar

# re: Exporting a report to multiple Excel tabs

I don't believe it's possible to rename a tab from within Reporting Services unless you add some custom .net code.
11/18/2008 11:14 AM | Joe Webb
Gravatar

# re: Exporting a report to multiple Excel tabs

Is any one has that .net code for above problem
11/25/2008 12:15 PM | Ashok Malani
Gravatar

# re: Exporting a report to multiple Excel tabs

Something you probably didn't notice is that the second sheet has a blank hidden row before the header row.. Any ideas why this happens??
2/26/2009 5:34 AM | Aya Abd El-Wahab
Gravatar

# re: Exporting a report to multiple Excel tabs

Has any one a .Net code to rename a tab when you export a report from Reporting services?
Thanks
4/21/2009 3:33 PM | Gustavo
Gravatar

# re: Exporting a report to multiple Excel tabs

I've found some code, but i don't know where to put it.
8/10/2009 11:51 AM | zzbyasb
Gravatar

# re: Exporting a report to multiple Excel tabs

zzbyasb... I would really love to see that code. Can you past bin it? http://pastebin.ca/
8/10/2009 6:20 PM | daveb
Gravatar

# re: Exporting a report to multiple Excel tabs

I would also really like to see the code. Was it put in pastebin?
11/4/2009 1:07 AM | BJ Lap
Gravatar

# re: Exporting a report to multiple Excel tabs

We'll use two table data regions for this demonstration. This is shown in the following figure.
10/16/2010 4:00 PM | columbia jackets
Gravatar

# re: Exporting a report to multiple Excel tabs

We'll use two table data regions for this demonstration. This is shown in the following figure.


snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:34 AM | furry boots
Gravatar

# re: Exporting a report to multiple Excel tabs

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under simple video editing function embedded, support TRIM, CUT, CROP, and Join video files. you may use it as an video joiner for mac or video cutter for mac.
10/26/2010 1:37 AM | hanly
Gravatar

# re: Exporting a report to multiple Excel tabs

Great one....anyone got solution on Tab Rename ???
3/31/2011 7:59 AM | DoYouknow.IN
Gravatar

# re: Exporting a report to multiple Excel tabs

I like the way it is been define I love to use the concept
5/25/2011 5:01 AM | Vivek Banafar
Gravatar

# re: Exporting a report to multiple Excel tabs

You can rename the tabs using the PageName property in 2008 R2. Unfortunately not prior verrsions.
7/18/2011 11:51 AM | cheryl
Gravatar

# re: Exporting a report to multiple Excel tabs

if you use pagename property then it is changed for all the sheets.How to change for specific sheets??
9/9/2011 3:50 AM | Krishnan
Gravatar

# re: Exporting a report to multiple Excel tabs

I am new to reporting services and thanks that is exactly what I was looking to do. This was very well done
1/30/2012 10:10 AM | Anthony
Gravatar

# re: Exporting a report to multiple Excel tabs

Good One thanks :)
2/27/2012 1:09 AM | Naresh
Gravatar

# re: Exporting a report to multiple Excel tabs

I am using SSRS 2008 R2 and can confirm that setting the PageName property on each of the tables allows you to specify the tab names.

Thanks Cheryl.
2/28/2012 9:37 AM | Fabio
Gravatar

# re: Exporting a report to multiple Excel tabs

Very helpful example and discussion. Thanks
4/16/2012 4:52 PM | Jawed
Gravatar

# re: Exporting a report to multiple Excel tabs

Nice, great tut. Helped me make a great looking and functional report just today!
9/6/2012 2:24 PM | Kevin
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET