Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

Home   >  Blogs

EXPORTING SSRS REPORTS TO EXCEL USING RENDERFORMAT

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

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 PageName property
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):

The Grouping Pane

First, select the group in the grouping pane window at the bottom of the screen.

 
The group properties window

Secondly, set the properties of the group so that:

  • The BreakLocation is set to Start (Reporting Services will insert a page break before each occurrence of the group)
  • The PageName is set to an expression which gives the value of each member of the group (here the Director field value).
 

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

Part three 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
  3. Naming Excel Worksheets using PageName (this article)
  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

Add a comment to this blog

BLOGS BY TOPIC

BY AUTHOR

BY YEAR

 

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