Section 14.2. Integrating Reports into Applications

14.2. Integrating Reports into Applications

SSRS lets you integrate reports into applications in three different ways:

Issue URL -based requests to navigate, access, and view reports. This is the most efficient way to render reports, because URL-based requests directly access the server. URL-based reports are also easy and efficient to implement. You can use URL-based requests in both web and Windows applications. In a Windows application, launch a web browser in a separate window or display the report in the web browser control on the form.
Use the freely distributable report viewer control for Visual Studio 2005 to embed Reporting Services functionality into either a Windows or web application.
Access the report server through Report Server web service using SOAP over HTTP as the communication interface between the client and the report server. In addition to providing the capabilities of URL-based requests, the web service exposes report management functionality that is not available through URL access . This includes content, subscription, and data-source management. You can use the SOAP API in both Windows and web applications.

A typical enterprise application uses more than one of these methods to meet reporting needs. The three methods are discussed in more detail in the following subsections.

14.2.1. URL Access

The URL request contains information identifying the report server to use, as well as parameters that are processed by the report server and that control the formatting and rendering of the report. The parameters, parameter prefixes, and combination of supplied parameters in the URL determine how the report server handles a specific request. Report server URLs follow W3C/IETF formatting guidelines:

Parameters are separated by an ampersand (&).
Name-value pairs are separated by an equals sign (=).
The order of parameters is not significant.

For example, enter the URL http://localhost/ReportServer?/AdventureWorksSampleReports/SalesOrderDetail&rs:Command=Render. The report shown in Figure 14-2 is rendered in the browser.

Figure 14-2. Sales order detail report

The information in the URL specifies that you want to do the following:

Use the local report server.
Access a report in the report server virtual root named ReportServer.
Access the report named AdventureWorks Sample Reports/Sales Order Detail.
Render the report in the default rendering format for the browser.

The report server URL syntax is:

     protocol://server/virtualroot?[/pathinfo]&[prefix:]param=         value[&prefix:param=value]...n]

where:

protocol

The URL protocol, usually http or https.

server

The name of the computer running the report server.

virtualroot

The name of the virtual root of the report server.

pathinfo

The full pathname of the item being accessed from the report server database. The item can be one of four types, described in Table 14-1.

Table 14-1. URL item types
Item type	Description
Data Source	Displays the data source if the user is authenticated with `Read Contents` permission for the data source
Folder	Returns a generic folder-navigation page containing links to subfolders, reports, data sources, and resources in the folder
Report	Renders and returns the specified report
Resource	Returns the specified resource for a report

prefix

Accesses a specific process running in the report server. If not specified, the parameter is processed as a report (rs) parameter. Table 14-2 describes the available prefixes.

Table 14-2. URL parameter prefixes
Parameter prefix	Description
rc	Specifies device-information settings, including those for reports targeted for the HTML Viewer. HTML device-information settings are described in Table 14-5. The device is specified using the report server `rs:Format` parameter. Valid values depend on the rendering extensions installed on the report server. Common devices include HTML 3.2, HTML 4.0, CSV, Excel, Image (TIFF), MHTML, PDF, and XML.
rs	Specifies configuration settings to the report server.
dsu	Specifies a username to access a data source.
dsp	Specifies a password to access a data source.

param

The name of the parameter. Parameters control how reports are processed and rendered.

value

The value of the parameter.

Report server parameters are prefixed with rs and are used to control report processing. Table 14-3 describes report server parameters.

