Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Export SQL Server Reporting Services Report Data into Multiple Excel Worksheets


By:   |   Read Comments (9)   |   Related Tips: > Reporting Services Tutorials

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


Problem

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?

Solution

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


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 This is very very helpful. Steps are explained well.

 

Thanks,

Bhuvana

 


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

 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

http://www.techbrothersit.com/2014/04/ssis-how-to-create-multiple-files.html

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

 Question,

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

Like 

Accesories.xlxs

Bikes.xlxs

Clothing.xlxs

Components.xlxs

Is there a tip

Any help is appreciated


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

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

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


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

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

@Eileen

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

Ghanesh, 

I enjoyed your article -- Thank you!

Do you know if I can apply this to SSRS2005?

 

Thanks,

Eileen

 

 

 

 


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

 

Great article


Learn more about SQL Server tools