Using Reporting Services Without the Report Manager | MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

The Report Manager provides a nice interface for finding and executing reports. However, the Report Manager is not always the best way to deliver a report to your users. Perhaps the user is browsing your website or using a custom application and needs to view a report. In these situations, it does not make sense to force the user to jump to Report Manager and begin navigating folders. We want to deliver the report to the user right where they are. In this section, we explore several ways to do just that.

URL Access

One way to execute a report without using Report Manager is through URL access. URL access allows a browser or a program capable of issuing HTTP requests to specify a URL and receive a report in the HTML report viewer. This URL can be built into a standard HTML anchor tag to allow a report to be displayed with one mouse click.

Basic URL Access

The basic URL used to access a report has two parts. The first part is the URL of the Report Server web service. In a default installation, this is

 http://{computername}/ReportServer

where {computername} is the name of the computer hosting the Report Server. This is followed by a question mark and the path through the Reporting Services virtual folders to the report you want to execute. The Home folder is the root of this path, but it’s not included in the path itself. The path must begin with a forward slash (/).

Let’s try an example. We can execute the Invoice-Batch Number Report for the Axelburg office. This report is in the Axelburg folder inside the Galactic Delivery Services folder.

Note

In the examples used throughout the rest of this chapter, we assume Reporting Services is installed on your computer. The localhost name is used to access IIS information on this computer. If you have Reporting Services installed on a different computer, substitute the name of that computer in place of localhost in the following examples.

Start Internet Explorer.

Enter the following URL in the address bar:

 http://localhost/ReportServer?/Galactic Delivery Services/Axelburg/                                            Invoice-Batch Number Report

Click Go. The Invoice-Batch Number Report appears in the browser inside the Report Viewer.

Note

When your URL is submitted, it is URL encoded. Some of the characters in your URL may be replaced by other characters or by hexadecimal strings such as %20. This ensures the URL can be interpreted correctly when it is sent to the web server.

As with Report Manager, Windows Integrated security is being used when a user executes a report through URL access. The user must have rights to execute the report; otherwise, an error results. However, because the user is not browsing through the folder structure to get to the report, the user does not need to have any rights to the folder containing the report. You can use this fact to hide a report from nonadministrative users who are browsing through folders in the Report Manager, while still making the report accessible to someone using URL access.

In addition to executing reports, you can also view the contents of folders, resources, and shared data sources. Try the following:

Enter this URL in the address bar:

 http://localhost/ReportServer?/Galactic Delivery Services

Click Go. The contents of the Galactic Delivery Services folder appears.
Click the link for the 2006 Conference folder. The contents of the 2006 Conference folder appears, as shown in Figure 12–1.

Figure 12–1: Browsing folder contents using URL access

Command Parameters

Look at the URL in the address bar. You see something has been added to the URL, namely &rs:Command=ListChildren. This is called a command parameter. It tells Reporting Services what to do with the item pointed to by the URL. The four possible values for the command parameter are listed in Table 12–1.

Table 12–1: Values for the Command Parameter
Command Parameter	Applies To	Result
GetDataSourceContents	Data Source	Displays the data source definition as an XML structure.
GetResourceContents	Resource Item	Displays the contents of the resource item in the browser.
ListChildren	Folder	Lists the contents of the folder with links to each content item.
Render	Report	Displays the report in the Report Viewer.

Looking at this table, you quickly realize that only one command parameter value applies to each type of item you can encounter in the Reporting Services virtual folders. Attempting to use a command parameter with the wrong type of item results in an error. If you do not include the command parameter, Reporting Services simply performs the one and only command that applies to the type of item you are targeting in your URL. Because specifying the command parameter is completely unnecessary, one can only assume this was put in place to allow for future growth.

Passing Parameters

When you executed the Invoice-Batch Number Report through URL access, you received the default values for the start date and end date. You can change these dates in the Report Viewer, but only after waiting for the report to execute with the default values. It would be much better to get exactly what you want the first time around.

Fortunately, you have a way to do just that. You can pass the values for report parameters as part of the URL. On the URL, include an ampersand (&) followed by the name of the report parameter, an equals sign, and the parameter value.

Try the following:

Enter the following URL in the address bar:

 http://localhost/ReportServer?/Galactic Delivery Services/Axelburg/       Invoice-Batch Number Report&StartDate=11/1/2005&EndDate=11/30/2005

Click Go. The Invoice-Batch Number Report appears with data for November 2005.

It is possible to hide parameters from interactive report users, while still allowing values to be passed to those parameters through the URL or web service access. This is done through the Hide option for each parameter. Let’s try the following:

Open the Report Manager and navigate to the /Galactic Delivery Services/ Axelburg folder.
Click Show Details.
Click the icon in the Edit column next to the Invoice-Batch Number Report.
Click Parameters on the left side of the screen. The Parameter Management page appears.
Check the Has Default check box in the StartDate row
Enter 11/1/2005 as the default value in the StartDate row.
Check the Hide check box in the StartDate row.
Click Apply.
Select the View tab. Notice the Start Date prompt no longer appears in the parameter area.

Enter the following URL in the address bar:

 http://localhost/ReportServer?/Galactic Delivery Services/Axelburg/       Invoice-Batch Number Report&StartDate=12/1/2005&EndDate=12/31/2005

Click Go. The Invoice-Batch Number Report appears with data for December 2005.

Even though Start Date parameter does not appear in the parameters area, we can still specify a value for it other than the default value. The Hide check box is not checked for the City parameter, so a value cannot be specified for this parameter in the URL. The following URL is going to fail:

 http://localhost/ReportServer?/Galactic Delivery Services/Axelburg/       Invoice-Batch Number Report&City=Utonal&EndDate=12/31/2005

Controlling the Report Viewer

In addition to specifying report parameters in the URL, you can also include parameters to control the format of the response from Reporting Services. You can specify which rendering format should be used for the report. Rather than using the Export drop-down list in the Report Viewer to export the report to a particular format, you can have it delivered in that format straight from Reporting Services.

Give this a try:

Enter the following URL in the address bar:

 http://localhost/ReportServer?/Galactic Delivery Services/                               2006 Conference/Nametags&rs:Format=PDF