Table 14-3. Report server parameters
Parameter	Description
Command	Specifies the type of request made to the report server. Table 14-4 describes possible values. If the `rs:Command` parameter is not specified, the report server evaluates the URL and selects the appropriate command value. Specifying the `rs:Command` value will improve performance. Only one `rs:Command` parameter can be specified in a URL.
Format	Specifies the format for rendering the report. The `ListExtensions( )` method of the `ReportingService` class (in the `ReportService` web service) returns the rendering extensions installed on a report server instance. The following code snippet shows how: ReportingService rs = new ReportingService( ); rs.Credentials = System.Net.CredentialCache.DefaultCredentials; Extension[] re = rs.ListExtensions(ExtensionTypeEnum.Render);
ParameterLanguage	Specifies the language for URL parameters that is independent of the browser language. The value is a culture value, such as `en-US`. The default is the browser language.
Snapshot	Renders the report based on a report-history snapshot. The value of this parameter must be a valid snapshot ID in the ISO 8601 standard format `YYYY-MM-DDTHH:MM:SS`.

Table 14-4 describes the rs:Command parameter values .

Table 14-4. rs:Command parameter values
Value	Description
GetDataSourceContents	Displays the value of a specified data source as XML. This parameter value corresponds to calling the `GeTDataSourceContents( )` web-service method.
GetResourceContents	Renders a resource and displays it as HTML. This parameter value corresponds to calling the `GetresourceContents( )` web-service method.
ListChildren	Displays children for the item passed in the URL. This parameter value corresponds to calling the `ListChildren( )` web-service method.
Render	Renders the specified report. This parameter value corresponds to calling the `Render( )` web-service method.

For example, entering this URL in your browser will display all child items of the AdventureWorks Sample Reports folder: http://localhost/reportserver?/AdventureWorksSampleReports&rs:Command=ListChildren.

HTML device-information settings control how the report is rendered in HTML format. HTML device-information settings, and other device-information settings, are prefixed with rc. Table 14-5 describes HTML device-information settings. See Microsoft SQL Server 2005 Books Online for details about settings for other devices.

Table 14-5. HTML device-information settings
Parameter	Description
BookmarkID	The bookmark ID to jump to in the report.
DocMap	Specifies whether the report document map is visible. The value `true` displays the document map and the value `false` hides it. The default value is `TRue`.
DocMapID	The document map ID to scroll to in the report.
EndFind	The last page in the report to search. The default value is the current page. Use this parameter together with the `StartFind` parameter.
FallbackPage	The number of the page to display if a search or document map selection fails. The default is the current page.
FindString	The text to search for in the report. The default value is an empty string.
GetImage	The icon for the HTML Viewer user interface.
HTMLFragment	Specifies whether an HTML fragment is created instead of a full HTML document. The HTML fragment omits the `<HTML>` and `<BODY>` HTML tags. The default value is `false`.
Icon	The icon for the specified rendering extension.
JavaScript	Specifies whether JavaScript is supported in the rendered report.
LinkTarget	The target for hyperlinks in the report. This value can be a specific window or frame, a new window by setting the parameter value to `_blank`, or the values `_self`, `_parent`, and `_top`.
Parameters	Specifies whether the parameters area of the toolbar is visible. The value `true` displays the parameters area and the value `false` hides it. The default value is `TRue`.
ReplacementRoot	The path used for prefixing the value of the `HRef` attribute of `A` elements in the HTML report returned by the server. By default, the server provides this value.
Section	The page in the report to display. The default value is `1`. The last page is displayed if the parameter value is greater than the number of pages in the report.
StartFind	The first page in the report to search. The default value is the current page. Use this parameter together with the `EndFind` parameter.
StreamRoot	The path used for prefixing the value of the `src` attribute of the `IMG` element in the HTML report returned by the server. By default, the server provides this value.
StyleSheet	The stylesheet to apply to the HTML Viewer.
StyleStream	Specifies whether styles and scripts are created as a separate stream instead of in the document. The value `TRue` creates a separate stream and the value of `false` puts styles and scripts in the document. The default value is `false`.
Toolbar	Specifies whether the toolbar is visible. The value `true` displays the toolbar for rendering formats that support a toolbar. The value `false` hides the toolbar. The default value is `true`.
Type	The short name of the browser as defined in `browscap.ini`.
Zoom	The report zoom value. The value can be a percent integer or the string constant `Page Width` or `Whole Page`. The default value is the percent integer `100`.

