Dinesh Asanka
Exporting SSRS Reports to Multiple Sheets in Excel with dynamic sheets.

Exporting SSRS reports to multiple worksheets in Excel

September 11, 2020 by

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.

Options of exporting in SSRS

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.

  1. Different Tablix in Different sheets: In this requirement, there will be multiple tables/tablixes that you need to export them to different sheets
  2. 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:

Configuring Queries in SSRS

Similarly, another data set is created for the clothing and two tables are included as follows.

Two Tablix are configured to demonstrate multiple sheets.

Please note that in order to demonstrate, only the top five records are retrieved and the following screen shows the output of the report.

View of the configured SSRS 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.

  1. Creating a Page Break after the Bikes table
  2. Naming the Sheets with appropriate names
  3. 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.

Configuration of Adding a page break after the first table for Exporting SSRS Report to Multiple Sheets of Excel.

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.

Allocating the PageNames

Similarly, the page name of the next tablix is named Clothing as shown in the below screenshot.

Naming the Sheet for the Clothing Dataset.

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.

Repeat with option to repeat the header.

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.

Configuring Report Header so that the report header will appear in every page.

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.

Exporting SSRS Reports to Multiple Sheets of Excel.

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.

Multiple groups with out page break.

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

Group by category

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.

With the Repeated column headers.

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.

Selecting Group Properties.

In the Page Breaks option, choose the Between each instance of a group as shown in the below image.

Adding the page break for exporting SSRS Reports to Multiple Sheets of Excel.

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.

PageName in the Groups.

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.

Dynamic name for the Sheets name which includes the product category

Now everything is set and let us verify whether we get the data in when Exporting SSRS reports to Multiple Sheets of Excell.

Exporting SSRS Reports to Multiple Sheets in Excel with dynamic sheets.

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.

Dinesh Asanka
210 Views