SQL Reporting Web Services

I’ve had to use the SQL Server Reporting Services Web Services for a number of projects recently. Just to recap, if you don’t want to or can’t use the url based report access feature, you can render reports using a call to the reportService web service.

Here’s the code that will do that for you;

‘now we are going to try and create the report
‘using rs.web services
Dim rs As New trstpw1.reportservice.ReportingService

‘setup the ntlm login credential
Dim myCred As New NetworkCredential(“DomainUser”,”DomainPassword”, “SQLDomain”)
Dim myCache As New CredentialCache

myCache.Add(New Uri(“ReportServerURL”), “NTLM”, myCred)
rs.Credentials = myCache

Dim format As String = “PDF”
Dim result As Byte() = Nothing
Dim credentials As DataSourceCredentials() = Nothing
Dim warnings As Warning() = Nothing
Dim streamIDs As String() = Nothing
Dim sh As New SessionHeader
rs.SessionHeaderValue = sh

‘we have 1 parameter, which is the type of letter

Dim params(0) As trstpw1.reportservice.ParameterValue
params(0) = New trstpw1.reportservice.ParameterValue
params(0).Name = “LETTER”
params(0).Value = Me.dlLetters.SelectedItem.ToString

‘first we open the envelope
result = rs.Render(“ReportServerReportPath” + “ENV”, format, Nothing, Nothing, params, credentials, Nothing, “”, “”, Nothing, warnings, streamIDs)

Dim estream As FileStream = File.Create(Request.PhysicalApplicationPath + “\PDF\” + “ENV-” + Me.dlLetters.SelectedItem.ToString.ToUpper + “.pdf”, result.Length)

estream.Write(result, 0, result.Length) estream.Close()

‘now we do the report
result = rs.Render(“ReportServerReportPath” + Me.dlLetters.SelectedItem.ToString.ToUpper, format, Nothing, Nothing, Nothing, credentials, Nothing, “”, “”, Nothing, warnings, streamIDs)

Dim stream As FileStream = File.Create(Request.PhysicalApplicationPath + “\PDF\” + Me.dlLetters.SelectedItem.ToString.ToUpper + “.pdf”, result.Length)

stream.Write(result, 0, result.Length)
stream.Close()