Introduction
SQL Server Reporting Services (SSRS) has multiple options of exporting data into a variety of formats and we will be discussing the options of exporting SSRS Reports to multiple sheets of excel. In SSRS, there are multiple formats available to export reports depending on the user’s needs. Microsoft Word, Microsoft Excel, Microsoft PowerPoint, Tiff file, MHTML (Web Archive), CSV (comma delimited) and XML file with report data are the popular formats that can be exported from SSRS as shown in the below screenshot.
As you know, every format has its own features. For example, in Microsoft excel there are sheets that are used to group the data. Excel savvy users prefer to group the data into sheets. The challenge that many users experience is exporting SSRS reports to multiple sheets of excel.
There are a couple of scenarios of requirements for exporting SSRS Reports to multiple sheets of excel.
- Different Tablix in Different sheets: In this requirement, there will be multiple tables/tablixes that you need to export them to different sheets
- Same Tablix in Different sheets: In this requirement, the same tablix will have different groups of data. For example, sales tablix will have product categories of Bikes, Components, Clothing, and Accessories. This data set may require exporting SSRS Reports to multiple sheets of Excel depending on these categories. In other words, sheets are dynamic as they depend on the data set
Scenario 1
First of all, let us create a sample report in order to demonstrate the feature of exporting SSRS Reports to multiple sheets of Excel. Let us launch the Visual Studio or SQL Server Data tool (SSDT) and create a Reporting server project. Then let us add a report to the newly created SSRS project. Next, we will create a connection by pointing out the AdventureWorksDW sample database.
Let us create two data sets where one has the bikes data and the other data set has data of clothing. Following is the query for one data set by joining FactInternetSales, DimProduct, DimProductCategory and DimProductSubcategory.
SELECT TOP (5) DimProductSubcategory.EnglishProductSubcategoryName, DimProductCategory.EnglishProductCategoryName, DimProduct.EnglishProductName, FactInternetSales.SalesAmount, FactInternetSales.TaxAmt,
FactInternetSales.Freight, DimDate.FullDateAlternateKey, FactInternetSales.SalesOrderNumber
FROM FactInternetSales INNER JOIN
DimProduct ON FactInternetSales.ProductKey = DimProduct.ProductKey INNER JOIN
DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
DimDate ON FactInternetSales.ShipDateKey = DimDate.DateKey
WHERE (DimProductCategory.EnglishProductCategoryName = ‘Bikes’)
This is configured as shown in the following screenshot:
Similarly, another data set is created for the clothing and two tables are included as follows.
Please note that in order to demonstrate, only the top five records are retrieved and the following screen shows the output of the report.
When this is exported to Microsoft excel, typically this will be exported to one sheet whereas the requirement is, exporting SSRS Reports to multiple sheets of Excel. Further, Sheet names should be Bikes and Clothing.
There are three tasks in order to create multiple sheets.
- Creating a Page Break after the Bikes table
- Naming the Sheets with appropriate names
- Repeat the headings on every page
Three different configurations have to be done for this and let us do this configuration one by to implement exporting SSRS Reports to Multiple sheets of Excel.
To add a page break, go to the properties of the first table or the tablix as shown below.
By selecting the Add a page break after, you are adding a page break after this table.
As you know, by default, sheets will be named as Sheet1, Sheet2 etc. However, we would like the name them with a proper name. For example, we would like to name the sheets as Bikes and Clothing instead of Sheet1, Sheet2.
By selecting the relevant tablix and changing the PageName to Bikes will change the sheet name to Bikes as shown in the below screenshot.
Similarly, the page name of the next tablix is named Clothing as shown in the below screenshot.
The last option is to repeat the heading on every page. There are several options for that and the very basic option is, configure the RepeatWith property of the Header text box.
The above configuration shows that the header is repeated with the Tablix3. However, if there are more than two pages, then this option will not work. Instead, we can add a report header by right-clicking and add a page header and configure as shown in the following screenshot.
Now you are done with the configuration and let us verify this.
When you preview the report, you will see that both tablixs have appeared in two different pages alone with the header. When you export it to the excel, you will see that data is in separate sheets with the relevant names. Further, you will see that you will have the header on both sheets.
This is shown in the following screenshot.
You will see that there are two sheets with appropriate names instead of default sheet names.
Scenario 2
If you look at the above example, you will see that category is kind of hardcoded. In case, there are new categories, you need to add them to another tablix along with a new query which will not be a good option. Therefore, we need the option of exporting SSRS Reports to multiple sheets of excel for dynamic groups.
Let us see the report without any page breaks as shown in the below screenshot.
When you export this to Microsoft Excel, we should see three sheets with relevant names. If there are new categories added later, those should be seen in a new sheet. This means sheets should be dynamic.
First of all, let us create a grouping with the category name by right-clicking the tablix and adding to
We have enabled the Add group header option so that we can use it to repeat the column headers later. Then move the tablix headers to the group headers and you will the following report after viewing it.
Next is to include the page break to the included groups. To do that, go to the properties of the groups from the following option.
In the Page Breaks option, choose the Between each instance of a group as shown in the below image.
Now we need to provide the sheet name for when exporting to the Multiple sheets of Excel in SSRS.
Go to the properties of the group and go to Group -> PageName as shown in the below screenshot.
Since the sheet name is dynamic, it has to be an Expression. In the expression property page, you need to provide the name of the sheet by combining the dataset fields as shown in the below screenshot.
Now everything is set and let us verify whether we get the data in when Exporting SSRS reports to Multiple Sheets of Excell.
You will observe that when exporting to multiple sheets in Excel, it can be configured dynamically in SSRS.
Conclusion
SQL Server Reporting Services is one of the most common reporting tools used in many organizations by many users. SSRS has the option of exporting reports to many formats and Microsoft Excel is the most common format that users use. However, since Microsoft Excel has the Sheet feature, users would like exporting SSRS Reports to multiple sheets of excel.
We looked at two options for creating multiple sheets in Microsoft Excel. Initially, we looked at how to create two separate tablixes into multiple sheets and name the sheets accordingly. Next, we looked at the exporting SSRS Report to multiple sheets of excel in dynamic nature. In that, we used the Grouping feature of the Tablix.
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021