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.

1. Start Internet Explorer.

2. Enter the following URL in the address bar:

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

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

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:

1. Enter this URL in the address bar:

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

2. Click Go. The contents of the Galactic Delivery Services folder appears.

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

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:

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

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

1. Open the Report Manager and navigate to the /Galactic Delivery Services/ Axelburg folder.

2. Click Show Details.

3. Click the icon in the Edit column next to the Invoice-Batch Number Report.

4. Click Parameters on the left side of the screen. The Parameter Management page appears.

5. Check the Has Default check box in the StartDate row

6. Enter 11/1/2005 as the default value in the StartDate row.

7. Check the Hide check box in the StartDate row.

8. Click Apply.

9. Select the View tab. Notice the Start Date prompt no longer appears in the parameter area.

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

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

1. Enter the following URL in the address bar:

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

2. Click Go.

3. If you are prompted whether to open or save the file, click Open.

4. The Nametags report appears in PDF format in Adobe Acrobat Reader.

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

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:

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

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

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

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

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

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.

1. Start up Visual Studio 2005. (This example will also work in earlier versions of Visual Studio .NET.)

2. Create a new project.

3. Select Visual Basic in the Project Types area.

4. Select ASP.NET Web Application from the Templates area.

5. Enter http://localhost/AxelburgFrontEnd for Location.

6. Click OK.

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

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

9. When the list of web services on the local machine appears, click the link for ReportExecution2005.

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

1. Change the name of WebForml.aspx to ReportFrontEnd.aspx.

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

3. Change the Text property of each label as shown.

4. Change the ID property of the left calendar control to calStartDate.

5. Set the SelectedDate property and the VisibleDate property of calStartDate to November 1, 2005.

6. Change the ID property of the right calendar control to calEndDate.

7. Set the SelectedDate property and the VisibleDate property of calEndDate to December 31, 2005.

8. Change the ID property of the button to cmdExecute.

9. Change the Text property of the button to Execute.

10. Double-click the cmdExecute button to open the code window.

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

12. Click Save All in the toolbar.

13. Select Debug|Start from the Main menu. This executes your program.

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

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

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.

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

1. Start up Visual Studio 2005.

2. Create a new project.

3. Select Visual Basic|Windows in the Project Types area.

4. Select Windows Application from the Templates area.

5. Enter AxelburgRVFrontEnd for Name. Select an appropriate Location for this project.

6. Click OK. A Windows application project with a Windows form, called Form1, is created.

7. Expand Form1 so it adequately displays the report.

8. Select the Toolbox window.

9. Locate the Data section of the Toolbox and, if it is not already expanded, expand it.

10. Drag the Report Viewer control from the Toolbox and drop it on Form1. See Figure 12–3.

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

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.

Figure 12–4: Opening the ReportViewer Tasks dialog box

1. In the ReportViewer Tasks dialog box, select <Server Report> from the Choose Report drop-down list.

2. Enter http://{ReportServer}/ReportServer for Report Server URL where {ReportServer} is the name of the server hosting Reporting Services.

3. Enter /Galactic Delivery Services/Axelburg/Invoice-Batch Number Report for Report Path.

4. Click Save All in the toolbar.

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

6. Enter or select 11/01/2005 for the Start Date.

7. Enter or select 12/31/2005 for the End Date.

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

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.

1. Close Form1 containing the report to return to Visual Studio 2005, if you have not already done so.

2. Open the ReportViewer Tasks dialog box.

3. Click the Design a New Report link. A new item, called Report1.rdlc, is created in the project.

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

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

6. Click New Connection. The Add Connection dialog box appears.

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

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

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

10. Expand the stored procedures node and place a check next to stp_EmployeeList. Enter EmployeeList for the DataSet name.

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

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

13. Add a table to the report layout.

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

15. Your report layout should appear similar to the layout shown in Figure 12–7.

    Figure 12–7: Layout of the local report

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

1. Click the Form1.vb [Design] tab in the layout area of Visual Studio 2005.

2. Open the Report Viewer Tasks dialog box.

3. Select AxelburgRVFrontEnd.Report1.rdlc from the Choose Report drop-down list.

4. Click Save All in the toolbar.

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

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

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:

1. Access the SharePoint site and click Create on the SharePoint toolbar.

2. Scroll down the page to the Web Pages section and click Web Part Page.

3. Type a name for the web part page and select a layout template.

4. Enter the location where your web part page is to be saved. The web part page appears.

5. Click Create at the bottom of the page.

6. Click Modify Shared Page. Point to Add Web Parts, and then click Browse.

7. Select the name of the gallery where you installed the Reporting Services web parts.

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

1. Click Modify Shared Web Part.

2. On the Report Explorer toolbar menu, click the down arrow, point to Connections, point to Show Report In, and then click Report Viewer.

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

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:

1. Insert a disk in the Report Server’s floppy drive.

2. Open a command window.

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

4. Press ENTER.

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

1. Insert the disk containing the backup of your encryption code into the Report Server’s floppy drive.

2. Open a command window.

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

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

1. Open a command window.

2. Enter the following at the command prompt:

   rskeymgmt /d

3. Press ENTER.

4. Use the RSConfig utility to specify the connection information to the Report Catalog.

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

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.

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

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:

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

2. Save this to a file called rstest.rss in a convenient folder on the Report Server.

3. Open a command window.

4. Change to the folder where you stored the rstest.rss file.

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

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

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.

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