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"
Const LocalFile As String = "C:\TEMP.PDF"
DownloadWebFile(URL, LocalFile)
PrintAdobePDF(LocalFile)
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:
Sub DownloadWebFile(ByVal URL As String, ByVal DestFilename As String)
Dim WebFile As System.Net.WebRequest
Dim LocalFile As System.IO.FileStream
Dim Buffer(16384) As Byte
Dim BytesRead As Long
WebFile = System.Net.WebRequest.Create(URL)
WebFile.Credentials = System.Net.CredentialCache.DefaultCredentials
LocalFile = New IO.FileStream(DestFilename, IO.FileMode.Create)
With WebFile.GetResponse.GetResponseStream
Do
BytesRead = .Read(Buffer, 0, 16384)
LocalFile.Write(Buffer, 0, BytesRead)
Loop Until BytesRead = 0
.Close()
End With
WebFile = Nothing
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
myProcess.StartInfo.FileName = Filename
myProcess.StartInfo.WorkingDirectory =
New IO.FileInfo(Filename).DirectoryName
myProcess.StartInfo.CreateNoWindow = True
myProcess.StartInfo.WindowStyle =
ProcessWindowStyle.Hidden
myProcess.StartInfo.Verb = "Print"
myProcess.Start()
If Not myProcess.HasExited Then
myProcess.WaitForInputIdle(10000)
Dim i As Integer = 1
Dim lbRunning As Boolean = True
While lbRunning And i <= 20
System.Threading.Thread.Sleep(500)
Case myProcess.HasExited
Case True : lbRunning = False
Case False : lbRunning =
Not myProcess.CloseMainWindow
End Select
i += 1
End While
If lbRunning AndAlso Not myProcess.HasExited Then myProcess.Kill()
myProcess.Dispose()
Catch ex As System.ComponentModel.Win32Exception
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
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
- 1.01 - 12/06/2004
- Article tags cleaned up for easier reading.