Creating PDF (or any other file type) from SQL Reporting Services using .WSDL (VS2012) Part 2

If you have been following along in Part 1 you know that by now you will have a web service ready to be consumed in your project. Let’s continue on with this example and create a report that is hosted at Arvixe. (Concepts apply to most vendors). One thing to keep in mind with Arvixe this that testing your code report link is different than when you deploy it. I will get to that in a minute but let’s continue on.

Right click on your project and select ..”Add New Item” (figure 1)


Figure 1

Select Generic Handler and name the file something like ….”SSRS.ashx

Copy the code below and replace the existing ProcessRequest. (I will discuss each part later)

Import the following;

Imports System.Net
Imports System.Security.Principal
Imports System.IO
Imports System
Imports System.Web.Configuration

Import the web reference to the web service (you added earlier (Part 1))

Imports XXX.arvixe.com

Copy the code below and replace the default ProcessRequest. Read comments for detail description of this code.;

Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim rs As ReportExecutionService = New ReportExecutionService
        Dim execInfo As ExecutionInfo = New ExecutionInfo()
        Dim execHeader As ExecutionHeader = New ExecutionHeader()

        Dim historyID As String = Nothing
        'This needs to be the full path of the reports in SSRS.
		'On Arvixe it would be something like /UserName
		Dim reportPath As String = "{Report Server Path]"

		'What you plan to export this as
		Dim extension As String = "XLS"
        Dim mimeType As String = "ms-excel"

		Dim encoding As String = ""
        Dim warning As Warning() = Nothing
        Dim streamID As String() = Nothing
        Dim result() As Byte = Nothing

		'This will allow you to change the value for this upon deployment
        Dim webservice As String = WebConfigurationManager.AppSettings("XXX.arvixe.com.ReportExecution2005")
        rs.Url = webservice & "?wsdl"

        'This is very important.  You need to pass the credentials so that Arvixe will let you login and run the report
		Dim cache As CredentialCache = New CredentialCache()
		'Add a NetworkCredential instance to CredentialCache.
        'Negotiate for NTLM or Kerberos authentication.
        cache.Add(New Uri(rs.Url), "Negotiate", New NetworkCredential("[ARVIXE LOGIN NAME TO REPORT SERVER", "PASSWORD TO THE REPORT SERVER", "SERVER NAME ie. xxx.arvixe.com"))

        'Assign CredentialCache to the Web service Client Proxy(myProxy) Credetials property.
        rs.Credentials = cache

		'This section contains the report parameters.  They must match exactly as they are in your report

		'Dim the number of Report Parameters.
		'This is my example for a report that I did for a client of mine with James River Webs, Inc.
        Dim ReportParams(2) As ParameterValue

        ReportParams(0) = New ParameterValue()
        ReportParams(0).Name = "pBeginDate"
        ReportParams(0).Value = context.Request.QueryString("BeginDate").ToString

        ReportParams(1) = New ParameterValue()
        ReportParams(1).Name = "pEndDate"
        ReportParams(1).Value = context.Request.QueryString("EndDate").ToString

        ReportParams(2) = New ParameterValue()
        ReportParams(2).Name = "pLocation"
        ReportParams(2).Value = context.Request.QueryString("Location").ToString

        Dim ReportHistoryParameters As ParameterValue() = Nothing

        Dim devInfo As String = "False"

        '  SessionId = rs.ExecutionHeaderValue.ExecutionID
        'Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID)

        rs.ExecutionHeaderValue = execHeader
        execInfo = rs.LoadReport(reportPath, historyID)
        rs.SetExecutionParameters(ReportParams, "en-us")
        result = rs.Render("Excel", devInfo, extension, mimeType, encoding, warning, streamID)

        HttpContext.Current.Response.ClearContent()

        ' Add the file name and attachment,
        ' which will force the open/cance/save dialog to show, to the header

		'Additional items can be sent back in the header to change the way it will return.
		'This will export to an Excel Document but could be a PDF.  See below for exporting to PDF
        HttpContext.Current.Response.AddHeader("Pragma", "public")
        HttpContext.Current.Response.AddHeader("Content-Description", "File Transfer")
        HttpContext.Current.Response.AddHeader("X-Download-Options", "noopen") ' // IE7 & IE8?
        HttpContext.Current.Response.AddHeader("X-Content-Type-Options", "nosniff") ' // IE8 bonus?
        HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary")

		'The line below is where you set the actual file name that the user will be prompted to save.
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=rpt_PartSummary.xls")

        HttpContext.Current.Response.AppendHeader("content-length", result.Length.ToString())
        HttpContext.Current.Response.ContentType = "application/excel"
        HttpContext.Current.Response.BinaryWrite(result)
        HttpContext.Current.Response.End()
        HttpContext.Current.Response.Flush()

End Sub

This is it. Now just wire this up to a button or image click on a page and you are done. I know that there are other things you can do which include using AJAX or jquery to call this as a web api or use a web service yourself, but this hopefully gives you a real good example how to code a SSRS using Arvixe Servers.

If you need an example of how to put the result in its own window or you have a better way to do this then please drop me a note. If you want to export to pdf then just change the HttpContext.Current to the following.

	    result = rs.Render("PDF", devInfo, extension, mimeType, encoding, warning, streamID)

        Response.ClearContent()
        Response.AppendHeader("content-length", result.Length.ToString())
        Response.ContentType = "application/pdf"
        Response.BinaryWrite(result)
        Response.End()
        Response.Flush()

	

VERY IMPORTANT:: When you deploy your project to the Arvixe servers you must change your web.config app setting line from the server location to localhost. Here is an example

<appSettings>
  <add key="com.arvixe.XXX.ReportExecution2005" value="<a href="http://localhost/ReportServer_SQLEXPRESS/ReportExecution2005.asmx">http://localhost/ReportServer_SQLEXPRESS/ReportExecution2005.asmx</a>" />
 </appSettings>

	


If you do not then you will get an error that says something about not having valid access or permission is denied.

Looking for quality ASP Web Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , , , , | Posted under ASP .NET 3.5, MSSQL | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


− 1 = 4

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>