Generate Multiple Worksheets by Groups in SSRS Exported Excel Spreadsheets

You might be wondering how to generate multiple dynamic worksheets in SQL Server Reporting Services, is this even possible or not? Well if you are using SQL Server 2008 R2 and above you are lucky as this is now possible if not I guess it is the time to upgrade.

So how do you achieve this?

It is fairly easy, all you need is to follow this 4 easy steps.

1. Create Row Groups for your report

Choose how your data will be grouped, this grouping will be the individual worksheets you are after. To do that right-click on your report row then choose “Add Group” then “Parent Group“. For this example we will group it by Booking Id.

2. Set the groups to be individual worksheets

Now that you set how do you want to group your data we then now define the worksheets by using the page break options. First you need to go to your row group then select “Group Properties”

Now look for “Page Breaks” then tick the box “between each instance of the group“, what this does is break the page on each group that you defined so when you export them they will be separated by worksheets.

By default it will work now but the worksheets will be named like such.

3. Name the worksheets according to the group value

If you are not satisfied with just Sheet1, Sheet2, Sheet3, … then you need to dynamically assign tab names by going to the properties of the Tablix. Look for “Group” expand it and you would see “PageName“, assign the correct value by using your fields. In our example since we used Booking_Id it will be =Fields!Booking_Id.Value.

Now this would assign the Booking Id as the worksheet name.

Again at this point everything will work fine but you will notice when you run them the header will only show on the first page and will not be shown on succeeding worksheets.

4. Show Report Header on all worksheets.

To show the Report Header on all worksheets in SSRS you have to click that small drop down arrow on tablix window as illustrated below.

Choose “Advanced Mode”

Now you will see the static members, choose the “Static” member on your “Row Group” then on its properties give “KeepWithGroup” the value of “After” and “RepeatOnNewPage” the value of “True“.

This will now copy the header on all worksheets.

Which now will look like this.