Generate Multiple Worksheets by Groups in SSRS Exported Excel Spreadsheets

By | March 13, 2014

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.

01 Create a group

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

02 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.


03 Page Break

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

03a Sheet Names

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.

04 Tab Name
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.

05 Advanced Mode

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“.

06 Keep Header

This will now copy the header on all worksheets.

Which now will look like this.

07 Tabs


4 thoughts on “Generate Multiple Worksheets by Groups in SSRS Exported Excel Spreadsheets

  1. Jay

    I tried this but only the first sheet has the header label, the other sheets only has data. How can you get the header row to repeat on all the sheets?

    Reply
  2. Paul

    I had problems with getting the tab names to export based on the Groups PageName. I found that I had to change my TargetServerVersion from SQL Server 2008 to SQL Server 2008 R2

    Solution Explorer –> Right click the project name and select Properties –> TargetServerVersion.

    Change it to “SQL Server 2008 R2″.

    Great write up! Thanks for the help.

    Reply
  3. Pingback: Interview - SSRS | Smart BI

Leave a Reply