Did you know that as of SQL Server Reporting Services 2008 R2 you can give the worksheets a customized name when exporting your report to Excel? If you didn’t, or you did but never took the time to find out how you’d implement that, I’ll show you here and now!
For this example I’ll be starting off from the report created in my earlier post on Cascading Calculated Fields.
The result can be downloaded from my Skydrive through this link.
As you may recall, our report shows a list of all our company’s products. When the report gets exported to Excel, each product category should get its own sheet. So all products from the Audio category should be located in a sheet called “Audio”, all Games and Toys in a sheet called “Games and Toys”, and so on.
Let’s first have a quick look what the export to Excel currently looks like, without any modifications to the report.
All records are being exported to just one sheet. And, by default, the name of the sheet is the name of the report. (I made a copy of my existing report and called it NamingExcelSheets.rdl.)
In case you want to change the default name of the sheet, it’s possible. On the report itself, there’s a property called InitialPageName.
Fill in a value and here’s the result in Excel:
Adding The Category Group
To be able to get the different categories into different sheets, we need to add a group on Category to the tablix in the report.
With the tablix selected, right-click the Details line in the Row Groups pane and select Add Group > Parent Group. Select ProductCategoryName as field to group by and activate the Add group header checkbox.
Remove the group column that gets added automatically and move the header cells from the main header to the group header. Delete the main header row so that you end up with something like this:
Open up the Group Properties by double-clicking the new ProductCategoryName item in the Row Groups pane. Select the Page Breaks page and activate the Between each instance of a group checkbox. Doing this ensures that each group gets its own page in the report, and its own sheet in Excel.
Let’s render the report and export to Excel to have a look at the effect of adding these page breaks.
Indeed, every group has gotten its own worksheet. However, they’ve also gotten the very original names such as Sheet1, Sheet2 and so on.
Customizing The Names Of The Sheets
On to the final part of the requirements: giving our own customized name to the generated Excel sheets. This is actually really easy once you know how to do this.
First select the ProductCategoryName group in the Row Groups pane so that its properties are displayed in the Properties pane. In the Properties pane, locate the Group > PageName property and specify the following expression:
That’s it, that’s all you need to do! Don’t believe me? Here’s what the export to Excel now looks like:
As we’ve seen in this article, it really doesn’t take too much effort to implement a custom name for the worksheets when exporting a report to Excel. Neat feature!