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 (this blog)
- Naming Excel Worksheets using PageName
- 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.
Hiding Reports Depending on ReportFormat
The first step is to create the report which you'll see in normal view:

Create a standard tabular report, and then select the table so that you can change its properties.
With the table selected as above, set its Hidden property as follows:

The Hidden property will be True only if the report is being formatted as an Excel workbook.
For those used to previous versions of Reporting Services, you can find the RenderFormat.Name property in the new Built-in Fields collection:

In the expression builder, you can choose the object shown to refer to the render format being used. In SSRS 2008 R2 this also includes Word.
Reproducing the Same Report for Excel
Now that you've got this table viewable in any mode but Excel, you can copy and paste it to get two versions of the table, one viewable in Excel and one viewable for all other render formats:

For clarity, the (top) Excel report is shown with red fill and the (bottom) normal one with blue fill.
You can now set the Hidden property of the top report to be:
You should now find that you get the blue report in all views apart from Excel, for which you get the red report. It's now time to start naming pages to get the individually named Excel workbooks!
- Exporting SSRS Reports to Excel using RenderFormat
- Hiding Reports Depending on ReportFormat (this blog)
- Naming Excel Worksheets using PageName
- Testing the Final Report including SSRS gauges