You can use a report server parameter to control the rendering format for the report. The URL http://localhost/reportserver?/AdventureWorksSampleReports/CompanySales&rs:Command=Render&rs:Format=IMAGE renders the report as a TIFF image. The URL http://localhost/reportserver?/AdventureWorksSampleReports/CompanySales&rs:Command=Render&rs:Format=XML renders the report as an XML file.

HTML device-information settings control how the report is displayed in a browser. The URL http://localhost/reportserver?/AdventureWorksSampleReports/CompanySales&rs:Command=Render&rc:Toolbar=false&rc:Zoom=200 renders the report at twice the default size without a toolbar.

Report parameters are passed in the URL as name-value pairs separated by an equals sign (=) . Pass a null parameter using the syntax parameterName:isNull=true. Note that report parameters are not prefixed. For example, the report Employee Sales Summary takes three parameters: ReportMonth (integer), ReportYear (integer), and EmpID (string). When you run the report normally, the RDL populates the employee drop-down list using the DataSet named SalesEmps. This corresponds to the following query:

     SELECT E.EmployeeID, C.FirstName + N' ' + C.LastName AS Employee     FROM HumanResources.Employee E     INNER JOIN Sales.SalesPerson SP ON E.EmployeeID = SP.SalesPersonID     INNER JOIN Person.Contact C ON E.ContactID = C.ContactID     ORDER BY C.LastName, C.FirstName

We need to run this query to determine the employee ID for each employee. Jillian Carson has employee ID 277. The URL:

http://localhost/reportserver?/AdventureWorksSampleReports/EmployeeSalesSummary&rsCommand=Render&EmpID=277&ReportMonth=7&ReportYear=2002

sets the report parameters to return the sales summary report for Jillian Carson for July 2002.

The preceding examples show how to request a report using a URL. This is equivalent to an HTTP GET method . You can also request a report using an HTTP POST method, which transfers the parameter name/value pairs in the HTTP header instead of the URL. Using an HTTP POST method overcomes the maximum allowable URL length limit in cases where a parameter list is long, and is also more secure because the user cannot directly modify the parameter names and values. The following HTML returns the same sales summary report for Jillian Carson for July 2002 as in the preceding example but uses an HTTP POST method:

     <form        action="http://localhost/reportserver?/AdventureWorks Sample Reports/       Employee Sales Summary" method="post" target="_self">         <input type="hidden" name="rs:Command" value="Render"/>         <input type="hidden" name="EmpID" value="277"/>         <input type="hidden" name="ReportMonth" value="7"/>         <input type="hidden" name="ReportYear" value="2002"/>         <input type="submit" value="Render"/>     </form>

14.2.2. Report Viewer Control

The report viewer control is a freely distributable control that ships with Visual Studio 2005. The control is called ReportViewer and is in the Data section of the Toolbox in the Visual Studio 2005 IDE.

Use the control by dragging it onto either a Windows form or a web form surface. The ReportViewer control menu prompts you to either "Choose Report" from a drop-down list or "Design a new report." Click "Design a new report" to bring up Report Designer. Select <Server Report>, and you are prompted for the Report Server Url and the Report Path. Fill in these values and run the applicationthe report appears in the control.

For example, set the value of Report Server Url to http://localhost/reportserver and the Report Path to /AdventureWorks Sample Reports/Company Sales. Run the applicationif you created a Windows Forms application, the resulting output will look like Figure 14-3.

Figure 14-3. AdventureWorks 2002-2003 company sales report

The report server and report path can be specified at runtime through the properties of the ServerReport object exposed by the ServerReport property of the control, as shown in the following code snippet:

     reportViewer1.ServerReport.ReportServerUrl =         new Uri(@"http://localhost/reportserver");     reportViewer1.ServerReport.ReportPath =         @"/AdventureWorks Sample Reports/Company Sales";

14.2.3. Report Server Web Service

SSRS provides full access to report server functionality through Report Server web services . The web service provides methods and properties for both report executioncontrolling the processing and rendering of reportsand report management.

