Export SQL Server Reporting Services Report Data into Multiple Excel Worksheets

By:   |   Comments (19)   |   Related: > Reporting Services Development


Sometimes SQL Server Reporting Services users want to export SSRS reports into Excel with multiple sheets, but when the SSRS report data region (Tablix/Matrix) doesn't have any grouping then all the data will be exported into a single sheet. How can we export a SSRS report into Excel with multiple sheets?


In this tip we will first review the problem and then demonstrate the solution. To demonstrate, I will use the AdventureworksDW2008R2 sample database and SQL Server 2012 Reporting Service.

This tip assumes that you have previous real world work experience building a simple SSRS Report.

SSRS Exports Data into a Single Excel Worksheets by Default

Suppose in your dataset there are two fields Product Category and Product, each Product has one Product category. A Tablix is used to display both the data fields (Product Category and Product). When a user previews and exports the report into Excel the entire data is exported into a single sheet, but the user doesn't want the data in a single sheet. User wants to divide the data into multiple sheets based on the Product Category (i.e. for each instance of Product Category there should be a new sheet and the sheet name should be assigned from the Product Category).

Let's create a report for this example. I have already created a data source connection to AdventureworksDW2008R2 sample database. Let's create the below dataset for the sample report.

Report Dataset: This dataset will be used for the report body and it has three data fields (Product Category, Product and Product Key).

SELECT ProductKey, EnglishProductName, EnglishProductcategoryName
FROM  DimProduct 
INNER JOIN DimProductSubcategory 
   ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey 
INNER JOIN DimProductcategory 
   ON DimProductSubcategory.ProductCategoryKey=DimProductcategory.ProductCategoryKey
ORDER BY DimProductcategory.EnglishProductcategoryName

After creating the dataset, the Report Data Pane looks like the below image.

Report Data Pane Window

Let's add a Tablix for data viewing purpose. I will pull all three data fields from Report Dataset into the Tablix. After adding the Tablix, the sample report looks like the below image.

Adding Tablix

As of now, the Tablix in our sample report doesn't have any grouping. This report will display all the records returned from the Report Dataset. Let's preview and export the report into Excel. You can refer to the below image to export into Excel.

Export Tablix Data Into Excel

As you can see from the below image, the entire report data was exported into a single Worksheet. By Default when we export a SSRS report into Excel, the worksheet name will be similar to the report name.

Tablix Data Exported Into Single Excel Sheet

As you know, our ultimate task is to export the entire report data into multiple sheets. It is very important to understand how the data will be divided between multiple sheets. Do you want a fixed number of rows in each sheet? Or do you want data to be divided into multiple sheets based on some grouping?

For our sample report let's divide the report data into multiple sheets based on Product Category, which means for each instance of Product Category there should be a new sheet. Each sheet will contain data for one particular Product Category.

Steps to export SSRS data into multiple Excel Sheets

1. Add Dummy RowGroup

As you know, the Tablix in our sample report doesn't have any grouping and based on the Product Category we have to divide the data into multiple sheets, so we have to add a dummy row group for Product Category on top of the available parent row group. To add a parent group, select the "Details" row under Row Groups and then right click and select Add Group and then click on Parent Group. You can refer to the below image.

Tablix Add Dummy Parent Row Group

Once you click on Parent Group it will open a new window, there you have to provide a Group By field. In our case I want to divide data based on Product Category, so I have to choose EnglishProductcategoryName data field and then click OK. You can refer to the below image.

Tablix Dummy Parent Group By

As you can see from the below image a new dummy row group has been created. SSRS has automatically added a new column in the Tablix for this newly created row group.

Report After Adding Dummy Group


2. Add Page Break

You need to create a page break between each instance of Product Category, so right click on the recently created row group and click on Group Properties. You can refer to the below image.

Tablix Dummy Parent Row Group Properties

Once you click on Group Properties, a new Group Properties window will open. Click on the Page Breaks tab and check "Between each instance of a group" check box and click OK. It will give a page break between each instance of Product Category, so when you export the report into Excel each instance of Product Category will be separated into different worksheets. You can refer to the below image.

Tablix Dummy Parent Row Group Properties Page Breaks Tab


3. Delete Dummy RowGroup Column

We don’t need the first column from the Tablix, this column was created automatically when we created the dummy row group, so delete the first column.

To delete the first column, right click on the first column then click on Delete Columns. We have to keep the group, so choose the second radio button Delete columns only and then click OK. You can refer to the below image.

Tablix Dummy Parent Row Group Column Delete

Let's preview and export the report into Excel. As you can see from the below image, data has been divided into four sheets because we have four different Product Categories. If you notice in Excel the sheet names are Sheet1, Sheet2, Sheet3 and Sheet4. This is because if neither an initial page name, nor page names related to page breaks are given then the worksheet tabs will have the default names Sheet1, Sheet2, and so forth, but we need meaningful sheet names.

Tablix Data ExportedInto Multiple Excel Sheets


4. Name the worksheets according to group value

You can dynamically assign sheet names. To assign sheets name select the Details Row under Row Groups and go to the properties by pressing the F4 key, it will open a Properties window. Look for "Group" in the properties window and expand it so you can see "PageName". Assign the data field which will be used for sheet names. In our example since we used EnglishProductcategoryName data field in the grouping, I will use the same data field EnglishProductcategoryName. Now this will assign the Product Category as the worksheet name.

Tablix Detail Row Group Selection Properties


5. Show Tablix Header on all worksheets

Because the report data will be divided into multiple sheets, each sheet must have Tablix Header. To show the Tablix Header on all worksheets, click on the small drop down arrow on the Tablix window as illustrated below and choose Advance Mode

Tablix Advance Mode

