Steps to export SSRS data into multiple Excel Sheets

1. Add Parent Group to the existing 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 VendAcount, so I have to choose VendAcount 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 VendAcount, 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 VendAcount, so when you export the report into Excel each instance of VendAcount 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 Vend Account data field in the grouping, I will use the same data field VendAccount. Now this will assign the VendAccount as the worksheet name.

5. 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 Vend Account was exported into different sheets and this time each sheet name is assigned dynamically.