Create Drill-Through Reports using ReportViewer in ASP.NET 2.0

Introduction

This article provides a step-by-step demo on how to create drill-through reports in local mode using SQL Server 2005, Microsoft Application Blocks, and the ReportViewer control in ASP.NET 2.0.

Scenario

We will create a parent report, listing all orders placed by each customer. When the user clicks on the [Order ID] field on the ReportViewer, a drill-through report is displayed to show the line items which make up the parent level order summary. When the user clicks on the [Product ID] field on each line item, our demo will display another level of drill-through which shows the product details.

Step 1: Create a stored procedure to list all orders and its dollar total per customer. I have limited the number of display records by selecting only two customers in this demo.

ALTER PROCEDURE List_Customers_OrderTotal
AS
SELECT Customers.CompanyName,
Orders.OrderID, 
Orders.OrderDate, 
SUM([Order Details].Quantity * [Order Details].UnitPrice) AS TotalDollars
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.CustomerID IN ('THEBI', 'THECR')
GROUP BY CompanyName, Orders.OrderID, OrderDate
ORDER BY CompanyName, Orders.OrderID, OrderDate
RETURN

Step 2: Create a stored procedure to show order details. Results from this stored procedure supplies data to our level 1 drill-through report.

ALTER PROCEDURE Show_OrderDetails ( @OrderID int )
AS
SELECT [Order Details].OrderID,
Products.ProductID,
Products.ProductName, 
[Order Details].UnitPrice, 
[Order Details].Quantity 
FROM [Order Details] INNER JOIN Products 
  ON [Order Details].ProductID = Products.ProductID
WHERE [Order Details].OrderID = @OrderID
RETURN

Step 3: Create a stored procedure to show product details. Result from this stored procedure supplies data to our level 2 drill-through report.

ALTER PROCEDURE Show_Products ( @ProductID int )
AS
SELECT Products.ProductID,
Products.ProductName As Product,
Categories.CategoryName As Category,
Categories.Description,
Suppliers.CompanyName AS Supplier,
Suppliers.Phone AS SupplierPhone
FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE ProductID=@ProductID
RETURN

Step 4: Create data tables in a dataset (.xsd) under the App_Code folder. This .xsd is used to store data retrieved from each stored procedure. Each data table will serve as a data source for an individual .rdlc. We will create three .rdlcs in the next few steps.

Step 5: Create a parent report (Parent.rdlc)

Step 6: Create drill-through report #1 (Level1.rdlc)

Step 7: Define the Report Parameter for report #1 (Level1.rdlc). When the user clicks on the OrderID field in the parent.rdlc, the OrderID value is passed to Level1.rdlc. The OrderID value is used to retrieve the correct order detail line item.

Click in the body of Level1.rdlc. Click the menu option “Report”. Select “Report Parameters”. Click “Add” to add OrderID to the Report Parameters list.

Step 8: Create drill-through report #2 (Level2.rdlc)

Step 9: Define Report Parameter for report #2 (Level2.rdlc). When the user clicks on the ProductID field in Level1.rdlc, the ProductID value is passed to Level2.rdlc to pull up the correct product information.

Click in the body of Level2.rdlc. Click the menu option “Report”. Select “Report Parameters”. Click “Add” to add ProductID to the Report Parameters list.

Step 10: Identify drill-through report to navigate to when the OrderID in parent.rdlc is clicked. For example, when the user clicks on an OrderID value in Parent.rdlc, our application will display the Level1.rdlc with the correct Order Details info.

Open Parent.rdlc. Right click on the value of OrderID. Select Properties from the context menu. Select the Navigation tab. Pick Level1.rdlc from “Jump to report”.

Click on “Parameters” and open the dialog box below. Enter “OrderID” as the Parameter Name, and identify where to get the parameter value. Click OK.

When the user clicks on a ProductID value in Level1.rdlc, our application will display the Level2.rdlc with the correct Product Info. Open Level1.rdlc. Right click on the value of ProductID. Select Properties from the context menu. Select the Navigation tab. Pick Level2.rdlc from “Jump to report”.

Click on “Parameters” and open the dialog box below. Enter “ProductID” as the Parameter Name and identify where to get the parameter value.

Step 11: Create an .aspx to house all three .rdlcs. DrillThroughReport_Parent.aspx is created below to display the initial report “Parent.rdlc” and subsequently, “Level1.rdlc” and “Level2.rdlc”. There is no need to create a separate .aspx to house “Level1.rdlc” or “Level2.rdlc”.