Now you will see the static members, choose the "Static" member on your "Row Group" then press F4 key. It will open a properties window, for property "KeepWithGroup" set the value to "After" and for "RepeatOnNewPage" set the value to "True". You can refer to the below image. This will now display the Tablix Header on all worksheets.

Tablix Static RowGroup Properties


6. Report Preview

We have made all necessary changes, let's preview the report. As you can see from the below image when we exported the report into Excel each Product Category was exported into different sheets and this time each sheet name is assigned dynamically. The Accessories sheet contains all data for the Accessories Product Category and the Clothing Sheet contains all data for the Clothing Product Category.

Tablix Data Exported Into Multiple Excel Sheets With Name
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, July 6, 2022 - 10:02:08 AM - Russle Back To Top (90233)
I have a similar query but when the report generates it only creates the tabs that return data.

Do you know of a way to still have the tab generate but without the data?

Friday, September 17, 2021 - 1:02:22 PM - Eric Back To Top (89239)
Thank you for this article. It got me exactly where I needed to be!!

The only thing I had trouble with was that the ReportBuilder UI didn't respond to F4. I had to use the view menu at the top and check the box for properties.

Wednesday, June 3, 2020 - 3:08:02 PM - jayesh kshirsagar Back To Top (85834)

Thank yo so much..It is really informative article ..All the steps are mentioned in so much that details that I worked just fine with all the mentioned steps.

Monday, July 22, 2019 - 11:33:08 AM - Lloyd Back To Top (81836)


thank you for the clear description presented here. I have a slightly different problem I'd like to solve and I can see that you maybe the chap who might be able to help. I manually run 4 seperate pices of SQL to collect 4 different lists of data. These 4 separate lists are placed into a seperate sheet in a single Excel workbook. This workbook is then uploaded into a 3rd party application. As you can see, this is quite labour intensive. 

I would like to automate this process using SSRS to execute all 4 queries and then load the workbook sheets and then call the 3rd party application to process the data.

From what I can see above, the different sheets all contain subsets of the same data structure.

Could you please describe in the manner above, how I might acheive my objective?     

Friday, June 28, 2019 - 10:34:11 AM - Noora Back To Top (81626)


It is very helpful article but i tried to do change the names of the excle sheet tabs many times but it is not work !!

any advice?


Tuesday, April 23, 2019 - 5:06:29 PM - Ruben Back To Top (79668)

Thank you so much!!!

Friday, April 5, 2019 - 9:50:57 AM - Rob Brooks Back To Top (79479)

I've used this so many times, it has been a great help. I'm having an issue though where my sheets are all in Alphabetical order. Is there a way to specify the order in which you want the sheets to display?

Monday, July 30, 2018 - 1:07:00 PM - William Back To Top (76901)

 What about sending data all to a single excel page? I have page breaks for every 10 records, but when I want to export the data to an excel file i get multiple spreadsheets, I'm trying to avoid this... how can i do it?


Thursday, June 14, 2018 - 11:10:02 AM - Grateful User Back To Top (76221)

 This worked perfectly using SSDT 2015 and SSRS 2016.....thank you so much!!!!

Friday, June 1, 2018 - 8:31:24 AM - Nagaraj Back To Top (76071)


Hi, I have used the solution and it is working but the problem is only the first sheet is showing column names /Headers.

all other sheets in the excel do not have column names.

Is there a solution for this?


Thanks for the solution and hope i get solution for this issue as well. 


Friday, April 28, 2017 - 5:32:54 AM - Bhuvaneswari Ramasamy Back To Top (55268)

 This is very very helpful. Steps are explained well.





Tuesday, February 16, 2016 - 10:59:45 PM - Kris Maly Back To Top (40697)

 Hello Friends  and Mr. Ganesh Prasad,

Thanks for all your help.

Finally I found an article 

SSIS - How to Create Multiple Files Dynamically From a SQL Server Table


The above link guides you how to create ".TXT" file

While configuring Destination file type ".CSV" instead ".TXT

This will creates Multiple Excel workbooks or Excel files


During my reserach In the website http://www.techbrothersit.com/   I found lots of other articles and Videos which helped me a lot.

I would like to share with you all


Thanks again





Friday, February 12, 2016 - 11:03:45 AM - Kris Back To Top (40667)


When an SSRS report is ran then how to Export the each group to different Excel files?






Is there a tip

Any help is appreciated

Wednesday, February 10, 2016 - 7:31:44 PM - Kris Back To Top (40644)

Thank you

Thank you

Thank you

I went through this article and carried all the steps and they were clear and crisp.

I was able to create the report successfully.

This article made my day!!!

Thanks for educating the community and appreciate your volunteership.

Thanks a bunch


Friday, July 24, 2015 - 12:58:47 AM - Deepak Back To Top (38286)

Great Explanation......Very simple and understandable langauage....thanks

Wednesday, June 24, 2015 - 11:15:42 AM - Kim Claybaugh Back To Top (38014)

Thank you for the great article! This helps really me with SSRS and excel.

Do you know of any way to have different data sets on different excel sheets? like the first sheet could be sorted by product type the second sheet sorted by product name and the third sheet be just totals.

Wednesday, June 24, 2015 - 2:11:57 AM - Deepak Sharma Back To Top (38005)


You can use to render your report in multiple sheets in SSRS 2005, but PageName property is not available in SSRS 2005.

Sunday, June 21, 2015 - 1:32:20 PM - Eileen Back To Top (37975)


I enjoyed your article -- Thank you!

Do you know if I can apply this to SSRS2005?








Tuesday, June 16, 2015 - 7:06:26 PM - Jack Back To Top (37938)


Great article

get free sql tips
agree to terms