Using the WebForms ReportViewer Control

New: 17 July 2006

To view reports deployed to a report server or reports that exist on the local file system, you can use the WebForms ReportViewer control to render them in a Web application.

To add the ReportViewer Control to a Web application
  1. Create a new Microsoft ASP.NET Web Site using either Microsoft Visual C# or Microsoft Visual Basic.
    - Or -
    Open an exiting ASP.NET Web Site and add a new Web Form.
  2. Locate the ReportViewer control in the Toolbox. If the Toolbox is not visible, you can access it from the View menu by selecting Toolbox.
  3. Drag the ReportViewer control onto the design surface of the Web Form.
    The ReportViewer control named reportViewer1 is added to the form.

After the control is added to the form the ReportViewer Tasks smart tag will appear prompting you to select a report. If the report you wish to view has been deployed to a report server select the <Server Report> option from the Choose Report drop-down list. Once the <Server Report> option is selected two additional properties will appear, Report Server Url and Report Path. The Report Server Url is the address to the report server and the Report Path is the full path to the report you want to render. If you want to view a report in local mode select either the Design a new report option to launch the report designer or select a report that is already part of the existing project.

Viewing Reports in Remote Processing Mode

The following example demonstrates how to render a report that has been deployed to a report server. This example uses the Sales Order Detail report that is included with the AdventureWorks sample reports project. For more information about how to deploy this sample report, see AdventureWorks Report Samples.

The example uses integrated Windows authentication so you first must enable impersonation. To do this insert the following line into the web.config file:

<!-- Web.config file. -->
<identity impersonate="true"/>

Note

Impersonation is disabled by default.

protected void Page_Init(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        // Set the processing mode for the ReportViewer to Remote
        reportViewer.ProcessingMode = ProcessingMode.Remote;

        ServerReport serverReport = reportViewer.ServerReport;

        // Set the report server URL and report path
        serverReport.ReportServerUrl =
            new Uri("http://localhost/reportserver");
        serverReport.ReportPath =
            "/AdventureWorks Sample Reports/Sales Order Detail";

        // Create the sales order number report parameter
        ReportParameter salesOrderNumber = new ReportParameter();
        salesOrderNumber.Name = "SalesOrderNumber";
        salesOrderNumber.Values.Add("SO43661");

        // Set the report parameters for the report
        reportViewer.ServerReport.SetParameters(
            new ReportParameter[] { salesOrderNumber });
    }
}
Imports Microsoft.Reporting.WebForms

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(ByVal sender As Object, _
            ByVal e As System.EventArgs) Handles Me.Init

        If Not Page.IsPostBack Then

            'Set the processing mode for the ReportViewer to Remote
            reportViewer.ProcessingMode = ProcessingMode.Remote

            Dim serverReport As ServerReport
            serverReport = reportViewer.ServerReport

            'Set the report server URL and report path
            serverReport.ReportServerUrl = _
                New Uri("http://localhost/reportserver")
            serverReport.ReportPath = _
                "/AdventureWorks Sample Reports/Sales Order Detail"

            'Create the sales order number report parameter
            Dim salesOrderNumber As New ReportParameter()
            salesOrderNumber.Name = "SalesOrderNumber"
            salesOrderNumber.Values.Add("SO43661")

            'Set the report parameters for the report
            Dim parameters() As ReportParameter = {salesOrderNumber}
            serverReport.SetParameters(parameters)

        End If

    End Sub

End Class

Viewing Reports in Local Processing Mode

The following example demonstrates how to render a report that is part of the Windows application and has not been deployed to a report server. This example also uses the Sales Order Detail report from the AdventureWorks Report Samples project.

To add the Sales Order Detail report to a Web Site
  1. Open the Web Site that the report will be added to.
  2. From the Website menu, select Add Existing Item.
  3. Browse to the location where the AdventureWorks Report Samples project is installed.
    The default location is C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports. For more information about how to install the samples, see Installing Samples.
  4. Select the Sales Order Detail.rdl file and click the Add button.
    The Sales Order Detail.rdl file should now be part of the project.
  5. Right-click the Sales Order Detail.rdl file in the Solution Explorer and select Rename. Rename the report to Sales Order Detail.rdlc and press ENTER.
    If the Solution Explorer is not visible, you can open it from the View menu by selecting Solution Explorer.

The following code example will create a dataset for the sales order data and then render the Sales Order Detail report in local mode.

protected void Page_Init(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        // Set the processing mode for the ReportViewer to Local
        reportViewer.ProcessingMode = ProcessingMode.Local;

        LocalReport localReport = reportViewer.LocalReport;

        localReport.ReportPath = "Sales Order Detail.rdlc";

        DataSet dataset = new DataSet("Sales Order Detail");

        string salesOrderNumber = "SO43661";

        GetSalesOrderData(salesOrderNumber, ref dataset);

        ReportDataSource dsSalesOrder = new ReportDataSource();
        dsSalesOrder.Name = "SalesOrder";
        dsSalesOrder.Value = dataset.Tables["SalesOrder"];

        localReport.DataSources.Add(dsSalesOrder);

        GetSalesOrderDetailData(salesOrderNumber, ref dataset);

        ReportDataSource dsSalesOrderDetail = new ReportDataSource();
        dsSalesOrderDetail.Name = "SalesOrderDetail";
        dsSalesOrderDetail.Value = dataset.Tables["SalesOrderDetail"];

        localReport.DataSources.Add(dsSalesOrderDetail);

        // Create the sales order number report parameter
        ReportParameter rpSalesOrderNumber = new ReportParameter();
        rpSalesOrderNumber.Name = "SalesOrderNumber";
        rpSalesOrderNumber.Values.Add("SO43661");

        // Set the report parameters for the report
        localReport.SetParameters(
            new ReportParameter[] { rpSalesOrderNumber });
    }
}

private void GetSalesOrderData(string salesOrderNumber,
                           ref DataSet dsSalesOrder)
{
    string sqlSalesOrder =
        "SELECT SOH.SalesOrderNumber, S.Name AS Store, " +
        "       SOH.OrderDate, C.FirstName AS SalesFirstName, " +
        "       C.LastName AS SalesLastName, E.Title AS " +
        "       SalesTitle, SOH.PurchaseOrderNumber, " +
        "       SM.Name AS ShipMethod, BA.AddressLine1 " +
        "       AS BillAddress1, BA.AddressLine2 AS " +
        "       BillAddress2, BA.City AS BillCity, " +
        "       BA.PostalCode AS BillPostalCode, BSP.Name " +
        "       AS BillStateProvince, BCR.Name AS " +
        "       BillCountryRegion, SA.AddressLine1 AS " +
        "       ShipAddress1, SA.AddressLine2 AS " +
        "       ShipAddress2, SA.City AS ShipCity, " +
        "       SA.PostalCode AS ShipPostalCode, SSP.Name " +
        "       AS ShipStateProvince, SCR.Name AS " +
        "       ShipCountryRegion, CC.Phone AS CustPhone, " +
        "       CC.FirstName AS CustFirstName, CC.LastName " +
        "       AS CustLastName " +
        "FROM   Person.Address SA INNER JOIN " +
        "       Person.StateProvince SSP ON " +
        "       SA.StateProvinceID = SSP.StateProvinceID " +
        "       INNER JOIN Person.CountryRegion SCR ON " +
        "       SSP.CountryRegionCode = SCR.CountryRegionCode " +
        "       RIGHT OUTER JOIN Sales.SalesOrderHeader SOH " +
        "       LEFT OUTER JOIN  Person.Contact CC ON " +
        "       SOH.ContactID = CC.ContactID LEFT OUTER JOIN" +
        "       Person.Address BA INNER JOIN " +
        "       Person.StateProvince BSP ON " +
        "       BA.StateProvinceID = BSP.StateProvinceID " +
        "       INNER JOIN Person.CountryRegion BCR ON " +
        "       BSP.CountryRegionCode = " +
        "       BCR.CountryRegionCode ON SOH.BillToAddressID " +
        "       = BA.AddressID ON  SA.AddressID = " +
        "       SOH.ShipToAddressID LEFT OUTER JOIN " +
        "       Person.Contact C RIGHT OUTER JOIN " +
        "       HumanResources.Employee E ON C.ContactID = " +
        "       E.ContactID ON SOH.SalesPersonID = " +
        "       E.EmployeeID LEFT OUTER JOIN " +
        "       Purchasing.ShipMethod SM ON SOH.ShipMethodID " +
        "       = SM.ShipMethodID LEFT OUTER JOIN Sales.Store" +
        "        S ON SOH.CustomerID = S.CustomerID " +
        "WHERE  (SOH.SalesOrderNumber = @SalesOrderNumber)";

    SqlConnection connection = new
        SqlConnection("Data Source=(local); " +
                      "Initial Catalog=AdventureWorks; " +
                      "Integrated Security=SSPI");

    SqlCommand command =
        new SqlCommand(sqlSalesOrder, connection);

    command.Parameters.Add(
        new SqlParameter("SalesOrderNumber",
        salesOrderNumber));

    SqlDataAdapter salesOrderAdapter = new
        SqlDataAdapter(command);

    salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder");
}

private void GetSalesOrderDetailData(string salesOrderNumber,
                       ref DataSet dsSalesOrder)
{
    string sqlSalesOrderDetail =
        "SELECT  SOD.SalesOrderDetailID, SOD.OrderQty, " +
        "        SOD.UnitPrice, CASE WHEN " +
        "        SOD.UnitPriceDiscount IS NULL THEN 0 " +
        "        ELSE SOD.UnitPriceDiscount END AS " +
        "        UnitPriceDiscount, SOD.LineTotal, " +
        "        SOD.CarrierTrackingNumber, " +
        "        SOD.SalesOrderID, P.Name, P.ProductNumber " +
        "FROM    Sales.SalesOrderDetail SOD INNER JOIN " +
        "        Production.Product P ON SOD.ProductID = " +
        "        P.ProductID INNER JOIN " +
        "        Sales.SalesOrderHeader SOH ON " +
        "        SOD.SalesOrderID = SOH.SalesOrderID " +
        "WHERE   (SOH.SalesOrderNumber = @SalesOrderNumber) " +
        "ORDER BY SOD.SalesOrderDetailID";

    using (SqlConnection connection = new
        SqlConnection("Data Source=(local); " +
                      "Initial Catalog=AdventureWorks; " +
                      "Integrated Security=SSPI"))
    {

        SqlCommand command =
            new SqlCommand(sqlSalesOrderDetail, connection);

        command.Parameters.Add(
            new SqlParameter("SalesOrderNumber",
            salesOrderNumber));

        SqlDataAdapter salesOrderDetailAdapter = new
            SqlDataAdapter(command);

        salesOrderDetailAdapter.Fill(dsSalesOrder,
            "SalesOrderDetail");
    }
}
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WebForms

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(ByVal sender As Object, _
                ByVal e As System.EventArgs) Handles Me.Init

        If Not Page.IsPostBack Then

            'Set the processing mode for the ReportViewer to Local
            reportViewer.ProcessingMode = ProcessingMode.Local

            Dim localReport As LocalReport
            localReport = reportViewer.LocalReport

            localReport.ReportPath = "Sales Order Detail.rdlc"

            Dim dataset As New DataSet("Sales Order Detail")

            Dim salesOrderNumber As String = "SO43661"

            'Get the sales order data
            GetSalesOrderData(salesOrderNumber, dataset)

            'Create a report data source for the sales order data
            Dim dsSalesOrder As New ReportDataSource()
            dsSalesOrder.Name = "SalesOrder"
            dsSalesOrder.Value = dataset.Tables("SalesOrder")

            localReport.DataSources.Add(dsSalesOrder)

            'Get the sales order detail data
            GetSalesOrderDetailData(salesOrderNumber, dataset)

            'Create a report data source for the sales 
            'order detail data
            Dim dsSalesOrderDetail As New ReportDataSource()
            dsSalesOrderDetail.Name = "SalesOrderDetail"
            dsSalesOrderDetail.Value = _
                dataset.Tables("SalesOrderDetail")

            localReport.DataSources.Add(dsSalesOrderDetail)

            'Create a report parameter for the sales order number 
            Dim rpSalesOrderNumber As New ReportParameter()
            rpSalesOrderNumber.Name = "SalesOrderNumber"
            rpSalesOrderNumber.Values.Add("SO43661")

            'Set the report parameters for the report
            Dim parameters() As ReportParameter = {rpSalesOrderNumber}
            localReport.SetParameters(parameters)

        End If

    End Sub

    Private Sub GetSalesOrderData(ByVal salesOrderNumber As String, _
                               ByRef dsSalesOrder As DataSet)

        Dim sqlSalesOrder As String = _
            "SELECT SOH.SalesOrderNumber, S.Name AS Store, " & _
            "       SOH.OrderDate, C.FirstName AS SalesFirstName, " & _
            "       C.LastName AS SalesLastName, E.Title AS " & _
            "       SalesTitle, SOH.PurchaseOrderNumber, " & _
            "       SM.Name AS ShipMethod, BA.AddressLine1 " & _
            "       AS BillAddress1, BA.AddressLine2 AS " & _
            "       BillAddress2, BA.City AS BillCity, " & _
            "       BA.PostalCode AS BillPostalCode, BSP.Name " & _
            "       AS BillStateProvince, BCR.Name AS " & _
            "       BillCountryRegion, SA.AddressLine1 AS " & _
            "       ShipAddress1, SA.AddressLine2 AS " & _
            "       ShipAddress2, SA.City AS ShipCity, " & _
            "       SA.PostalCode AS ShipPostalCode, SSP.Name " & _
            "       AS ShipStateProvince, SCR.Name AS " & _
            "       ShipCountryRegion, CC.Phone AS CustPhone, " & _
            "       CC.FirstName AS CustFirstName, CC.LastName " & _
            "       AS CustLastName " & _
            "FROM   Person.Address SA INNER JOIN " & _
            "       Person.StateProvince SSP ON " & _
            "       SA.StateProvinceID = SSP.StateProvinceID " & _
            "       INNER JOIN Person.CountryRegion SCR ON " & _
            "       SSP.CountryRegionCode = SCR.CountryRegionCode " & _
            "       RIGHT OUTER JOIN Sales.SalesOrderHeader SOH " & _
            "       LEFT OUTER JOIN  Person.Contact CC ON " & _
            "       SOH.ContactID = CC.ContactID LEFT OUTER JOIN" & _
            "       Person.Address BA INNER JOIN " & _
            "       Person.StateProvince BSP ON " & _
            "       BA.StateProvinceID = BSP.StateProvinceID " & _
            "       INNER JOIN Person.CountryRegion BCR ON " & _
            "       BSP.CountryRegionCode = " & _
            "       BCR.CountryRegionCode ON SOH.BillToAddressID " & _
            "       = BA.AddressID ON  SA.AddressID = " & _
            "       SOH.ShipToAddressID LEFT OUTER JOIN " & _
            "       Person.Contact C RIGHT OUTER JOIN " & _
            "       HumanResources.Employee E ON C.ContactID = " & _
            "       E.ContactID ON SOH.SalesPersonID = " & _
            "       E.EmployeeID LEFT OUTER JOIN " & _
            "       Purchasing.ShipMethod SM ON SOH.ShipMethodID " & _
            "       = SM.ShipMethodID LEFT OUTER JOIN Sales.Store" & _
            "        S ON SOH.CustomerID = S.CustomerID " & _
            "WHERE  (SOH.SalesOrderNumber = @SalesOrderNumber)"

        Using connection As New SqlConnection( _
                      "Data Source=(local); " & _
                      "Initial Catalog=AdventureWorks; " & _
                      "Integrated Security=SSPI")

            Dim command As New SqlCommand(sqlSalesOrder, connection)

            Dim parameter As New SqlParameter("SalesOrderNumber", _
                salesOrderNumber)
            command.Parameters.Add(parameter)

            Dim salesOrderAdapter As New SqlDataAdapter(command)

            salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder")

        End Using

    End Sub

    Private Sub GetSalesOrderDetailData( _
                           ByVal salesOrderNumber As String, _
                           ByRef dsSalesOrder As DataSet)

        Dim sqlSalesOrderDetail As String = _
            "SELECT  SOD.SalesOrderDetailID, SOD.OrderQty, " & _
            "        SOD.UnitPrice, CASE WHEN " & _
            "        SOD.UnitPriceDiscount IS NULL THEN 0 " & _
            "        ELSE SOD.UnitPriceDiscount END AS " & _
            "        UnitPriceDiscount, SOD.LineTotal, " & _
            "        SOD.CarrierTrackingNumber, " & _
            "        SOD.SalesOrderID, P.Name, P.ProductNumber " & _
            "FROM    Sales.SalesOrderDetail SOD INNER JOIN " & _
            "        Production.Product P ON SOD.ProductID = " & _
            "        P.ProductID INNER JOIN " & _
            "        Sales.SalesOrderHeader SOH ON " & _
            "        SOD.SalesOrderID = SOH.SalesOrderID " & _
            "WHERE   (SOH.SalesOrderNumber = @SalesOrderNumber) " & _
            "ORDER BY SOD.SalesOrderDetailID"

        Using connection As New SqlConnection( _
                      "Data Source=(local); " & _
                      "Initial Catalog=AdventureWorks; " & _
                      "Integrated Security=SSPI")

            Dim command As New SqlCommand(sqlSalesOrderDetail, _
                                          connection)

            Dim parameter As New SqlParameter("SalesOrderNumber", _
                salesOrderNumber)
            command.Parameters.Add(parameter)

            Dim salesOrderDetailAdapter As New SqlDataAdapter(command)

            salesOrderDetailAdapter.Fill(dsSalesOrder, _
                "SalesOrderDetail")

        End Using

    End Sub

End Class

See Also

Concepts

Integrating Reporting Services Using the ReportViewer Controls

Other Resources

Installing Samples

Help and Information

Getting SQL Server 2005 Assistance