Printing Reporting Services Reports in Landscape from within .NET

Introduction

Microsoft has recently released Reporting Services, an enterprise level reporting system. Reporting Services (RS) is now easily obtainable and most likely will be bundled with SQL Server starting with its next release. This makes RS not only powerful, but convenient. However, being a new product, it suffers from (relatively few) lack of mature features. One surprising element that is missing is the ability to print a report in landscape orientation. This article will take you through developing .NET work-around code to simulate a landscape report via RS. This work-around is functional, but does have the standard work-around property of being somewhat limited, as you will see.

Assumptions

This article assumes that:

• You have some experience with VB.NET and Reporting Services.
• That you have a report prepared and have set its margins to a width of 11.5 and a height of 8.5. (The reverse of the default margins.)

Background

RS delivers all its reports via a browser using HTML formatting. This allows for extremely portable and integratable output. However, the downside is that we are somewhat limited by the constraints of HTML and the browser. Since HTML is inherently setup in a portrait orientation, it follows that all RS reports will be portrait.

However, RS also allows for exporting output to other formats such as Excel, CSV, etc. Among the list of available output formats is Adobe�s Acrobat PDF format. This free document handling technology handles such features as printing landscape. So, all we need to do is run the report and export it to the Adobe Acrobat PDF format� right? Well, there are a few other twist turns to think about.

For example, how do we generate a report and automatically export it to the Acrobat format? Once it�s generated, we need to get a local copy of it to print it. As well, Acrobat does not support OLE commands. So, we will have to use a courser method of opening the program and telling it to print.

Using the code

Following is some VB.NET code that outlines the basic process. For simplicity, I have hard-coded some values and am assuming the report requires parameters to be run. As well, much to my own horror, I am omitting basic error trapping and other bullet proofing, and just sticking with the basics for clarity.

Private Sub PrintReport() 
   /*Path to Report file, including command to export to PDF  */
   Const URL As String = 
     "http://localhost/ReportServer">http://localhost/ReportServer" & _
    "?/TestSection/DBStats" & _
      "&rs%3aCommand=Render&rs%3AFormat=PDF" 

   'Temp file location for a local working copy of the generated file 

   Const LocalFile As String = "C:\TEMP.PDF" 
 
   'Copy the file from the web server to our temp file location 

   DownloadWebFile(URL, LocalFile) 
 
   'Now that we have the file, send it to Acrobat for printing 

   PrintAdobePDF(LocalFile) 
 
   'The report should now be on the printer. Clean up the temp file 

   If IO.File.Exists(LocalFile) Then IO.File.Delete(LocalFile) 
End Sub

A close look at the URL constant will reveal that it is pointing to a RS Server located on the local host. The ?/TestSection/DBStats segment instructs the RS Server to find the DBStats report located in the TestSection category. The final segment that reads &rs%3aCommand=Render&rs%3AFormat=PDF instructs the server to render the report using the Adobe Acrobat PDF format. Voila! We now have a baby bouncing PDF file of the report to work with.

Note that if your report does require parameters, you would stuff them in the URL as well. See the RS documentation on how to structure those commands.

As an alternative, you could create the report via a Web Service provided by RS. There is good documentation in RS on how to do this. However, again, for simplicity I am going with the URL construction kit approach for this demonstration.

Now that we have generated a PDF on the server, we need to make a local copy. Later, we will be employing a technique to print the file that requires the file exist locally. Note: Locally is simply shorthand for "Not on the Web Server". The file doesn't have to be on the local hard drive, although that location certainly makes the most sense in most situations.

The DownloadWebFile routine makes use of a widely used technique. Now that we have generated a PDF on the server, we need to make a local copy. The DownloadWebFile routine makes use of a widely used technique to copy a file from a web server to a local drive and it goes a little somethin� like this:

'Downloads a web file at the given URL to the given destination file 

Sub DownloadWebFile(ByVal URL As String, ByVal DestFilename As String) 
   'Create a web request to attach to the remote file 

   Dim WebFile As System.Net.WebRequest 
   'Create a file stream for writing the file to the local filename 

   Dim LocalFile As System.IO.FileStream
 
   'Create some working variables for the copy process 

   Dim Buffer(16384) As Byte 
   Dim BytesRead As Long
 
   'Open a WebRequest stream to the source file 

   WebFile = System.Net.WebRequest.Create(URL) 
   'Credentials are required, pass the defaults 

   WebFile.Credentials = System.Net.CredentialCache.DefaultCredentials 
   'Create the local file 

   LocalFile = New IO.FileStream(DestFilename, IO.FileMode.Create)
 
   'Download the file in 16k chunks 

   With WebFile.GetResponse.GetResponseStream 
      Do 
         BytesRead = .Read(Buffer, 0, 16384) 
         LocalFile.Write(Buffer, 0, BytesRead) 
      Loop Until BytesRead = 0 
 
      .Close() 
   End With
 
   WebFile = Nothing
 
   'Force all data out of memory and into the file before closing 

   LocalFile.Flush() 
   LocalFile.Close() 
   LocalFile = Nothing 
