SQl RS 2005 - Automate export to pdf after user parameter entered

Question

Hey,

I have a report that requires the user to enter their ticket number, then they click view, and it generates. Pretty standard. What I would like to do is have the report automatically export to a pdf when they click view. Does anyone know if this is possible and how I would approach the matter?

Thanks for any input.

Answer 1

I have done this ASP.NET but not VB.NET. What it involved was making a request behind the scenes to the report server using URL Access where you could specify the render type of report (in this case PDF) and my code-behind would download and save the file to the web server, then server it back up to the user as a PDF file. All the user had to do was fill in their parameters and hit the submit button.

I would imagine you could perform pretty much the same steps I followed all the way up until you need to serve the file, where in a forms environment you wouldn't need to give them the download prompt since it was already on the machine and all you would have to do is open the file (which should load in your default PDF viewer).

UPDATE: Here is some of the code, most of it was based off of a CodeProject tutorial but I cannot remember how to get to it. Some minor modifications have been made:

Public Sub ServeReport(ByVal URL As String, _
    ByVal Directory As String, ByVal Filename As String)

    Dim f As New FileIOPermission(PermissionState.None)
    Dim fs As FileStream

    If Not System.IO.Directory.Exists(Directory) Then
        System.IO.Directory.CreateDirectory(Directory)
    End If

    DownloadWebFile(URL, Directory & Filename)    

    fs = File.Open(Directory & Filename, FileMode.Open)

    Dim bytBytes(fs.Length) As Byte
    fs.Read(bytBytes, 0, fs.Length)
    fs.Close()

    Response.AddHeader("Content-disposition", _
               "attachment; filename=" & Filename)
    Response.ContentType = "application/octet-stream"
    Response.BinaryWrite(bytBytes)

    f.AllLocalFiles = FileIOPermissionAccess.AllAccess
    File.Delete(Directory & Filename)
End Sub

Here is the DownloadWebFile sub which will actually make the report server request and download the file and save it to the local disk.

Public Shared 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

Finally, here is an example of the usage:

Dim URL As String
URL = reporturl & "&rs%3aCommand=Render&rs%3AFormat=PDF"

ServeReport(URL, "C:\Temp\", "MyReport.PDF")