Using Reporting Services Without the Report Manager | Microsoft SQL Server 2000 Reporting Services Step by Step (Pro-Step by Step Developer)

The Report Manager provides a very nice interface for finding and executing reports. There will be times, however, when the Report Manager is not 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 will 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 wish 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 will execute the Invoice-Batch Number report for the Axelburg office. This report is in the Axelburg folder inside of the Galactic Delivery Services folder.

Note

In the examples used throughout the rest of this chapter, it is assumed that 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

Click Go. The Invoice-Batch Number report will appear in the browser inside of the Report Viewer.

Note

When your URL is submitted, it will be URL encoded. Some of the characters in your URL may be replaced by other characters or by hexadecimal strings such as %20. This ensures that 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 will result. 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 non-administrative 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 will appear.
Click the link for the 2004 Conference folder. The contents of the 2004 Conference folder will appear, 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 will see that 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 will 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 will result in an error. If you do not include the command parameter, Reporting Services will simply perform 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 that 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 around8

Fortunately, there is 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&StartDate=11/1/2003&EndDate=11/30/2003

Click Go. The Invoice-Batch Number report will appear with data for November, 2003.

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/                              2004 Conference/Nametags&rs:Format=PDF

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

You can also specify what portion of the Report Viewer interface you want visible. Here’s an example:

Enter the following URL in the address bar:

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

Click Go. The Invoice-Batch Number report will appear with data for November, 2003. The parameter portion of the Report Viewer is not visible, so the user cannot change the parameter values.

You can even 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&StartDate=11/1/2003&EndDate=11/30/2003            &rc:Toolbar=false

Click Go. The Invoice-Batch Number report will appear with data for November, 2003.
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.

The major settings that can be passed as URL parameters and their possible values are listed in Table 12-2.

Table 12-2: URL Parameters and Their Possible Values
Setting	Valid Values	Function
BookmarkID	{BookmarkID}	Jumps to the specified Bookmark ID in the report.
DocMap	True False	Specifies whether the document map is shown.
DocMapID	{DocMapID}	Jumps to the specified Document Map ID.
EndFind	{PageNumber}	The last report page to be searched when executing a Find from the URL (see FindString).
FallbackPage	{PageNumber}	The report page to go to if the Find is unsuccessful or a jump to a Document Map ID fails.
FindString	{TextToFind}	Searches for this text in the report and jumps to its first location.
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.
LinkTarget	{TargetWindowName} _blank _self _parent _top	Specifies the target window to use for any links in the report.
Parameters	True False	Specifies whether to show the parameters section of the Report Viewer.
Section	{PageNumber}	The page number of the report to render.
StartFind	{PageNumber}	The first report page to be searched when executing a Find from the URL (see FindString).
StreamRoot	{URL}	The path used to prefix the value of the src attribute of any IMG tags in an HTML rendering of the report.
Toolbar	True False	Specifies whether the Report Viewer toolbar is visible.
Zoom	Page Width Whole Page 500 200 150 100 75 50 25 10	The zoom percentage to use when displaying the report.

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 that 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 allows 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 will take you through the steps necessary to execute a report using the web service interface. In this example, you will 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 we need to create an ASP.NET project with a reference to the Reporting Services web service.

Start up Visual Studio .NET 2003.
Create a new project.
Select Visual Basic Projects 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 will appear.
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 ReportService.
When the Reporting Service description appears in the dialog box, click Add Reference.

In order 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 will use “localhost.ReportingService” as its namespace.

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

Change the name of WebForm1.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, 2003.
Change the ID property of the right calendar control to calEndDate.
Set the SelectedDate property and the VisibleDate property of calEndDate to December 31, 2003.
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.ReportingService _                                 = New localhost.ReportingService         ' 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 _               System.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"         ' 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 sh As localhost.SessionHeader = _                             New localhost.SessionHeader         rs.SessionHeaderValue = sh         Try             ' Execute the report.             report = rs.Render(reportPath, format, historyID, _                                devInfo, parameters, credentials, _                                showHideToggle, encoding, mimeType, _                                reportHistoryParameters, warnings, _                                streamIDs)             sh.SessionId = rs.SessionHeaderValue.SessionId             ' 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 will execute your program.
When the browser window appears with the web application front-end page, click Execute. The report will appear 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, refer to Appendix B.

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 had a mind to, you could 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 services interface.

Reporting Services Utilities

In addition to URL access and the web services interface, you can also interact with Reporting Services through several command-line utility programs. Like the other methods, these command-line utilities allow you to manage Reporting Services. These utilities allow you to start up or activate Reporting Services as well as control encryption keys and encrypted values. The most capable of the utilities, the RS utility, allows you to script and automate just about any Reporting Service activity.

Each utility program will be described here briefly. 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 use a forward slash (as in /a) in order for the utility program to function.

The RSActivate Utility

As the name implies, the RSActivate utility activates a new Reporting Services installation. This only needs to be done once when Reporting Services is newly installed. The activation process creates the encryption key that Reporting Services will use to encrypt sensitive information, such as user names and passwords, stored in the configuration files.

Usually, activation occurs automatically as part of the Reporting Services installation process. In most cases, you will not need to use the RSActivate utility unless a problem occurs during installation. See “Manually Activating Reporting Services” in Chapter 2 for more information on using the RSActivate utility to manually activate a Reporting Services installation.

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 due to 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.
When the process completes, enter the following at the command prompt:
```
iisreset
```
Press ENTER.

If your Reporting Services installation becomes disabled due to 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.
When the process completes, enter the following at the command prompt:
```
iisreset
```
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 used 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 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 will appear in the command window.

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_{timestamp}.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_{timestamp}.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 that is being executed is identified by a globally unique identifier (GUID) rather than by the report name. Fortunately, Microsoft provides a DTS 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 DTS package, view “Execution Log” in the index of Reporting Services Books Online. (Reporting Services Books Online is available in your Program menu under Microsoft SQL Server | Reporting Services | Reporting Services Books Online.)