his article will show you how to use Microsoft SQL Server’s Reporting Services Web service to install your reports and execute them from a smart client application.

If you use Reporting Services in a corporate environment, deploying reports and data sources is easy, just configure your test, staging, and live servers in Visual Studio when you create your project. When you’ve completed your report, select the appropriate solution configuration, right click your report, choose Deploy, and you’re done. The Web-based user interface is flexible and end users can run your reports easily.

But what if you want your reports to run from a smart client application rather than from the Web interface? You might want to use the flexibility of Reporting Services to automatically render your report as an Excel spreadsheet or Adobe PDF file without end users even knowing that Reporting Services is involved.

Or suppose you need to deploy reports to hundreds or thousands of external customers? You need a way to automate report deployment so that end users don’t need to figure out how to install your reports?so that installation happens automatically. The solution is to use the Reporting Services Web service from within your application.

Both the Report Designer component in Visual Studio and the Web-based Report Manager use the Reporting Web service, so you can use the Web service from your own applications. All the reporting engine’s capabilities are still available, including the ability to create users and roles, install reports and data sources, examine existing configuration settings, schedule reports, and execute reports interactively.

Creating the Web Service Proxy Class
As with all .NET Web service clients, you need to write or auto-generate a Web service client proxy class for the Reporting Services Web service. To auto-generate your Web proxy from within Visual Studio, right click the References item in the Solution Explorer, and select “Add Web Reference” (see Figure 1).

	?
Figure 1. Adding a Web Reference: To add a Web Reference, right click on References in the Solution Explorer and select the “Add Web Reference” item.

In the “Add Web Reference” dialog, enter the URL of your reporting server’s Web service?this is not the same URL you use to access the Web user interface. In a default setup, the URL is the reporting server name followed by /ReportServer/reportservice.asmx. For example, for a reporting services setup on your local development machine, use the URL http://localhost/ReportServer/reportservice.asmx.

When the Add Web reference dialog displays the Web service information, enter a Web reference name (I used “ReportServer” for the sample application), and click “Add Reference”. Visual Studio will automatically create a proxy class for you to use.

Web Service Proxy Setup
After creating an instance of your Web service proxy, make sure you set the Web service URL and Credentials properties:

       mobjReportServer = _         New ReportServer.ReportingService          mobjReportServer.Url = URL       mobjReportServer.Credentials = _         System.Net.CredentialCache.DefaultCredentials

Under some configurations of Windows and IIS, if you don’t set the proxy’s credentials, your Web service calls will fail, even if you are logged in as administrator. That’s because .NET does not pass the credentials of the current user to the Web service by default. You can use the NetworkCredential class rather than the DefaultCredentials class shown in the preceding code to force the Web service proxy to use the credentials of a specific user.

Web Services Sample 1?Report Installer
When creating a setup, you’ll want to add code to install your reports automatically. The sample code deploys reports using a Windows Forms user interface for simplicity’s sake, but for real applications, you will probably want to automate your server installation using a Visual Studio Setup project and the techniques described in the article “Building Custom Installer Classes in .NET” in combination with the code outlined in this article.

	?
Figure 2. The Report Installer Sample: The first sample application gathers the information required to deploy a smart-client report.

The sample displays a form that gets user input, and then calls three methods of the example Deployer class (see Figure 2).

The CreateFolder Method
The sample CreateFolder method creates a folder on your reporting server, checking to make sure it does not already exist. If it does exist, the method returns without doing anything. The ReportServerFolder argument is the user-defined name of the folder that will hold reports and data sources. You can use any valid folder name as specified by the Reporting Services documentation. Note that a valid folder name cannot include the following reserved characters: ? ; @ & = : + $ , * > < | . " .

The first step is to check for an existing folder with the same name by creating a SearchCondition object to specify the search criteria to use with the FindItems method. Search conditions are quite simple; they contain a property name, and the value you want to match. You can search for any standard property name visible on-screen in the report manager?in this case, Name or Description.

   srcFolderCond = New ReportServer.SearchCondition   With srcFolderCond     .Condition = ConditionEnum.Equals     .Name = "Name"     .Value = ReportServerFolder   End With      srchResults = mobjReportServer.FindItems _     ("/", BooleanOperatorEnum.And, _     New ReportServer.SearchCondition() {srcFolderCond})

The FindItems method requires a starting path (use a slash (/) to start from the root), an operator to indicate how to combine the search conditions, and an array of search conditions. Note that the FindItems method expects an array of SearchCondition objects?you can pass multiple SearchCondition objects within the array to create more complex queries. FindItems returns an array of CatalogItem objects.

To ensure that the search matches an existing folder rather than some other reporting services object type with the same name, the code loops through the returned CatalogItem array to check if one of the returned CatalogItem objects is a Folder.

      ' Check that found item(s) is a folder   For Each srchResult In srchResults     If srchResult.Type = ItemTypeEnum.Folder Then       blnExists = True     End If   Next

After determining that the folder does not already exist, you can create the folder using the CreateFolder method:

   mobjReportServer.CreateFolder(ReportServerFolder, "/", CreateStandardProperties)