Click Go.
If you are prompted whether to open or save the file, click Open.
The Nametags report appears in PDF format in Adobe Acrobat Reader.
Close Adobe Acrobat Reader. The valid format parameters are as follows:
- CSV
- EXCEL
- HTML3.2
- HTML4.0
- HTMLOWC
- IMAGE
- MHTML
- NULL
- PDF
- XML

In addition to the rs:Command and rs:Format parameters, several other Report Server parameters use the rs: prefix. Table 12–2 shows these.

Table 12–2: Report Server (rs) URL Parameters and Their Possible Values
Parameter	Valid Values	Function
rs:ClearSession	True False	When true, this parameter prevents a report from being pinned in cache by forcing the report to be rerendered.
rs:ParameterLanguage	a valid culture identifier such as “en-us”	Used to specify a language for the parameters passed in the URL that is different from the browser’s language setting. This defaults to the browser’s language setting when it is not specified.
rs:SessionID	a unique session identifier	Used to maintain session state when the Report Server has been configured not to use session cookies.
rs:Snapshot	the data and time of a valid snapshot for the specified report	Used to render the requested report from a history snapshot.

Device information parameters can also be passed as part of the URL. These device information parameters are specific to the format being used to render the report. Because they are rendering format-specific, device information parameters can also be thought of as renderer control parameters. Therefore, they use an re: prefix.

Let’s look at a couple of examples using device information parameters. When you receive a report rendered as HTML, you also receive the Report Viewer controls. This may not always be desirable. Several device information parameters enable you to specify what portion of the Report Viewer interface you want visible. For example:

Enter the following URL in the address bar:

 http://localhost/ReportServerP/Galactic Delivery Services/Axelburg/       Invoice-Batch Number Report&StartDate=11/1/2005&EndDate=11/30/2005       &rc:Parameters=false

Click Go. The Invoice-Batch Number Report appears with data for November 2005. The parameter portion of the Report Viewer is invisible, so the user cannot change the parameter values.

You can get rid of the entire Report Viewer interface as follows:

Enter the following URL in the address bar:

 http://localhost/ReportServer?/Galactic Delivery Services/Axelburg/       Invoice-Batch Number Report&StartDate=11/1/2005&EndDate=11/30/2005       &rc:Toolbar=false

Click Go. The Invoice-Batch Number Report appears with data for November 2005.
Expand the 445 row heading and the Axelburg column heading.

Even when we expand the row and column headings, causing a new page to be sent from the Report Server, the Report Viewer does not reappear.

Table 12–3 shows the device information parameters for the CSV format.

Table 12–3: CSV Format Device Information (rc) URL Parameters and Their Possible Values
Setting	Valid Values	Function
rc:Encoding	ASCII UTF-7 UTF-8 Unicode	The character encoding scheme to use. The default is Unicode.
rc:Extension		The file extension for the file. The default is .CSV.
rc:FieldDelimiter		The field delimiter to use in the file. The default is a comma.
rc:NoHeader	True False	If true, no header is written with the data in the file. The default is false.
rc:Qualifier		The string qualifier to put around fields that contain the field delimiter. The default is a quotation mark.
rc:RecordDelimiter		The record delimiter to use in the file. The default is a carriage return and linefeed.
rc:SuppressLineBreaks	True False	If true, line breaks in the data are not included in the file. The default is false.

Table 12–4 shows the device information parameters for the Excel format.

Table 12–4: Excel Format Device Information (rc) URL Parameters and Their Possible Values
Setting	Valid Values	Function
rc:OmitDocumentMap	True False	If true, the document map for the rendered report is not included in the Excel file. The default is false.
rc:OmitFormulas	True False	If true, formulas are not included in the Excel file. The default is false.
rc:RemoveSpace	followed by “in” (the abbreviation for inches)	When this parameter is included, rows and columns that do not contain data and are smaller than the size specified are not included in the Excel file. This parameter is used to exclude extra rows or columns that do not contain report items. The default is 0.125in.
rc:SimplePageHeader	True False	If true, the report page header is placed in the Excel page header. Otherwise, the report page header is placed in the first row of the worksheet. The default value is false.

The device information parameters for the HTML formats are shown in Table 12–5.

Table 12–5: HTML Format Device Information (rc) URL Parameters and Their Possible Values
Setting	Valid Values	Function
rc:BookmarkID	{BookmarkID}	Jumps to the specified Bookmark ID in the report.
rc:DocMap	True False	Specifies whether the document map is shown.
rc:DocMapID	{DocMapID}	Jumps to the specified Document Map ID.
rc:EndFind	{PageNumber}	The last report page to be searched when executing a Find from the URL (see FindString).
rc:FallbackPage	{PageNumber}	The report page to go to if the Find is unsuccessful or a jump to a Document Map ID fails.
rc:FindString	{TextToFind}	Searches for this text in the report and jumps to its first location.
rc:HTMLFragment	True False	When this is set to true, the report is returned as a table rather than a complete HTML page. This table can then be placed inside your own HTML page. The default value is false.
rc:JavaScript	True False	If true, JavaScript is supported in the rendered report.
rc:LinkTarget	{TargetWindowName} _blank _self _parent _top	Specifies the target window to use for any links in the report.
rc:Parameters	True False	Specifies whether to show the parameters section of the Report Viewer.
rc:ReplacementRoot		The path used to prefix any hyperlinks created in the report.
rejection	{PageNumber}	The page number of the report to render.
rc:StartFind	{PageNumber}	The first report page to be searched when executing a Find from the URL (see FindString).
rc:StreamRoot	{URL}	The path used to prefix the value of the src attribute of any IMG tags in an HTML rendering of the report.
rc:StyleSheet		The name of a cascading style sheet in the Report Server Styles folder to be applied to the Report Viewer. The name should not include the .css extension. The default location of the Styles folder is C:\Program Files\Microsoft SQL Server\MSSQL.3\ReportingServices\ReportServer\Styles. The default value is HTMLViewer.
rc:StyleStream	True False	If true, styles and scripts are created as separate streams rather than in the document. The default is false.
rc:Toolbar	True False	Specifies whether the Report Viewer toolbar is visible.
rc:Type		The shortname of the browser type as defined in browsercap.ini.
rc:Zoom	Page Width Whole Page 500 200 150 100 75 50 25 10	The zoom percentage to use when displaying the report.

Table 12–6 shows the device information parameters for the image format.

Table 12–6: Image Format Device Information (rc) URL Parameters and Their Possible Values
Setting	Valid Values	Function
rc:ColorDepth	1 4 8 24 32	The color depth of the image created. The default is 24. This is only valid for the TIFF image type.
rc:Columns		The number of columns to use when creating the image.
rc:ColumnSpacing		The column spacing to use when creating the image
rc:DpiX		The number of dots per inch in the x-direction. The default is 96.
rc:DpiY		The number of dots per inch in the y-direction. The default is 96.
rc:EndPage		The last page to render. The default value is the value for the StartPage parameter.
rc:MarginBottom	An integer or decimal followed by “in” (the abbreviation for inches)	The bottom margin to use when creating the image.
rc:MarginLeft	An integer or decimal followed by “in” (the abbreviation for inches)	The left margin to use when creating the image.
rc:MarginRight	An integer or decimal followed by “in” (the abbreviation for inches)	The right margin to use when creating the image.
rc:MarginTop	An integer or decimal followed by “in” (the abbreviation for inches)	The top margin to use when creating the image.
rc:OutputFormat	BMP EMF GIF JPEG PNG TIFF	The graphics format to create.
rc:PageHeight	An integer or decimal followed by “in” (the abbreviation for inches)	The page height to use when creating the image.
rc:PageWidth	An integer or decimal followed by “in” (the abbreviation for inches)	The page width to use when creating the image.
rc:StartPage		The first page to render. A value of 0 causes all pages to be rendered. The default value is 1.

Table 12–7 shows the device information parameters for the MHTML format.

Table 12–7: MHTML Format Device Information (rc) URL Parameters and Their Possible Values
Setting	Valid Values	Function
rc:JavaScript	True False	If true, JavaScript is supported in the rendered report.
rc:MHTMLFragment	True False	When this is set to true, the report is returned as a table rather than a complete HTML page. This table can then be placed inside your own HTML page. The default value is false.

The PDF format device information parameters are shown in Table 12–8.

Table 12–8: PDF Format Device Information (rc) URL Parameters and Their Possible Values
Setting	Valid Values	Function
rc:Columns		The number of columns to use when creating the PDF file.
rc:ColumnSpacing		The column spacing to use when creating the PDF file.
rc:EndPage		The last page to render. The default value is the value for the StartPage parameter.
rc:MarginBottom	An integer or decimal followed by “in” (the abbreviation for inches)	The bottom margin to use when creating the PDF file.
rc:MarginLeft	An integer or decimal followed by “in” (the abbreviation for inches)	The left margin to use when creating the PDF file.
rc:MarginRight	An integer or decimal followed by “in” (the abbreviation for inches)	The right margin to use when creating the PDF file.
rc:MarginTop	An integer or decimal followed by “in” (the abbreviation for inches)	The top margin to use when creating the PDF file.
rc:PageHeight	An integer or decimal followed by “in” (the abbreviation for inches)	The page height to use when creating the PDF file.
rc:PageWidth	An integer or decimal followed by “in” (the abbreviation for inches)	The page width to use when creating the PDF file.
rc:StartPage		The first page to render. A value of 0 causes all pages to be rendered. The default value is 1 .

Table 12–9 shows the device information parameters for the XML format.

Table 12–9: XML Format Device Information (rc) URL Parameters and Their Possible Values
Setting	Valid Values	Function
rc:Encoding	ASCII UTF-8 Unicode	The character encoding scheme to use. The default is UTF-8.
rc:FileExtension		The file extension for the XML file. The default is .XML.
rc:lndented	True False	If true, the XML file is indented. The default is false.
rc:MIMEType		The MIME type of the XML file.
rc:OmitSchema	True False	If true, the schema name and XSD are not included in the XML file. The default is false.
rc:Schema	True False	If true, the XSL schema definition (XSD) is rendered in the XML file. Otherwise, the report itself is rendered in the XML file. The default is false.
UseFormattedValues	True False	If true, the formatted value of each text box is included in the XML file. Otherwise, the unformatted value of each text box is included.
XSLT		The path in the Report Server namespace of an XSLT document to apply to the XML file. The XSLT must be a published resource on the Report Server and it must be accessed through the Report Server itself.

Finally, you can specify the user name and password for data sources that prompt for credentials each time the report is run. This is done using the dsu and dsp prefixes.

For example, to specify credentials for a data source called GalacticPrompt, you would add the following to the end of the URL:

 dsu:GalacticPrompt=MyDBUser&dsp:GalacticPrompt=DBPassword

where MyDBUser is a valid database login and DBPassword is the password for that login.

URL Access Using an HTTP Post

The previous examples demonstrate the use of URL access using the HTTP Get method. This method has several limitations. First, all the parameter values are exposed in the URL itself. Second, the number of characters you can have in a URL has a limit.

You can get around these limitations and still use URL access by employing the HTTP Post method. The HTTP Post method passes parameters as fields in an HTML form, so they are not exposed in the URL. Also, the HTTP Post is not subject to the same length restrictions as the HTTP Get.

The following HTML page uses the HTTP Post to request the Transport Monitor Report for Transport Number 1305 in the HTML 4.0, TIFF image, or Excel format:

 <HTML> <Head> <title> Reporting Services URL Post Demo </title> </Head> <Body> <FORM  action="http://localhost/ReportServer?             /Galactic Delivery Services/Chapter 08/TransportMonitor"             method="post" target="_self"> <H3>Transport Monitor Report</H3><br> <b>For Transport 1305</bxbrxbr> Render the Transportation Monitor Report in the following format : <br> <Select  NAME="rs:Format" size=1> <Option Value="HTML4.0">HTML 4.0</Option> <Option VALUE="IMAGE">TIFF Image</Option> <Option VALUE="EXCEL">Excel FIle</Option> </Select> <Input type="hidden" name="TransportNumber" value="1305"> <br><br> <INPUT type="submit" value="Render Report"> </FORM> </Body> </HTML>

Web Service Access

In addition to URL access, you can also access reports by using the web service interface. This is the same interface used by the Report Manager web application to interact with Reporting Services. This means anything you can do in Report Manager, you can also do through the web service interface.

The web service interface provides additional functionality not available through URL access. For example, the web service interface enables you to specify a set of credentials to use when executing a report. This allows your custom application to use a set of hard-coded credentials to access reports through the web service interface. This can be a big benefit in situations where you want Reporting Services reports to be exposed on an Internet or extranet site where each user does not have a domain account.

