Exporting SSRS Reports to Excel using RenderFormat
Part two of a four-part series of blogs

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.

  1. Exporting SSRS Reports to Excel using RenderFormat
  2. Hiding Reports Depending on ReportFormat (this blog)
  3. Naming Excel Worksheets using PageName
  4. 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:

Normal report in design 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:

Hidden property for normal report

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:

Inserting the RenderFormat property

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:

The two reports - normal and Excel

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:

=(Globals!RenderFormat.Name<>"EXCEL")

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!

  1. Exporting SSRS Reports to Excel using RenderFormat
  2. Hiding Reports Depending on ReportFormat (this blog)
  3. Naming Excel Worksheets using PageName
  4. Testing the Final Report including SSRS gauges
This blog has 0 threads Add post