Accessing Reports via URLs and Embedding Reports
 
In the last article titled, Adding Parameters to a Report, we saw the basics of how to add parameters to report to control the information that is shown to the report user. We also saw how to create dependent parameters so that you can have multiple parameters depending on each other for data display. We now have enough information to see how to access reports via the URL directly. Why is this required?? Usually, you access reports using the Report Manager application which provides a web based interface for working with reports. The URL based access is useful if you want to embed reports into another application (like a web-based application or a forms-based application).

In this article we will see how to access a report using its URL and then see an example of embedding a report into a Windows Forms application. I will assume that you are already familiar with using SQL Reporting Services. If you are not, please read the following articles (in the same order):

  1. Reporting Services - A First Look
  2. Adding Parameters to a Report

Let us first build a report and test it so that we can then access it via its URL. The following are the steps you will need to follow to create this report.

  1. Create a new Business Intelligence Project in Visual Studio 2003.
  2. Create a shared data source to the AdventureWorks2000 database.
  3. The report that we will create will show information about a product and an image of the product (if it exists)
  4. We will create two datasets, one which will act as the parameter list and the other to get the data.
    1. The first dataset will be called ProductList and will use the following query: SELECT ProductID, Name FROM Product
    2. The second dataset will be called ProductDetails and will use the following query: SELECT p.Name, p.ProductNumber, p.Color, p.ListPrice, i.LargePhoto FROM dbo.Product p LEFT OUTER JOIN dbo.ProductPhoto i ON p.ProductPhotoID = i.ProductPhotoID WHERE ProductID = @ProductID
  5. Note that we need to configure @ProductID as a parameter that fetches its data from the ProductList dataset
  6. Finally we can give a layout for the report. The following figure shows the layout that I have chosen.

You can now preview this report to see if everything is working fine. The following figure shows a sample product selected in my preview pane.

Looks like everything is fine! We will now see how to access the same report using the URL based access. Before doing this, we need to deploy the report. The folder structure for this project looks like the following on my machine (you might have a different structure based on your project name etc).

When you now deploy this project, a new folder called My Experiments is created in the report server and the report that we have developed (called ProductImages.rdl) will be placed. We need to understand this structure to properly specify the URL to access the report.

The URL access syntax for Reporting Services contains a bunch of information that we need to know about. URL requests can contain multiple parameters that are listed in any order. Parameters are separated by an ampersand (&) and name/value pairs are separated by an equal sign (=). The basic syntax is as follows:


http://servername/virtualroot?[/pathinfo]&prefix:parameter=value[...n]

In the above syntax:

  • servername, is the name of the server where report server is running. If you are running report server locally on your own machine, this can be specified as localhost
  • virtualroot, is the name of virtual directory of the report server. On my machine, this is called reportserver
  • pathinfo, is the full path of the item being accessed from the report server. This corresponds to the layout that we discussed earlier
  • prefix, is a parameter prefix that accesses a specific process running within the report server. If a parameter prefix for a parameter is not included, the parameter is processed by the report server as a report parameter
  • parameter=value, is the name-value pair denoting the name of the parameter and its intended value

Ok, with this information, let us see how to get the URL for the report developed above. The following is the URL that you will need to type in your browser:


http://localhost/reportserver?/My%20Experiments/ProductImages&rc:Command=Render&ProductID=497

Note that /My Experiments/ProductImages constitutes the full path to access our report. The rc is a prefix that supplies a rendering extension with specific device information settings. Refer to the Reporting Services BOL for more information about the prefixes and what they mean. Finally, ProductID is the name of our parameter and 497 is the product ID that we are interested in. Here is another example that shows how to store the output of the report using the JPEG format.


http://localhost/reportserver?/My Experiments/ProductImages&ProductID=497&rs:Command=Render&rs:Format=IMAGE&rc:OutputFormat=JPEG
 
When you execute the above URL, the browser will prompt you for a dialog box that will allow you to save the output of the report to a JPEG file.
 
Once we understand how the URL syntax works, it is very easy to capitalize this on an application so that we can embed reports of interest. Let us see an example of an application (Windows Forms) that allows a user to type in a product number and then shows the report designed above. To develop this application, create a Windows Forms application with the following layout.
 
 
Note that the blank space below the controls is the browser control. When the user clicks on the Show Details button, we point the browser to the appropriate URL and provide the parameter from the text box as the product ID. This will then display the report on the browser control. Here is the code for the button.

    Private Sub cmdShowDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdShowDetails.Click
        Dim strURL As String

        strURL = "http://localhost/reportserver?/My Experiments/ProductImages&rc:Command=Render&rc:Toolbar=false&ProductID=" & Trim(txtProductID.Text)
        myWebBrowser.Navigate(strURL)
    End Sub
 
Note the usage of rc:Toolbar=false. This parameter ensures that the toolbar that displays parameter info and other details is not displayed. When you run this application and provide a product ID, you will see the associated details as shown in the following figure.
 
 
That's cool, isn'nt it?? Embedding reports can help you do a lot of cool things inside your application without having to code for the same. You can design the appropriate reports that aggregate data from various sources and then embed them inside your application and reap all the benefits of that data. Remember that the report however is still accessible via the report manager application. Thus, its the best of both worlds!! Have fun!!

Home