Using a Web Service Call to Execute a Report

This example takes you through the steps necessary to execute a report using the web service interface. In this example, you build a web application that acts as a front end for the Axelburg Invoice-Batch Number Report.

Note

Some basic knowledge of ASP.NET programming is assumed in the following discussion.

Creating a Project and a Web Reference First, you need to create an ASP.NET project with a reference to the Reporting Services web service.

Start up Visual Studio 2005. (This example will also work in earlier versions of Visual Studio .NET.)
Create a new project.
Select Visual Basic in the Project Types area.
Select ASP.NET Web Application from the Templates area.
Enter http://localhost/AxelburgFrontEnd for Location.
Click OK.
When the new project has been created, right-click the project folder for this new project in the Solution Explorer and select Add Web Reference from the Context menu. The Add Web Reference dialog box appears.
Select the link for Web Services on the Local Machine.

Note
Again, if Reporting Services is not on your computer, do not use this link. Instead, look for the web service on the computer where Reporting Services is installed.
When the list of web services on the local machine appears, click the link for ReportExecution2005.
When the Reporting Service description appears in the dialog box, click Add Reference.

To use a web service, you need to create code that knows how to send data to and retrieve data from that web service. Fortunately, this code is generated for you by Visual Studio through the process of creating a web reference. Once the web reference is in place, you can call the methods of the web service the same way you call the methods of a local .NET assembly.

When you clicked the link for Web Services on the Local Machine, a URL beginning with http://localhost was used to locate the web services on the local machine. Because of this, the Reporting Services web service uses localhost.ReportingService as its namespace.

Creating the Web Form Now, we need to create the web form that is going to serve as our user interface.

Change the name of WebForml.aspx to ReportFrontEnd.aspx.
Place three labels, two calendar controls, and a button on the web form, as shown in Figure 12–2.

Figure 12–2: The Axelburg Invoice-Batch Number Report front end
Change the Text property of each label as shown.
Change the ID property of the left calendar control to calStartDate.
Set the SelectedDate property and the VisibleDate property of calStartDate to November 1, 2005.
Change the ID property of the right calendar control to calEndDate.
Set the SelectedDate property and the VisibleDate property of calEndDate to December 31, 2005.
Change the ID property of the button to cmdExecute.
Change the Text property of the button to Execute.
Double-click the cmdExecute button to open the code window.

Enter the following code for cmdExecute_Click.

 Private Sub cmdExecute_Click(ByVal sender As System.Object,                              ByVal e As System.EventArgs)                              Handles cmdExecute.Click     Dim report As Byte() = Nothing     ' Create an instance of the Reporting Services     ' Web Reference.     Dim rs As localhost.ReportExecutionService                             = New localhost.ReportExecutionService     ' Create the credentials that will be used when accessing     ' Reporting Services. This must be a logon that has rights     ' to the Axelburg Invoice-Batch Number report.     ' *** Replace "LoginName", "Password", and "Domain" with     '     the appropriate values. ***     rs.Credentials = New           Systern.Net.NetworkCredential("LoginName",          "Password", "Domain")             rs.PreAuthenticate = True     ' The Reporting Services virtual path to the report.     Dim reportPath As String =     "/Galactic Delivery Services/Axelburg/Invoice-Batch Number Report"     ' The rendering format for the report.     Dim format As String = "HTML4.0"     ' The devInfo string tells the report viewer     ' how to display with the report.     Dim devInfo As String =         "<DeviceInfo>" +         "<Toolbar>False</Toolbar>" +         "<Parameters>False</Parameters>" +         "<DocMap>True</DocMap>" +         "<Zoom>100</Zoom>" +         "</DeviceInfo>"     ' Create an array of the values for the report parameters     Dim parameters(1) As localhost.ParameterValue     Dim paramValue As localhost.ParameterValue                               = New localhost.ParameterValue     paramValue.Name = "StartDate"     paramValue.Value = calStartDate.SelectedDate     parameters(0) = paramValue     paramValue = New localhost.ParameterValue     paramValue.Name = "EndDate"     paramValue.Value = calEndDate.SelectedDate     parameters(1) = paramValue     ' Create variables for the remainder of the parameters     Dim historyID As String = Nothing     Dim credentials() As localhost.DataSourceCredentials = Nothing     Dim showHideToggle As String = Nothing     Dim encoding As String     Dim mimeType As String     Dim warnings() As localhost.Warning = Nothing     Dim reportHistoryParameters() As                         localhost.ParameterValue = Nothing     Dim streamIDs() As String = Nothing     Dim execInfo As New localhost.ExecutionInfo     Dim execHeader As New localhost.ExecutionHeader     rs.ExecutionHeaderValue = execHeader     execInfo = rs.LoadReport(reportPath, historyID)     rs.SetExecutionParameters(parameters, "en-us")     Try         ' Execute the report.         report = rs.Render(format,           devInfo, "", mimeType, "", warnings, streamIDs)         ' Flush any pending response.         Response.Clear()         ' Set the HTTP headers for a PDF response.         HttpContext.Current.Response.ClearHeaders()         HttpContext.Current.Response.ClearContent()         HttpContext.Current.Response.ContentType = "text/html"         ' filename is the default filename displayed         ' if the user does a save as.         HttpContext.Current.Response.AppendHeader(           "Content-Disposition",           "filename=""Invoice-BatchNumber.HTM""")         ' Send the byte array containing the report         ' as a binary response.         HttpContext.Current.Response.BinaryWrite(report)         HttpContext.Current.Response.End()     Catch ex As Exception         If ex.Message <> "Thread was being aborted." then             HttpContext.Current.Response.ClearHeaders()             HttpContext.Current.Response.ClearContent()             HttpContext.Current.Response.ContentType = "text/html"             HttpContext.Current.Response.Write(                  "<HTML><BODY><H1>Error</H1><br><br>" &                                ex.Message & "</BODY></HTML>")             HttpContext.Current.Response.End()         End If     End Try End Sub

Click Save All in the toolbar.
Select Debug|Start from the Main menu. This executes your program.
When the browser window appears with the web application front-end page, click Execute. The report appears using the dates selected on the front-end page.
Switch back to Visual Studio and select Debug|Stop Debugging from the Main menu.

You can refer to the comments in the code sample for information on the purpose of each section of code. For additional information and additional examples, refer to Appendix B and the accompanying RSWebServiceSample program.

