Where The Sheets Have A Name

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.

The Scenario

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.

The Report

Starting Position

Let’s first have a quick look what the export to Excel currently looks like, without any modifications to the report.

Default export to Excel - all data in one sheet

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.

Use the InitialPageName property on the report to change the default name of the Excel sheet

Fill in a value and here’s the result in Excel:

The Excel sheet with its default name changed to a very unique name

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.

Add group on Product Category

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:

Tablix with group on Product Category added

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.

Adding page breaks between the instances of a group

Let’s render the report and export to Excel to have a look at the effect of adding these page breaks.

Report exported to Excel with each group in a separate sheet

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:

=Fields!ProductCategoryName.Value

That’s it, that’s all you need to do!  Don’t believe me?  Here’s what the export to Excel now looks like:

Data exported to Excel, with customized sheetnames

Conclusion

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!

Have fun!

Valentino.

References

Understanding Pagination in Reporting Services (Report Builder 3.0 and SSRS)

Share

Tags: , ,

  1. Gadjo Dilo’s avatar

    I’ve been looking for this solutions for a few days now.
    Everything worked great, just like your tutorial.

    Thank you!

    Reply

  2. Alex Calhoun’s avatar

    I’m attempting to implement these steps on a report upgraded from SSRS 2005 to 2008 R2. I’ve gone through all the steps, but the Excel document still has unnamed tabs. Setting the InitialPageName doesn’t work either. I’m using the Report Designer in MS Visual Studio; do these features only work from Report Builder 3.0?

    Reply

    1. Valentino Vranken’s avatar

      Nope, should work from BIDS as well, that’s what I used here. When you say Visual Studio, do you mean really VS or BIDS? Or, in other words, are you building a client-side report (RDLC) or a regular SSRS report (RDL)?

      In case of RDLC, the new features from SQL Server 2008 R2 may not be supported, not even in VS2010. See http://msdn.microsoft.com/en-us/library/ee960138.aspx
      Could be that an update is available nowadays, but I’m not sure of that. (I never use RDLCs.)

      Reply

      1. Alex Calhoun’s avatar

        I was using an RDL, not an RDLC, and I did get this working. The issue was that the project was set to process as v2008, not 2008 R2. Once I updated that setting, it worked as expected. I didn’t realize that after the upgrade it would default to just v2008.

        Reply

        1. Al’s avatar

          How exactly did you update your settings? I have Sql express 2008 R2 with no local Reporting server. Is this the issue?

          Reply

          1. Alex Calhoun’s avatar

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

            Change it to “SQL Server 2008 R2″.

  3. Anjali’s avatar

    fabulous piece of work ….helped quite as lot to fulfill my requirement.

    Reply

  4. Eric’s avatar

    you saved my life I have changed “SQL Server 2008″ to “SQL Server 2008″ and it works great!!!!
    I wasted so much time
    thanks again

    Reply

  5. Dayal’s avatar

    Worked like gem! Thanks for your knowledge sharing. I’m benefited.

    Reply

  6. Rick’s avatar

    This is great!!!

    I need to take it one step further though and have not been successful. I need to show all data on one tab, then the other groups on the separate tabs. I was able to get separate tabs for each group but not able to figure out how to show everything on the first tab on the same excel document.

    Any ideas?

    Thanks again!

    Reply

    1. Valentino Vranken’s avatar

      Thanks Rick.

      To show all data on the first sheet I would add an additional tablix above the one you’ve already got and only make it visible when exporting to Excel.

      See my other article for more info on showing/hiding items based on rendering: http://blog.hoegaerden.be/2011/01/18/ssrs-hideshow-items-dependant-on-export-format/

      Enjoy the weekend!
      Valentino.

      Reply

      1. Rick’s avatar

        Thanks again!!

        By the way, I like your illustrations the best by far!!!

        Reply

        1. Valentino Vranken’s avatar

          I try to be original, when possible :)

          Reply

  7. Samith’s avatar

    There is any restriction on this functionality in Excel 2010 ??

    Reply

  8. Atulkumar’s avatar

    Thanks for the solution…it worked really well

    Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved