Browse DevX
Sign up for e-mail newsletters from DevX


Automating Smart-Client Report Deployments : Page 4

Building reports is often easier than getting them into users' hands and making sure they're used correctly, but now you can use the SQL Server Reporting Services Web service to automate deployment and control execution of reports.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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:

Argument Description
Report The name of the report to run, including the path. The path must start with the "/" character.
Format The file format for the rendered report. The standard formats are HTML3.2, HTML4.0, HTML5, XML, CSV, and PDF.
HistoryID You can use the HistoryID parameter to re-render a previously stored history snapshot, or pass Nothing (or null) to use the current database data. You can get more detailed information here.
DeviceInfo An XML string used to control rendering. Options are specific to each rendering format specified in the Format argument. Pass an empty string if you have no options to pass in. You can get more detailed information here.
Parameters An array of ParameterValue objects. These must correspond to the parameters specified in your report.
Credentials If your report's data sources require specific usernames and passwords, you can pass in an array of DataSourceCredentials objects (pass Nothing/null if you are using integrated security).
ShowHide Use the ShowHide to specify the name of the show/hide toggle button. If you are not planning to access the toggle button from script, you can pass Nothing or null for this value.
Encoding This property returns a string representing the encoding used to render the report.. The HTML formats generate UTF-8 by default, and rendering to the PDF format will leave this string blank..
MimeType Returns a string representing the MIME type of the report. For the PDF format, the MIME type will be application/pdf.
ParametersUsed If you use the HistoryID argument, it returns the parameters that were used (from the history snapshot).
Warnings An array of Warning objects that represent any warnings encountered rendering the report.
StreamIDs If you use graphs or images in a format type that does not embed them (for example, HTML) this argument contains an array of Stream ID's you can use to render them. Refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.asp for more information.

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.

Anthony Glenwright is the Product Development Manager at Inventua for TrueTeam, a collaboration tool which manages and integrates the workflows of running a successful software organization. You can contact him through his Web site at www.inventua.com or directly.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date