Note

The items in the DeviceInfo XML structure are the same rendering-specific, device information settings as those documented in the “URL Access” section of this chapter. Use the parameter name, minus the rc: prefix as the element name.

Managing Reporting Services Through Web Services

In addition to executing reports through the web service interface, you can also manage Reporting Services using the web services. If you choose, you can write an application that completely replaces the Report Manager web application for controlling Reporting Services. Refer to Appendix B for more information on management capabilities of the web service interface.

The Report Viewer Control

The Report Server web service gives you a tremendous amount of control over report access. However, the web service simply provides our applications with a stream that contains the report. It is up to our applications to provide an appropriate method for viewing the content of that report stream.

The Report Viewer control in Visual Studio 2005 takes things one step further. Not only does it provide access to the reports, but it also provides a means to view them. In fact, the Report Viewer can even free you from the tether to the Report Server altogether. The Report Viewer control can be used in both Windows forms and web forms.

Displaying a Report from a Report Server

We first use the Report Viewer control to access a report on the Report Server. In this example, you build a Windows application that uses the Report Viewer to display the Axelburg Invoice-Batch Number Report. For this application to function properly, it must have access to the Report Server whenever a report is executed.

Note

The web service example in the previous section works in any version of Visual Studio .NET. The Report Viewer examples in this section require Visual Studio 2005.

Creating a Project and an Instance of the Report Viewer First, you need to create a Windows application project in Visual Studio 2005.

Start up Visual Studio 2005.
Create a new project.
Select Visual Basic|Windows in the Project Types area.
Select Windows Application from the Templates area.
Enter AxelburgRVFrontEnd for Name. Select an appropriate Location for this project.
Click OK. A Windows application project with a Windows form, called Form1, is created.
Expand Form1 so it adequately displays the report.
Select the Toolbox window.
Locate the Data section of the Toolbox and, if it is not already expanded, expand it.
Drag the Report Viewer control from the Toolbox and drop it on Form1. See Figure 12–3.
Click the Dock in Parent Container link in the Report Viewer Tasks dialog box.

Note
If you plan to put other controls on the same form with the Report Viewer, do not dock the viewer in the parent container.

image from book
Figure 12–3: Placing a Report Viewer control on a Windows form

Configuring the Report Viewer Now we need to point the Report Viewer at a report. You need to make several selections from the Report Viewer Tasks dialog box. If this dialog box is invisible, click the small black triangle in the upper-right corner of the Report Viewer control as shown in Figure 12–4.

image from book
Figure 12–4: Opening the ReportViewer Tasks dialog box

In the ReportViewer Tasks dialog box, select <Server Report> from the Choose Report drop-down list.
Enter http://{ReportServer}/ReportServer for Report Server URL where {ReportServer} is the name of the server hosting Reporting Services.
Enter /Galactic Delivery Services/Axelburg/Invoice-Batch Number Report for Report Path.
Click Save All in the toolbar.
Select Debug|Start Debugging from the Main menu. Form1 executes and displays the Invoice-Batch Number Report from the Report Server. If the report requires any parameters, the parameter entry area is displayed as shown in Figure 12–5.

Figure 12–5: The Report Viewer control with the parameter entry area
Enter or select 11/01/2005 for the Start Date.
Enter or select 12/31/2005 for the End Date.

Click View Report. The report appears. Note the interactive features, such as drill-down work in the Report Viewer control as shown in Figure 12–6.

Note

You can use theServerReport:ReportServerUrl and ServerReport:ReportPath properties of the Report Viewer control to programmatically change the report that the Report Viewer displays. In this way, a single Report Viewer control can display different reports depending on user selection.

image from book
Figure 12–6: The Report Viewer displaying a report with drilldown

Displaying a Local Report in the Report Viewer

So far, all the methods of accessing reports we looked at in this chapter have required a Report Server. The Report Server provides a number of advantages for managing reports, including centralized control for updating report definitions and maintaining security. However, in some situations, it is impractical or undesirable for all installations of an application to pull reports from a Report Server.

The Report Viewer control provides an alternative. In addition to displaying reports rendered by a Report Server, the Report Viewer can also render reports contained within the Visual Studio project. In this example, we create a simple report right in the Visual Studio project, and then display it with the Report Viewer.

Creating a Local Report We begin by creating a report in the Visual Studio project.

Close Form1 containing the report to return to Visual Studio 2005, if you have not already done so.
Open the ReportViewer Tasks dialog box.
Click the Design a New Report link. A new item, called Report1.rdlc, is created in the project.
As with reports created previously, the first step in designing a local report is to define the data source. Click the Add New Data Source link in the Data Sources window. The Data Source Configuration Wizard dialog box appears.
As you can see in the dialog box, we can use information from a database, from a web service, or from an object in our application as a data source for the report. Make sure Database is selected and click Next. The Choose Your Data Connection page of the wizard appears.
Click New Connection. The Add Connection dialog box appears.
Create a connection to the Galactic database. Use SQL Server authentication with GalacticReporting as the user and gds as the password. Test the connection to make sure you configured it correctly. When the connection passes the test, click OK.
The wizard warns you that sensitive information, namely the password, is being included in the connection string. Select the radio button next to “Yes, include sensitive data in the connection string” and click Next. The Save the Connection String to the Application Configuration File page of the wizard appears.
In most cases, it makes sense to store the connection information in the configuration file to make maintenance easier. Leave the default setting of Yes and click Next. The Choose Your Database Objects page appears.
Expand the stored procedures node and place a check next to stp_EmployeeList. Enter EmployeeList for the DataSet name.
Click Finish to complete the wizard. A typed dataset is created by the wizard for use with the report. The objects for the typed dataset appear just under the report layout area.
Select the Toolbox window and place a text box at the top of the report layout area. This text box is the title of the report. Enter Employee List as the content of the text box and format it appropriately for a report title.
Add a table to the report layout.
Select the Data Sources window. Expand the EmployeeList|stp_EmployeeList entry to see the fields in the dataset. Drag the fields into the detail line of the table. Format the column headings appropriately.
Your report layout should appear similar to the layout shown in Figure 12–7.

Figure 12–7: Layout of the local report
Click Save All in the toolbar.

Point the Report Viewer at the Local Report Now, we point the Report Viewer at the new local report.