End Sub

Lastly, we get to the heart of the matter. Since there is no OLE object in the Acrobat Reader, we can simply open the local file by spawning a new process. This is equivalent to using the Run command but allows us to have a bit more control.

Assuming that your system has the .PDF extension associated with Adobe Acrobat (and does anyone not have it?) we can simply call the data file, and Windows will load Acrobat for us. As well, we can specify a Verb. Verbs allow us to tell Windows what to do with the file. For example, the Run command uses the Open Verb, which opens the specified file. There is an Explore Verb for opening file folders, etc. We are going to make use of the Print Verb which loads the parent application and automatically begins to print the document.

A word of warning, the print job will be sent directly to the Windows default printer with the default page setup and default number of copies, etc. This is an obvious short-coming, but hey� I told you this was a limited work-around.

The last problem to surmount is that Acrobat has the curious behavior of staying in memory after the job has been printed. The document itself closes after the print, but the Acrobat application remains in memory. This is not good, especially if you are printing a large batch of reports. So, the final segment of the following code monitors the in memory process and asks if it�s done yet every so often. Once it�s done, or once a timeout is exceeded, the Acrobat is forced to be closed.

Sub PrintAdobePDF(ByVal Filename As String) 

Dim myProcess As New Process 
 
   Try 
      'Point the process to our Acrobat file 

      myProcess.StartInfo.FileName = Filename 
      myProcess.StartInfo.WorkingDirectory = 
           New IO.FileInfo(Filename).DirectoryName 
 
      'Since we don't want to see Acrobat's UI 

      '(we just want the report printed) 

      'then suppress the UI and pass the "Print" verb 

      'to force Acrobat to only print 

      myProcess.StartInfo.CreateNoWindow = True 
      myProcess.StartInfo.WindowStyle = 
                ProcessWindowStyle.Hidden 
      myProcess.StartInfo.Verb = "Print" 
      myProcess.Start() 

      'Wait until Acrobat has finished heavy processing, 

      'or 10 seconds 

      'whichever comes first 

      If Not myProcess.HasExited Then 
         myProcess.WaitForInputIdle(10000) 
         'Attempt to close Acrobat so it's UI 

         'doesn't sit on the user's task bar 

         'loop until we suceed or until we timeout. 

         'If we timeout, kill Adobe 

         Dim i As Integer = 1 
         Dim lbRunning As Boolean = True 
         While lbRunning And i <= 20 
            'sleep for 1/2 of a second 

            System.Threading.Thread.Sleep(500) 

            'See if the Adobe will exit gracefully Select 

            Case myProcess.HasExited 
               Case True : lbRunning = False 
               Case False : lbRunning = 
                         Not myProcess.CloseMainWindow 
            End Select 

            i += 1 
         End While

      'If it never closed gracefuly, force it out of memory 

      If lbRunning AndAlso Not myProcess.HasExited Then myProcess.Kill() 
      myProcess.Dispose() 
   Catch ex As System.ComponentModel.Win32Exception 
      'A process error throws a somewhat generic 

      'Win32Exception object when the source file is not found. 

      'Capture the Win32Exception object and examine it for a 

      'File Not Found state. If found, throw a more specific exception 

      Const ERROR_FILE_NOT_FOUND As Integer = 2 
      Select Case ex.NativeErrorCode 
         Case ERROR_FILE_NOT_FOUND : Throw New System.IO.FileNotFoundException 
         Case Else : Throw New Exception(ex.Message & " (PrintAdobePDF)") 
      End Select 
   Catch ex As Exception 
      'Bubble up the exception and concatinate the name of this procedure 

      'as a poor man's route trace 

      Throw New Exception(ex.Message & " (PrintAdobeFile)") 
   End Try 
End Sub

Conclusion

It's a rough work-around. It isn't pretty, but it gets the job done. I have been using this with success in a manufacturing environment with a heavy load, and have had no major issues with it. I haven't even gotten any complaints of memory leaks or slow report printing, which one might expect from such a back-alley solution as this.

The next version of RS is rumored to address this short-coming. I, for one, look forward to it! In the meantime, this approach is available if needed.

One final observation: There could be a better way! If you have a better technique, please let me know and I will amend this article.

History

• 1.00 - 12/04/2004
  • Initial version.
• 1.01 - 12/06/2004
  • Article tags cleaned up for easier reading.