• 
• 

 1 of 2

Gallery

Automated printing of SQL Server Reports

Posted on 12 Jan 2010 at 15:13

Simon Jones wrestles with the printing of SQL Server Reporting Services Reports

I’ve been doing battle with the printing of SQL Server Reporting Services (SSRS) Reports. SSRS is a great tool for making reports from SQL Server data, and it has a really easy-to-use interface, but sometimes you want to integrate the reports into an application without the users having to navigate the SSRS web interface.

Visual Studio 2005 and 2008 come with a ReportViewer control, which you can embed on a form of your own design, and this has a PrintDialog method you can call to make it show the Print dialog box, so the user can choose how many copies of which pages and on which printer to output them. What is definitely missing, however, is any way to preview the printed output and, more importantly, any way to print a report without showing the Print dialog.

This is often necessary when you need to print reports quickly. For example, you might need to automate the production of many reports – all to the same printer, and you want the user to choose that printer only once and the application to take care of the rest. You definitely don’t want the user to have to sit there confirming every single printed copy.

Visual Studio has the concept of a generic PrintDocument, which can be sent to any printer or Print Preview window, but the ReportViewer control doesn’t have a property or method that can return a PrintDocument. My solution was to write a generic class that encapsulated and extended the functionality of the ReportViewer control to provide a PrintDocument.

A ReportViewer control has a ServerReport object, which contains all the properties needed to define which report to show and what its parameters should be. (There’s a corresponding LocalReport object for when you’re dealing with report definitions being processed locally rather than on a server.) The ServerReport object takes a URI that points to the report server, usually the name of your SQL Server, and a path to the report definition. Once you’ve set these and any parameters that are required for the report, the ReportViewer is ready to show the report.

In this case, however, rather than showing the ReportViewer control to the user, we call its Render method to make it layout the first page of the report as an EMF (Enhanced Metafile) image and return the result as a MemoryStream. We keep calling the Render method, incrementing the page number we request, until we get an empty MemoryStream object back. We store all these MemoryStream objects we’ve received in a list, and then define a PrintDocument that will return the EMF image for the appropriate page whenever its PrintPage method is called.