Click the Form1.vb [Design] tab in the layout area of Visual Studio 2005.
Open the Report Viewer Tasks dialog box.
Select AxelburgRVFrontEnd.Report1.rdlc from the Choose Report drop-down list.
Click Save All in the toolbar.
Select Debug|Start Debugging from the Main menu. Form1 executes and displays the local report. The local report you just created shows a list of all Galactic employees.
Close Form1 when you finish viewing this report.

When you compile the AxelburgRVFrontEnd project, the Report1.rdlc report definition is compiled as an embedded resource in the executable. Therefore, the data source is the only thing needed for the report to be rendered. The report always goes along with the application.

SharePoint Web Parts

We looked at a number of ways to integrate Reporting Services reports with applications, but we have one additional method yet to cover. Reporting Services provides a pair of web parts for use with SharePoint. The Report Explorer web part enables users to browse through the folders on the Report Server. The Report Viewer web part displays a rendered report. These web parts are designed for use with SharePoint, but they can be used as standalone components.

Installing the Web Parts

The SharePoint web parts come with Reporting Services in the RSWebParts.cab file ready for installation. The default location of this file is

 C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint

This cab file should be installed using the Stsadm utility. The Stsadm utility unpacks the web parts, installs them in the appropriate location, and creates entries in the SafeControls section of the web.config file for the SharePoint virtual server. Use the following command line to complete the installation using default locations:

 C:\Program Files\Common Files\Microsoft Shared\web server extensions\          60\BIN\STSADM.EXE -o addwppack -filename          "C:\Program Files\Microsoft SQL Server\80\Tools\          Reporting Services\SharePoint\RSWebParts.cab"

Note

If you use the globalinstall switch with the Stsadm utility to install the web parts in the global assembly cache, you need to use the strong name for the assembly in place of the friendly name in the web.config file for the SharePoint virtual server.

Adding the Web Parts

Now that the web parts are installed, they need to be added to a web part page using the SharePoint window. Use the following steps:

Access the SharePoint site and click Create on the SharePoint toolbar.
Scroll down the page to the Web Pages section and click Web Part Page.
Type a name for the web part page and select a layout template.
Enter the location where your web part page is to be saved. The web part page appears.
Click Create at the bottom of the page.
Click Modify Shared Page. Point to Add Web Parts, and then click Browse.
Select the name of the gallery where you installed the Reporting Services web parts.
Select either Report Explorer or the Report Viewer from the list of web parts, and then drag it to an area of the web part page.

When both the Report Explorer and Report Viewer web parts are placed on the same web part page, you can connect them together. This enables the user to browse to a report in the Report Explorer web part, and then view the report in the Report Viewer web part. If the two web parts are not connected, selecting a report in the Report Explorer causes it to display in a new page. Use the following steps to connect the two web parts:

Click Modify Shared Web Part.
On the Report Explorer toolbar menu, click the down arrow, point to Connections, point to Show Report In, and then click Report Viewer.
Click OK.

Reporting Services Utilities

In addition to URL access, the web service interface, Report Viewer and SharePoint web parts, you can also interact with Reporting Services through several command-line utility programs. Like the other methods, these command-line utilities let you manage Reporting Services. These utilities enable you to control Reporting Services, as well as the encryption keys and encrypted values. The most capable of the utilities, the RS utility, lets you script and automate nearly any Reporting Service activity.

Each utility program is briefly described here. For more information, you can execute any of the utility programs followed by /? to view a listing of the valid parameters.

Caution

Even though the parameter listing for each utility program uses a dash before the parameter character (as in -a), you may need to enter a forward slash (as in /a) for the utility program to function properly.

The RSKeyMgmt Utility

The RSKeyMgmt utility is used to administer the encryption key used by Reporting Services. You can use the RSKeyMgmt utility to back up the encryption key. You can also use RSKeyMgmt to delete encrypted data in case of a problem.

When Reporting Services is installed, sensitive information stored in the configuration files, such as logon credentials, is encrypted for security. Also, any user names and passwords stored in reports or shared data sources are also encrypted. The encryption key used to decrypt the information is stored in the Report Catalog (ReportServer) database. Making certain changes can cause problems with the Reporting Services installation. These changes include the following:

Modifying the user account used by the Reporting Services web service
Modifying the name of the SQL Server used to store the Report Catalog
Modifying the name of the computer hosting Reporting Services

A backup copy of the encryption key made with the RSKeyMgmt utility helps recover your Reporting Services installation in these situations.

The backup copy of the encryption key is protected by a password. You specify this password as a parameter to the RSKeyMgmt utility when you create the backup. You must have this password when you use the backup copy of the key.

Creating a Backup of the Report Server Encryption Key To make a backup of the Report Server encryption key, do the following:

Insert a disk in the Report Server’s floppy drive.
Open a command window.
Enter the following at the command prompt, where {password} is the password used to protect the encryption key:
```
 rskeymgmt /e /f a:\rsdbkey.txt /p {password}
```
Press ENTER.
When the backup process is complete, store the disk in a safe location.

Recovering a Reporting Services Installation If your Reporting Services installation becomes disabled because of one of the situations described previously and you have a backup of the encryption key, follow this procedure:

Insert the disk containing the backup of your encryption code into the Report Server’s floppy drive.
Open a command window.
Enter the following at the command prompt, where {password} is the password used to protect the encryption key:
```
 rskeymgmt /a /f a:\rsdbkey.txt /p {password}
```
Press ENTER.

If your Reporting Services installation becomes disabled because of one of the situations described previously and you do not have a backup of the encryption key, follow this procedure:

Open a command window.
Enter the following at the command prompt:
```
 rskeymgmt /d
```
Press ENTER.
Use the RSConfig utility to specify the connection information to the Report Catalog.
Reenter the user names and passwords for all reports and shared data sources stored on this Report Server that use stored credentials.

The RSConfig Utility

The RSConfig utility is used to change the credentials used by Reporting Services to access the Report Catalog (ReportServer) database. These credentials are encrypted in the configuration file, so they cannot be edited directly.

The following example changes the credentials used to access the Report Catalog on a SQL Server called RSServer to use a SQL Server logon called RSCatLogon with a password of rscat37:

 rsconfig /c /s RSServer /d ReportServer /a Sql /u RSCatLogon                                                         /p rscat37

Note

