One of the frequent feature requests for Reporting Services was to add the ability to define data-dependent names for worksheets when exporting to Excel. Reporting Services 2008 R2 adds this capability through a more general feature to name pages.
In Excel export, report page names determine the name of worksheets. The report item with the most top/left position on a rendered page defines the page name for a particular rendered page. The screenshot below demonstrate a data oriented view of the Product Catalog report when exported to Excel, with worksheets for data per product category, while you still get the regular Product Catalog view in other rendering formats.
In this posting I show how to build this kind of report. All you need to try this yourself is the AdventureWorks2008 sample database (download), and the Product Catalog 2008 sample report (download). Alternatively, you can download the final report from the attachment of this posting (download), and/or read more details about it in the following book on pages 420-424.
The report design combines two concepts:
Steps for modifying the Product Catalog 2008 report to accomplish page numbers per product category.
Done – run the report and enjoy! You still get the original product catalog view with document map navigation when running in HTML, Preview, etc. However, when exporting to Excel you get a cover page, and worksheets named per category with data tables.