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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
Next Steps
- If users want to divide data into multiple sheets based on fixed number of records then don’t forget to check this tip Display Fixed number of rows in SQL Server Reporting Services.
- Check out all of my tips here.
- Check out other tips on SQL Server Reporting Services Parameters.
- Check out all of the SQL Server Reporting Services tips