The CreateFolder Web method takes three arguments: The name of the folder to create, the path for the new folder (which must start with the “/” character) and an array of user-defined properties. You can pass Nothing (or null in C#) for this argument if you don’t have any user-defined properties to add. The example adds an “Application” property by passing in the array of ReportServer.Property objects returned from a custom CreateStandardProperties function.

   Private Function CreateStandardProperties() _      As ReportServer.Property()     Dim objProperty As New ReportServer.Property     Dim objProperties() As ReportServer.Property = _         {objProperty}        objProperty.Name = "Application"     objProperty.Value = _         "DevX Reporting Services WebService Sample"        Return objProperties   End Function

If you place your application-specific reports and data sources in a folder, you will help users to understand that the reports belong to your application (and that they should leave them alone!).

The CreateDataSource Method
The CreateDataSource method creates a data source in the specified folder with the specified name and connection string, first checking whether the named data source already exists. If it does exist, the method deletes and then replaces it.

The code checks for an existing data source with the specified name in the same way as in the CreateFolder example shown earlier. After establishing that the named data source does not already exist, it creates a DataSourceDefinition object, and calls the CreateDataSource method to create the Data Source.

   dsNewDataSource = _      New ReportServer.DataSourceDefinition      With dsNewDataSource     .ConnectString = DataSourceConnectionString     .CredentialRetrieval = _       CredentialRetrievalEnum.Integrated     .Enabled = True     .EnabledSpecified = True     .Extension = "SQL"     .ImpersonateUser = False     .ImpersonateUserSpecified = True     .WindowsCredentials = True   End With      ReportServer.CreateDataSource( _     DataSourceName, "/" & ReportServerFolder, True,      dsNewDataSource, CreateStandardProperties)

The DeployReport Method
The DeployReport method deploys a report to the report server using the CreateReport Web method. You pass the contents of the report (.rdl) file to the CreateReport Web method as an array of bytes.

   Public Sub DeployReport(ByVal ReportFolder As String, ByVal ReportFilename As String)     Dim strmReportFile As System.io.FileStream     Dim definition() As Byte     Dim warnings() As ReportServer.Warning = Nothing     Dim warning As ReportServer.Warning     Dim strWarning As String     Dim strReportName As String     Dim strName as String        strName = _       System.IO.Path.GetFileNameWithoutExtension( _         ReportFilename)        strmReportFile = _         System.IO.File.OpenRead(ReportFilename)        ReDim definition(strmReportFile.Length)     strmReportFile.Read _         (definition, 0, strmReportFile.Length)     strmReportFile.Close()        warnings = _      mobjReportServer.CreateReport( _         strName, _         "/" & ReportFolder, _         True,          definition,          CreateStandardProperties)        End Sub 

Table 1 shows the arguments to the CreateReport method.

Table 1. CreateReport Arguments: The table contains a list of arguments to the CreateReport method along with a description of each.

Web Services Sample 2?Report Runner
Using the reporting services Web service, you can execute reports and render them as HTML, Excel, Web Archive (MHTML), PDF, TIFF, CSV, or XML. Each of the formats has its own limitations?for example, if your report has graphs, you have to do some extra work to render the HTML because you’ll need to read a stream for each individual graphic. The MHTML format works around this limitation by encapsulating the HTML and images into a single file, but will not render on all browsers. Alternatively, you can render the report as PDF, as shown earlier in this article, which also encapsulates the report and images into a single file, but displays in the ubiquitous Acrobat reader (see Figure 2).

	?
Figure 3. The Report Runner Sample Application: The Report Runner lets users select a report, run it, and save the results.

The Report Runner sample reads a list of folders from the report server, then reads the reports in the selected folder (for simplicity, it only reads the top-level folder names). The user can then select a path in which to save the report, and click run to render the report to PDF format and save it in the specified location.

Under the hood, the code calls the ReadFolders and ReadReports functions, which both use the FindItems Web method to check for an existing folder. Here, the application uses it to list all top-level folders.

The RunReport method calls the Render Web method to render the report as a PDF file. The method returns the report as an array of bytes that you can save directly to a file.

For simplicity, the sample report simply renders some text without reading any data, and requires only a single parameter. To run the report, you first create a ParameterValue object to pass in that parameter, called “Title”. Parameter names must match the parameter names specified within the report, and are case-sensitive.

   prmTitleParameter.Name = "Title"      prmTitleParameter.Value = "DevX Sample Report"

You can then use the parameter as input to the Render method.

   objReportResult = _      rptService.Render("/" & cboFolder.Text & "/" & cboReport.Text, _      "PDF", Nothing, "", _      New ReportServer.ParameterValue() {prmTitleParameter}, _      Nothing, Nothing, "", "", Nothing, warnings,       streamIDs)

Table 2 lists and explains the arguments to the Render method:

You can pass Nothing (or null) for the HistoryID, DeviceInfo, Credentials, ShowHide, Encoding, MimeType and ParametersUsed parameters. If you’re rendering to a format that does not return multiple streams or your report does not include images or graphs, you can pass Nothing for the StreamIDs argument, too. The Report and Format arguments are the only ones for which you must always pass values.

   objReportResult = rptService.Render( _      "/" & cboFolder.Text & "/" & cboReport.Text, _      "PDF", Nothing, "", _      New ReportServer.ParameterValue() _      {prmTitleParameter}, Nothing, Nothing, _      Nothing, Nothing, Nothing, warnings, Nothing)

The Warnings array returns an array of zero-to-many Warning objects. It’s a good idea to check the Warnings array, even though you will get an exception for critical errors.

   If Not warnings Is Nothing AndAlso _      warnings.Length > 0 Then      For Each objWarning In warnings         strWarningText = strWarningText & _         objWarning.Message & _         " (" & objWarning.Code & ")" & vbCrLf     Next     MsgBox(strWarningText, MsgBoxStyle.Exclamation)   End If

The Render method returns a byte array, which you can write to a file.

   With System.IO.File.Create( _      strTarget, objReportResult.Length)     .Write(objReportResult, 0, objReportResult.Length)     .Close()   End With

After writing the file you can open it in Acrobat by using the process object and the UseShellExecute option, which lets Windows figure out what application to run using the file extension of the file path passed as a parameter to the Start method.

   With New Process     .StartInfo.UseShellExecute = True     .Start(txtSaveAsPath.Text)   End With

Out of the box, Reporting Services seems totally focused on in-house corporate-style reporting, but using the Web service interface as described in this article opens up Reporting Services as a general-purpose reporting tool for “shrink-wrapped” application developers.