Report Design: Naming Excel Worksheets
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:
- Naming of report pages, as shown in this previous posting.
- Rendering-dependent report layout, as discussed in another posting.
Steps for modifying the Product Catalog 2008 report to accomplish page numbers per product category.
- Open the Product Catalog 2008 sample report in Report Builder 3.0 of SQL Server Reporting Services 2008 R2.
- In the existing report, select the product catalog cover page rectangle ("Rectangle2"), and in the property grid set its PageName property to "Product Catalog Cover Page", as shown in the screenshot below. This will set the name of the initial page (and first worksheet on Excel export).
- Hide the product catalog details tablix for Excel export (and provide a more data friendly format in step 4).
a) Locate and select the overall product table ("Tablix1") near the bottom of the design surface.
b) Open the tablix properties for Tablix1, go to the Visibility page, and use the following expression for the show or hide setting:
=(Globals!RenderFormat.Name = "EXCEL")
This expression will hide Tablix1 when rendered to Excel.
- Make room in the report layout for an additional data-oriented table for Excel export by increasing the report body height by dragging the splitter between report body and page footer.
a) Use the table wizard to add a new table, group by the Category field, and add fields into the Values section such as Subcategory, Model, Product, Description, etc.
b) Move the newly created table into the empty space at the bottom of the report page.
c) Adjust the table column widths to provide more room for fields such as description, decrease the width for fields such as size and color, and add formatting as desired.
d) Select the table you just created, and open the tablix properties dialog.
e) On the Visibility page of the tablix properties, use the following expression for the show or hide setting:
=(Globals!RenderFormat.Name <> "EXCEL")
This expression will show the data table only when rendered to Excel, but not for any other rendering formats.
f) On the Sorting page, add the following sort expressions in sequence so that the data table sorts the detail rows: SubCategory (ascending), Model (ascending)
g) Close the tablix properties dialog
- Select the tablix member "Category1" (of the table created in step 4 above) from the grouping pane. In the properties window for the Category tablix member, expand the Group properties, and set the following page break related properties as shown in picture below:
BreakLocation: Between
PageName: =Fields!Category.Value
Note: By adding a page break in between each group instance and setting the page name to the current category, each category will create a separate worksheet upon Excel export, named based on the current category of data.
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.