The Report Server Configuration Manager, discussed in Chapter 2, can be used to perform the same functions as the RSKeyMgmt and RSConfig utility programs. The Configuration Manager provides a graphical user interface, which you may find preferable to the command-line interface of the other utility programs.

The RSReportServer.Config File

The RSConfig utility (as well as the Reporting Services Configuration Tool) modifies information stored in the RSReportServer.Config file. Some of the information in this file, such as logon credentials, is encrypted for security purposes. This information must be edited using the utility program. Other configuration information in this file is in plain text and can be edited with Notepad or another text editor. You need to stop and restart the Reporting Services Windows service for these changes to take effect.

Caution

Always make a backup copy of the RSReportServer.Config file before editing. The Reporting Services Windows service cannot restart if this configuration file is invalid.

The default location of this file is

 C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer

Table 12–10 shows the values immediately under the Configuration element in the RSReportServer.Config file. The settings are shown in the order they occur in the file.

Table 12–10: RSReportServer.config Configuration Elements
Setting	Valid Values	Function
Report Server Database Connection Information	(Encrypted—use the RSConfig utility or the Reporting Services Configuration Tool to modify)	This is the information required by Reporting Services to access the ReportServer database. This includes DSN, LogonUser, LogonDomain, and LogonCred.
ConnectionType	Default Impersonate	The type of credentials being used by Reporting Services to access the ReportServer database.
InstanceID		The identifier for the Reporting Services instance. This is tied to a SQL Server instance.
InstallationID		A GUID to identify this Reporting Services installation.
SecureConnectionLevel	0 to 3	The degree of security for the web service connection. 0—All requests processed. 1—Requests made over insecure connections and passing sensitive information, such as credentials, are rejected. 2—All rendered reports and web service calls require a secure connection. 3—All calls made to the Reporting Services SOAP API require a secure connection.
InstanceName		The name for the Reporting Services instance. This is tied to a SQL Server instance.
ProcessRecycleOptions	0 or 1	If 0, ASP.NET worker process is recycled when severe errors occur.
CleanupCycleMinutes		The number of minutes after which old sessions and expired snapshots are removed from the ReportServer databases. A value of 0 disables the cleanup process. The default is 10.
SQLCommandTimeoutSeconds		This setting is not used.
MaxActiveReqForOneUser		The maximum number of simultaneous, in-progress connections a single user can have open. This setting is intended to thwart a denial of service (DoS) attack. A value of 0 indicates no limit. The default is 20.
DatabaseQueryTimeout		The number of seconds before a connection to the ReportServer database times out. A value of 0 results in no timeout. The default is 120.
RunningRequestsScavengerCycle		The number of seconds before orphaned and expired requests are canceled. The default is 60.
RunningRequestsDbCycle		The frequency, in seconds, at which the Manage Jobs page is updated and the running jobs are checked to determine if they have exceeded the report execution timeout. The default is 60.
RunningRequestAge		The number of seconds after which a running job’s status is changed from new to running. The default is 30.
MaxScheduleWait		The number of seconds Reporting Services waits for a schedule to be updated by the SQL Server Agent when a next run time is requested. The default is 5.
DisplayErrorLink	True False	If true, a link to the Microsoft Help and Support site is displayed when an error occurs. The default is true.
WebServiceUseFileShareStorage	True False	If true, the Reporting Services web service stores cached reports and temporary snapshots on the file system rather than in the ReportServerTempDB database. The default is false.
WatsonFlags		Specifies the type of dump sent with error reporting to Microsoft. 0x0430—Full dump 0x0428—Minidump 0x0002—No dump The default is 0x0428.
WatsonDumpOnExceptions		Do not change this setting.
WatsonDumpExcludelfContainsExceptions		Do not change this setting.

Table 12–11 shows the values in the Service section of the RSReportServer.config file. The settings are shown in the order they occur in the file.

Table 12–11: RSReportServer.config Service Elements
Setting	Valid Values	Function
IsSchedulingService	True False	If true, a thread is dedicated to making sure the schedules in the ReportServer database match the schedules in the SQL Server Agent. The default is true.
IsNotificationService	True False	If true, a thread is dedicated to polling the notification table in the ReportServer database to determine if there are any pending notifications. The default is true.
IsEventService	True False	If true, Reporting Services processes events in the event queue. The default is true.
PollingInterval		The number of seconds between polls of the event table. The default is 10.
WindowsServiceUseFileShareStorage	True False	If true, the Report Server Windows service stores cached reports and temporary snapshots on the file system rather than in the ReportServerTempDB database. The default is false.
FileShareStorageLocation		The path to the folder where cached reports and temporary snapshots are stored, if they are being stored on the file system. A UNC path can be used, but it is not recommended. The default is C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\RSTempFiles.
MemoryLimit		The percent of available memory that may be used by Reporting Services before requests are rejected. The default is 60.
RecycleTime		The number of minutes for the recycling of the Report Server application domain. After this interval has elapsed, all new requests are sent to a new instance of the Reporting Services application domain. The default is 720.
MaximumMemoryLimit		The Report Server application domain is recycled when it reaches this percentage of available memory used. The default is 720.
MaxAppDomainUnloadTime		The number of minutes the Report Server application domain is allowed to upload during a recycle operation. The default is 30.
MaxQueueThreads		The maximum number of threads dedicated to polling the event table in the ReportServer database. The default is 0.
UrlRoot		The URL root used by delivery extensions to create the URL for accessing items stored on the Report Server.
UnattendedExecutionAccount		The credentials for the Execution Account. See Chapter 2 for more information. These credentials are encrypted and should be set using the Reporting Services Configuration Tool.
PolicyLevel		The security policy configuration file for the Report Server.
WebServiceAccount		The credentials used to run the Report Server web services. Also used to confirm the identity of the Report Server web service when doing data encryption.
IsWindowsServiceEnabled	True False	If true, the Report Server Windows service is enabled. This is set by the SQL Server Surface Area Configuration Tool. The default is true.
IsWebServiceEnabled	True False	If true, the Report Server web service is enabled. This is set by the SQL Server Surface Area Configuration Tool. The default is true.

The next sections of the RSReportServer.config file deal with extensions to the Report Server for delivery, rendering, data processing, semantic query processing,

custom security, and event processing. These extensions are beyond the scope of this book, with the exception of the custom security extension, which is covered in the later section “Issues with Custom Security.”

