Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

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

Legacy Comments


markus
2008-11-16
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

Kelly
2008-11-17
re: Exporting a report to multiple Excel tabs
I would also like to know how to change the names of the tabs...

Keith
2008-11-17
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

Joe Webb
2008-11-18
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.

Ashok Malani
2008-11-25
re: Exporting a report to multiple Excel tabs
Is any one has that .net code for above problem

Aya Abd El-Wahab
2009-02-26
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??

Gustavo
2009-04-21
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

zzbyasb
2009-08-10
re: Exporting a report to multiple Excel tabs
I've found some code, but i don't know where to put it.

daveb
2009-08-10
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/

BJ Lap
2009-11-04
re: Exporting a report to multiple Excel tabs
I would also really like to see the code. Was it put in pastebin?

DoYouknow.IN
2011-03-31
re: Exporting a report to multiple Excel tabs
Great one....anyone got solution on Tab Rename ???

Vivek Banafar
2011-05-25
re: Exporting a report to multiple Excel tabs
I like the way it is been define I love to use the concept

cheryl
2011-07-18
re: Exporting a report to multiple Excel tabs
You can rename the tabs using the PageName property in 2008 R2. Unfortunately not prior verrsions.

Krishnan
2011-09-09
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??

Anthony
2012-01-30
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

Naresh
2012-02-27
re: Exporting a report to multiple Excel tabs
Good One thanks :)

Fabio
2012-02-28
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.

Jawed
2012-04-16
re: Exporting a report to multiple Excel tabs
Very helpful example and discussion. Thanks