Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using the SQL Reporting Services Web Service

Web Services

Using the SQL Reporting Services Web Service

by  jshurst  Posted    (Edited  )
I had another programmer ask me if it was possible to call a report from code (vb.net). She didn't want to display the report, but rather save it in a specific location in a specific format. I am pretty new to Reporting Services so I didn't know if this was possible. What I found was really cool.

Basically there are 2 ways to work with Reporting Services. The first is by using your browsers address bar. This along with the report manager provides a very easy way to interact with reporting services.

The second way is through the web service that is provided with reporting services. This allows access to the functions and methods that make up reporting services.

You will need to add a web reference to your project. The web reference for this is...
Code: 
http://yourserver/reportserver/reportservice.asmx?wsdl

In this FAQ I am going to show an example of how I used this service. I will show 2 basic functions that will save a report in a specific format in a specific location on the network and the second one to list all of the reports available on the server.

Code: 
Imports System.Web
Imports System.Web.Services
Imports System.Xml
Imports System
Imports System.IO
Imports System.Xml.Serialization

Public Class MISReportingService

    Enum Format
        PDF
        Excel
        HTML
        XML
        CSV
        Image
    End Enum

    Public Function SimpleRendering(ByVal NewFileName As String, ByVal NewFilePath As String, ByVal ReportPathAndName As String, ByVal RenderingFormat As String) As String
        'dimming the return value
        Dim Outcome As String
        Dim Extension As String

        Try
            'selecting which format is wanted, then assigning the extension
            Select Case RenderingFormat
                Case "PDF"
                    Extension = ".pdf"
                Case "Excel"
                    Extension = ".xls"
                Case "HTML"
                    'does not work for some reason.
                    Extension = ".html"
                Case "XML"
                    Extension = ".xml"
                Case "Image"
                    Extension = ".tiff"
                Case "CSV"
                    Extension = ".csv"
            End Select

            Dim rs As New ReportingService.ReportingService

            'setting the credentials to the machine that this application is running on
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials

            'Dimming all of the needed parameters
            Dim ResultStream() As Byte
            Dim StreamIdentifiers() As String
            Dim OptionalParam As String = Nothing
            Dim optionalParams As ReportingService.ParameterValue() = Nothing
            Dim optionalWarnings As ReportingService.Warning() = Nothing

            'rendering the report in the specific format
            ResultStream = rs.Render(ReportPathAndName, RenderingFormat, Nothing, "<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", Nothing, Nothing, Nothing, OptionalParam, OptionalParam, optionalParams, optionalWarnings, StreamIdentifiers)


            'creating a file to hold the stream with the correct extension from above
            Dim stream As FileStream = File.Create(NewFilePath + NewFileName + Extension)


            'writing the stream to the file
            stream.Write(ResultStream, 0, ResultStream.Length)
            stream.Close()

            'setting the return value
            Outcome = "Successful"
            Return Outcome

        Catch ex As Exception
            'setting the return value
            Outcome = ex.ToString
            Return Outcome
        End Try

    End Function

    Public Function ListAvailableReports() As DataTable

        Dim dtReports As New DataTable("Reports")
        Dim datarow As DataRow

        Try

            'creating an new instance of the web service
            Dim rs As New reportingservice.ReportingService

            'setting the credentials to the machine that this application is running on
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials

            'getting the available reports
            Dim items() As reportingservice.CatalogItem = rs.ListChildren("/", True)
            'defining a datacolumn
            Dim dcReportName As DataColumn = dtReports.Columns.Add("Report", GetType(System.String))
            'looping through all of the returned reports
            Dim cnt As Integer
            For cnt = 0 To items.Length - 1
                'creating a new row
                datarow = dtReports.NewRow
                'adding text to the row and column
                datarow("Report") = items(cnt).Name.ToString()
                'adding the row the the datatable
                dtReports.Rows.Add(datarow)

            Next cnt
            'returning the datatable
            Return dtReports

        Catch ex As Exception
            'returning the datatable with no rows(shows that you have an exception)
            dtReports = Nothing
            Return dtReports
        End Try

    End Function
End Class

Ok, so now you have some functions, now what? Well, I put this in a DLL so it could be shared. Here is how I call the function...

Code: 
        Dim location As String = "\\whateverserver\pub\_deletedeveryfriday\Jeremy\"
        Dim NewFileName As String = "Testing"
        Dim reportName As String = "/PAWS/rptAnimalsInShelterCount"

        'create the file using the .dll

        Dim outcome = SQL.SimpleRendering(NewFileName, location, reportName, SQL.Format.Image.ToString)
        Label1.Text = outcome & " " & [b]SQL.Format.Image.ToString[/b]

Notice the bolded line. This line gives you the enumerated options of the function. This helps make sure that the file is in the correct format when you pass it over. Be sure to put ".ToString" after the enueration, otherwise it will give you a value (and we want the actual word).

If you want to use the list function then do it like so...

Code: 
'lists available reports
        Dim outcome As DataTable
        outcome = SQL.ListAvailableReports
        cmbReports.DataSource = outcome
        cmbReports.DisplayMember = "Report"

One thing I forgot to mention...
If you are planning on using this in a dll you will need to reference the dll and create a new instance of the class. I called mine "SQL" and my dll is named "MISReportingService." So I put this at the top of the page..

Code: 
Dim SQL As New MISReportingService.MISReportingService

Hope this helps.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top