Be sure to set the ReportViewer control’s Visible property to false in design mode. The Visible property will be re-set to true programmatically once its data source is filled.

Step 12: Add source code to parent report’s drill-through event (DrillThroughReport_Parent.aspx)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;

public partial class DrillThroughReport_Parent : System.Web.UI.Page
{
    public string thisConnectionString = 
      ConfigurationManager.ConnectionStrings
      ["NorthwindConnectionString"].ConnectionString;
    public string thatConnectionString = 
      ConfigurationManager.ConnectionStrings
      ["NorthwindConnectionString"].ConnectionString;
    public SqlParameter[] Level1SearchValue = new SqlParameter[1];
    public SqlParameter[] Level2SearchValue = new SqlParameter[1];

    protected void RunReportButton_Click(object sender, EventArgs e)
    {
        ReportViewer1.Visible = true;

        SqlConnection thisConnection = new SqlConnection(thisConnectionString);
        System.Data.DataSet thisDataSet = new System.Data.DataSet();

        //Run the stored procedure  to fill dataset for Parent.rdlc
        thisDataSet = SqlHelper.ExecuteDataset(thisConnection, 
                     "List_Customers_OrderTotal");

        //Assign dataset to report datasource
        ReportDataSource datasource = 
          new ReportDataSource("DrillThroughDataSet_ListCustomersOrderTotal", 
          thisDataSet.Tables[0]);

        //Assign datasource to reportviewer control
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
        ReportViewer1.LocalReport.Refresh();
    }

    protected void ReportViewer1_Drillthrough(object sender, 
              DrillthroughEventArgs e)
    {
        //Get OrderID that was clicked by 
        //user via e.Report.GetParameters()
        ReportParameterInfoCollection DrillThroughValues = 
                                 e.Report.GetParameters();    

        //This is just to show you how to iterate 
        //through the collection if you have
        //multiple parameters values instead of a single parameter value.
        //To process multiple parameters values, 
        //concatenate d.Values[0] into a string with a delimiter.
        //Use the Split() method to  separate values 
        //into an array. Assign indivdual array element to
        //corresponding parameter array element.
        foreach (ReportParameterInfo d in DrillThroughValues)
        {
            lblParameter.Text = d.Values[0].ToString().Trim();
        }
        LocalReport localreport = (LocalReport)e.Report;

        //Fill dataset for Level1.rdlc
        SqlConnection thisConnection = new SqlConnection(thisConnectionString);
        System.Data.DataSet Level1DataSet = new System.Data.DataSet();

        Level1SearchValue[0] = new SqlParameter("@OrderID", 
                               lblParameter.Text.Trim());
        Level1DataSet = SqlHelper.ExecuteDataset(thisConnection, 
                       "Show_OrderDetails", Level1SearchValue);

        ReportDataSource level1datasource = new 
          ReportDataSource("DrillThroughDataSet_ShowOrderDetails", 
          Level1DataSet.Tables[0]);
        localreport.DataSources.Clear();
        localreport.DataSources.Add(level1datasource);
        localreport.Refresh();

        //Fill dataset for Level2.rdlc.
        SqlConnection thatConnection = 
          new SqlConnection(thatConnectionString);
        System.Data.DataSet Level2DataSet = new System.Data.DataSet();

        Level2SearchValue[0] = 
          new SqlParameter("@ProductID", lblParameter.Text);
        Level2DataSet = SqlHelper.ExecuteDataset(thisConnection, 
                       "Show_Products", Level2SearchValue);

        ReportDataSource level2datasource = 
          new ReportDataSource("DrillThroughDataSet_ShowProducts", 
          Level2DataSet.Tables[0]);
        //No need to clear datasource again
        localreport.DataSources.Add(level2datasource);
        localreport.Refresh();
    }    
}

Let’s run the application: When the user clicks on “Run Report” button, parent.rdlc shows the result from the stored procedure “List_Customers_OrderTotal”.

When the user clicks on the Order ID “10310”, Level1.rdlc shows the result returned by the stored procedure “Show_OrderDetails”.

If the user clicks on Product ID “62”, Level2.rdlc shows the result returned by the stored procedure “Show_Products”.

Conclusion

We have created a two-level drill-through report in local mode using Microsoft Application Blocks, SQL Server 2005, and Visual Studio 2005. Hope this example offers some insights into the “How To’s” in creating drill-through reports. If this is the first time you use the ReportViewer control, you may want to look up my other article “Using the ASP.NET 2.0 ReportViewer in Local Mode” for installation hints on your local Web.config and the .exe required on the server.

Happy Computing!