Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft SQL Server: Reporting Services FAQ

Reference Material

Working with SQL Reporting Services Web Service by jshurst
Posted: 15 Jun 05

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...



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.


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

Public Class MISReportingService

    Enum Format
    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

            '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)

            '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


            '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

            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...


        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 & " " & SQL.Format.Image.ToString

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...


'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..


Dim SQL As New MISReportingService.MISReportingService

Hope this helps.

Back to Microsoft SQL Server: Reporting Services FAQ Index
Back to Microsoft SQL Server: Reporting Services Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close