Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

EXPORTING SSRS REPORTS TO EXCEL USING RENDERFORMAT

Part one 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 (this article)
  2. Hiding Reports Depending on ReportFormat
  3. Naming Excel Worksheets using PageName
  4. Testing the Final Report including SSRS gauges
Posted by Andy Brown on 25 July 2011 | 2 comments

Exporting SSRS Reports to Excel using RenderFormat

SQL Server Reporting Services 2008 R2 introduces (amoung many other things) the following two new concepts:

  1. The ability to assign a PageName property to any part of a report; and
  2. The ability to detect and react to the RenderFormat property of a report, to do different things according to how the report is being viewed.

This blog explains how you can combine these two ideas to create a report which looks like this (for example) in normal view:

Report in normal view

In normal view, the report just lists out films grouped by director.

 

However, when you view this report in Excel view, SSRS creates the following Excel workbook:

Cover sheet of Excel workbook

The first worksheet explains what the rest of the workbook contains.

 
Typical worksheet for each director

Subsequent worksheets list the films made by each director

 

How do you reach this programming nirvana?  The rest of this blog gives a free tutorial to explain how!

If you want to reproduce the report shown in this blog exactly, you may find it useful to download the script to generate the necessary database and table.

 

EXPORTING SSRS REPORTS TO EXCEL USING RENDERFORMAT

Part one 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 (this article)
  2. Hiding Reports Depending on ReportFormat
  3. Naming Excel Worksheets using PageName
  4. Testing the Final Report including SSRS gauges

Comments on this blog

This blog has 2 comments:

Comment added by nrlehr on 08 September 2011 at 06:23 GMT
Being new to SSRS, I'd like to know exactly how you setup the table data region for this report (RenderFormat article). There were no screen shots indicating grouping, expressions etc. This would be very helpful for newbies. Thank you.
 
Comment added by Andy Brown on 08 September 2011 at 08:38 GMT

Hi NrLehr

I think the answer to that has to be to attend an SSRS training course with us! 

Seriously, I would like to blog much more on SSRS basics, and intend to do so at some time in the future.  Time, as ever, is the enemy.

Andy

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.