The RS Utility

The RS utility is used to execute script that can interact with Reporting Services. The scripting language supported by the RS utility is Visual Basic .NET. This scripting language supports the complete web service interface to Reporting Services.

The RS utility automatically creates a reference to the web service interface. This predefined reference, called rs, means you do not need to instantiate the web service interface; it is simply ready to go. All the Reporting Services classes and data types are also available.

The following sample code lists the contents of the Galactic Delivery Services virtual folder:

Enter the following into Notepad or some other text editor:

 Public Sub Main()     Dim items() As CatalogItem     items = rs.ListChildren("/Galactic Delivery Services", False)     Dim item As CatalogItem     For Each item In items         Console.WriteLine(item.Name)     Next item End Sub

Save this to a file called rstest.rss in a convenient folder on the Report Server.
Open a command window.
Change to the folder where you stored the rstest.rss file.
Enter the following at the command prompt, where {userID} is a logon with administrative rights on the Report Server and {password} is the password for that logon:
```
 rs /i rstest.rss /s http://localhost/ReportServer                           /u {userID} /p {password}
```
Press ENTER. A list of the folders in the Galactic Delivery Services folder appears in the command window.

Using the RS Utility to Manage System Properties

In Chapter 11, we looked at the Site Settings page in the Report Manager. This page enables you to make configuration changes to Reporting Services system properties. In addition to the settings exposed on the Site Settings page, Reporting Services has a number of other configuration options. Table 12–12 lists all these Reporting Services system properties.

Table 12–12: Reporting Services System Properties
Property	Valid Values	Function
EnableClientPrinting	True False	If true, users may download the ActiveX object and use client-side printing. The default is true.
EnableExecutionLogging	True False	If true, the execution of each report is recorded in a log table. The default is true.
EnableIntegratedSecurity	True False	If true, integrated security may be used in data sources. The default is true.
EnableMyReports	True False	If true, a MyReports folder is created for each Report Server user. The default is false.
EnableReportDesignClientDownload	True False	If true, a user with appropriate rights may use the Edit link in the Report Definition section of the report properties to download a copy of the report definition. The default is true.
ExecutionLogDaysKept	0 to 2,147,483,647	The number of days of log information kept in the report execution log. A value of 0 means an unlimited number of days are kept in the log. The default is 60.
ExternallmagesTimeout		The maximum number of seconds the Report Server attempts to retrieve an external image. The default is 600.
MyReportsRole	{Security Role}	The security role to assign to each user with their MyReports folder. The default is My Reports.
SessionTimeout	An integer value	The number of seconds a session remains active without any activity. The default is 600.
SiteName	A string up to 8,000 characters in length	The title displayed at the top of the Report Manager pages. The default is Microsoft Report Server.
SnapshotCompression	All None SQL	If All, report snapshots are compressed when stored in all locations, including both the Report Server database and the file system. If None, report snapshots are not compressed. If SQL, report snapshots are only compressed when stored in the Report Server database. The default is SQL.
SystemReportTimeout	–1 to 2,147,483,647	The maximum number of minutes a given report may execute. This value can be overridden for an individual report. A value of –1 means reports may execute for an unlimited amount of time. The default is 5.
SystemSnapshotLimit	–1 to 2,147,483,647	The maximum number of snapshots that can be saved for a given report. A value of –1 means there is no limit.
UseSessionCookies	True False	If true, the Report Server uses session cookies to track each session. If false, the rs:SessionID Report Server parameter must be used to pass the session ID. The default is true.

Caution

Using integrated security with a report exposes your SQL Server to a security risk. If a user with administration rights on the SQL Server executes a report with integrated security, that report then has administration rights on the server. A malicious query built into such a report could harm your SQL Server when it is run with integrated security. This risk can be mitigated by using a careful QA testing process before each report is deployed to the Report Server. If this is impossible and you want to eliminate the risk of this type of attack, set the EnableIntegratedSecurity system property to false.

One of the easiest ways to query and set the system properties that are unavailable on the Site Settings page is through the RS utility. The following script prints all the system properties and their current values:

 Public Sub Main()       Dim SSRSProperties() As [Property]       Dim SSRSProperty As [Property]       SSRSProperties = rs.GetSystemProperties(Nothing)       For Each SSRSProperty In SSRSProperties             Console.WriteLine(SSRSProperty.Name & "—" & SSRSProperty.Value)       Next item End Sub

This script sets the SystemReportTimeout property to ten minutes:

 Public Sub Main()       Dim SSRSProperties(0) As [Property]       Dim SSRSProperty As New [Property]       SSRSProperty.Name = "SystemReportTimeout"       SSRSProperty.Value = 600       SSRSProperties(0) = SSRSProperty       rs.SetSystemProperties(SSRSProperties) End Sub

Log Files

Along with the Reporting Services utilities, the logs created by Reporting Services can be helpful for managing and troubleshooting. These logs are text files that can be viewed with Notepad or any other text editor. In a default installation, the log files created by Reporting Services are stored in the following folder:

 C:\Program Files\Microsoft SQL Server\MSSQL\                                        Reporting Services\LogFiles

Four different types of log files are created, as listed in the following table.

File Name	Created By
ReportServer_{timestamp}.log	Report Server Engine
ReportServerService_{timestamp}.log ReportServerService_main_itimestampl.log	Report Server Windows Service
ReportServerWebApp_{timestamp}.log	Report Manager

File Name

Created By

ReportServer_{timestamp}.log

Report Server Engine

ReportServerService_{timestamp}.log

ReportServerService_main_itimestampl.log

Report Server Windows Service

ReportServerWebApp_{timestamp}.log

Report Manager

In addition to these log files is an ExecutionLog table in the Report Catalog (ReportServer) database. A record is created in this table each time a report is executed. The date and time of the execution, as well as the user name of the logged on user, are recorded. Unfortunately, the report being executed is identified by a globally unique identifier (GUID) rather than by the report name. Fortunately, Microsoft provides an Integration Services package for converting the information in the ExecutionLog table into something far more useable, including report names.

For more information on the Execution Log and the conversion Integration Services package, view “execution logs [Reporting Services]” in the index of SQL Server Books Online. (SQL Server Books Online is available in your Program menu under Microsoft SQL Server|Documentation and Tutorials|SQL Server Books Online.)

Note

Report Execution Logging must be turned on to use the logging features.