BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
SQL Server Reporting Services 2008 R2 includes (among many other features) the ability to detect a report's rendering format, together with a facility for naming report pages. You can combine these two features to create Excel workbooks with individually named worksheets for each member of a group, as shown in this blog.
- Exporting SSRS Reports to Excel using RenderFormat
- Hiding Reports Depending on ReportFormat
- Naming Excel Worksheets using PageName (this blog)
- Testing the Final Report including SSRS gauges
Posted by Andy Brown on 25 July 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Naming Excel Worksheets using PageName
If you've been following this blog so far, you'll have a report which does different things when being viewed in Excel or other formats. It's now time to work on the Excel version.
Getting the Cover Sheet Working
To get SSRS to name the first worksheet of your Excel workbook, give your table a page name (here Cover sheet):
|
|
| Select the table then ... | ... set the PageName property |
When you view the report in Excel format, Reporting Services will create a page called Cover sheet in an Excel workbook.
Creating Individual Worksheets for each Group Member
The next stage is to ensure that SSRS creates a different worksheet for each group member (here for each film director):
First, select the group in the grouping pane window at the bottom of the screen.
Secondly, set the properties of the group so that:
When you run this report, as shown in the final part of this blog, you should now get the right thing in Excel or non-Excel views!
- Exporting SSRS Reports to Excel using RenderFormat
- Hiding Reports Depending on ReportFormat
- Naming Excel Worksheets using PageName (this blog)
- Testing the Final Report including SSRS gauges