You can develop Reporting Services applications that use Report Server web services with the .NET Framework, by using the Reporting Services script environment and the rs utility (rs.exe), or by using any development tools capable of invoking SOAP methods. This section discusses only the first approach. For information on the other two approaches, see Microsoft SQL Server 2005 Books Online.

These are the steps you follow to create an application that uses Report Server web services (you'll build a real example shortly):

Create a new Windows application in Visual Studio 2005.
Using Solution Explorer, add a web reference to the ReportService web service from the local machine. Do this by right-clicking the Web References node in Solution Explorer and selecting Add Web Reference from the context menu. This displays the Add Web Reference dialog box, Start Browsing for Web Services page, shown in Figure 14-4.
Click the "Web services on the local machine" link to display the dialog box shown in Figure 14-5.
Click the ReportService service to display the dialog box shown in Figure 14-6.
Specify ReportService in the Web reference name listbox and click the Add Reference button.
Add a using directive to the top of the class for the ReportService web service. For example, if you named the application MyApp, the using directive would be as follows:
```
     using MyApp.ReportService; 
```
Create an instance of the proxy class, as shown in the following snippet:
```
     ReportingService rs = new ReportingService(  ); 
```
Pass authentication credentials to the web service. The following code passes default Windows credentials:
```
     rs.Credentials = System.Net.CredentialCache.DefaultCredentials; 
```
Figure 14-4. Start Browsing for Web Services page
Call report server web service methods from your application. Once the proxy is instantiated and the caller is authenticated, this is done in the same way as with any .NET Framework method.

As an example, let's build a Windows Forms application that presents a drop-down list of the reports available in the AdventureWorks Sample Reports folder, as shown in Figure 14-7.

When you select a report from the list and click the Render button, the report is saved to a web page (HTML) file. Note that if you select a report that requires parameters, such as Employee Sales Summary, a ReportParameterValueNotSetException is raised. The ParameterValue array argument is always passed as null to keep this example simple.

To build the example, create a Windows application named ReportServerWebService. Add a listbox to the form and name it reportListBox. Add a button to the form and name it renderButton. Add a web reference to the ReportService web service and give it the name ReportService. Copy the following code into the form:

     using System;     using System.Collections.Generic;     using System.ComponentModel;     using System.Data;     using System.Drawing;

Figure 14-5. Web Services on the Local Machine page

     using System.Text;     using System.Windows.Forms;     using System.IO;     using ReportServerWebService.ReportService;     namespace ReportServerWebService     {         public partial class MainForm : Form         {             private string reportPath = @"/AdventureWorks Sample Reports";             public MainForm(  )             {                 InitializeComponent(  );             }             private void MainForm_Load(object sender, EventArgs e)             {                 ReportingService rs = new ReportingService(  );                 rs.Credentials = System.Net.CredentialCache.DefaultCredentials;                 CatalogItem[] cis = rs.ListChildren(reportPath, false);                 foreach (CatalogItem ci in cis)                 {                     if(ci.Type == ItemTypeEnum.Report)                         reportListBox.Items.Add(ci.Name);                 }             }

Figure 14-6. "ReportingService" Description page

Figure 14-7. AdventureWorks sample reports viewer

             private void renderButton_Click(object sender, EventArgs e)             {                 if (reportListBox.SelectedIndex >= 0)                 {                     string reportName =                         reportListBox.Items[reportListBox.SelectedIndex].ToString(  );                     SaveFileDialog sfd = new SaveFileDialog(  );                     sfd.Filter = "Web page format (*.html)|*.html";                     sfd.FileName = reportName;                     if (sfd.ShowDialog(  ) == DialogResult.OK)                     {                         string renderFileName = sfd.FileName;                         ReportingService rs = new ReportingService(  );                         rs.Credentials = System.Net.CredentialCache.DefaultCredentials;                         string reportFullName = reportPath + "/" + reportName;                         string format = "HTML4.0";                         string historyID = null;                         string deviceInfo = null;                         ParameterValue[] pv = null;                         DataSourceCredentials[] dsc = null;                         string showHideToggle = null;                         string encoding;                         string mimeType;                         ParameterValue[] parameterUsed = null;                         Warning[] warning = null;                         string[] streamIds = null;                         byte[] report;                         try                         {                             report = rs.Render(reportFullName, format, historyID,                                 deviceInfo, pv, dsc, showHideToggle, out encoding,                                 out mimeType, out parameterUsed, out warning,                                 out streamIds);                             using (FileStream fs = File.OpenWrite(renderFileName))                                 fs.Write(report, 0, report.Length);                         }                         catch (Exception ex)                         {                             MessageBox.Show(ex.Message, "Error",                                 MessageBoxButtons.OK, MessageBoxIcon.Error);                         }                     }                 }             }         }     }

Running the example launches the application window with a listbox containing the available reports. Select the Company Sales report and click the Render button. The report is saved as Company Sales.html in the location you specify. An excerpt from this file follows:

     <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">     <html>     <head>     <title>     Company Sales     </title>     <META http-equiv="Content-Type" content="text/html; charset=utf-8">     <META http-equiv="Content-Style-Type" content="text/css">     <META http-equiv="Content-Script-Type" content="text/javascript">     ...     <IMG BORDER="0" src="/books/1/111/1/html/2/http://localhost/ReportServer?%2f     AdventureWorks+Sample+Reports%2fCompany+Sales&amp;     rs:Command=Get&amp;rc:GetImage=9.00.1399.00TogglePlus.gif"/></a>     &nbsp;Bikes</DIV></TD>     <TD ><DIV >$26,664,534</DIV></TD>     <TD ><DIV >$35,199,346</DIV></TD></TR>     <TR VALIGN="top"><TD WIDTH="0" style="HEIGHT:6.35mm"></TD>     <TD  COLSPAN="2"><DIV >     <a href="http://localhost/ReportServer?%2f     AdventureWorks+Sample+Reports%2fCompany+Sales&amp;     rc%3aZoom=200&amp;rc%3aSection=0&amp;rs%3aFormat=HTML4.0&amp;     rs%3aShowHideToggle=368&amp;rs%3aSnapshot%3aisnull=True">     <IMG BORDER="0" src="/books/1/111/1/html/2/http://localhost/ReportServer?%2f     AdventureWorks+Sample+Reports%2fCompany+Sales&amp;rs:Command=Get&amp;     rc:GetImage=9.00.1399.00TogglePlus.gif"/></a>     &nbsp;Accessories</DIV></TD>     <TD ><DIV >$93,797</DIV></TD>     <TD ><DIV >$595,014</DIV></TD></TR></TABLE></TD></TR>     <TR><TD style="HEIGHT:1.59mm"></TD></TR></TABLE></DIV></TD>     <TD WIDTH="100%" HEIGHT="0"></TD></TR><TR>     <TD WIDTH="0" HEIGHT="100%"></TD></TR></TABLE></DIV>     </body>     </html>

Figure 14-8 shows the report when opened in Internet Explorer.

The Render( ) method takes arguments that identify the report and specify how to render the results to a byte stream that can be saved to a file or displayed.

Device-information settings are passed to the Render( ) method as a <DeviceInfo> XML element. HTML device-information settings are described in Table 14-5, earlier in the chapter. For example, to zoom the report 200%, set the deviceInfo argument of the Render( ) method to the following:

     <DeviceInfo>         <Zoom>200</Zoom>     </DeviceInfo>

Figure 14-8. Results for rendering a report to a file example

Section 14.2. Integrating Reports into Applications

14.2. Integrating Reports into Applications

14.2.1. URL Access

Figure 14-2. Sales order detail report

Table 14-1. URL item types

Table 14-2. URL parameter prefixes

Table 14-3. Report server parameters

Table 14-4. rs:Command parameter values

Table 14-5. HTML device-information settings