Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET
Article

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

Rate me:
Please Sign up or sign in to vote.
4.71/5 (20 votes)
12 Dec 2006CPOL4 min read 216K   82   32
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.

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.

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

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

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

Image 1

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

Image 2

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

Image 3

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.

Image 4

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

Image 5

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.

Image 6

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

Image 7

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.

Image 8

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

Image 9

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

Image 10

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.

Image 11

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

C#
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”.

Image 12

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

Image 13

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

Image 14

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey17-Feb-12 20:45
professionalManoj Kumar Choubey17-Feb-12 20:45 
GeneralMy vote of 5 Pin
keyhidalgob25-Oct-10 6:59
keyhidalgob25-Oct-10 6:59 
GeneralMy vote of 5 Pin
Rajasekhar_Pediredla5-Jul-10 22:51
Rajasekhar_Pediredla5-Jul-10 22:51 
Generaldrillthrough report in vs 2008 Pin
raheel ahmad13-Feb-10 21:33
raheel ahmad13-Feb-10 21:33 
Generalusing VS 2008 Get Error on SQLHelper (Drillthrought Reports) Pin
raheel ahmad12-Feb-10 21:24
raheel ahmad12-Feb-10 21:24 
GeneralError:An attempt was made to set a report parameter '@SubstanceName' that is not defined in this report. Please help ASAP! Pin
archie@hyd21-Jan-10 22:03
archie@hyd21-Jan-10 22:03 
GeneralRe: Error:An attempt was made to set a report parameter '@SubstanceName' that is not defined in this report. Please help ASAP! Pin
ShirleySW25-Jan-10 4:44
ShirleySW25-Jan-10 4:44 
GeneralDrill-Through Reports using ReportViewer Getting Error " An error occurred during local report processing" Pin
kanagaraju2-Sep-09 18:45
kanagaraju2-Sep-09 18:45 
GeneralRe: Drill-Through Reports using ReportViewer Getting Error " An error occurred during local report processing" Pin
ShirleySW3-Sep-09 3:46
ShirleySW3-Sep-09 3:46 
NewsRe: Drill-Through Reports using ReportViewer Getting Error " An error occurred during local report processing" Pin
Member 894893729-May-12 0:55
Member 894893729-May-12 0:55 
GeneralWinForm Drill-Through Pin
HennieSmit12-May-09 23:14
HennieSmit12-May-09 23:14 
GeneralRe: WinForm Drill-Through Pin
ShirleySW13-May-09 3:38
ShirleySW13-May-09 3:38 
GeneralRe: WinForm Drill-Through Pin
HennieSmit13-May-09 22:58
HennieSmit13-May-09 22:58 
GeneralGreat Example, but can't get past this error when I click the button Pin
geezer9925-Apr-09 7:04
geezer9925-Apr-09 7:04 
GeneralRe: Great Example, but can't get past this error when I click the button Pin
ShirleySW27-Apr-09 3:30
ShirleySW27-Apr-09 3:30 
GeneralRe: Great Example, but can't get past this error when I click the button Pin
kmrkonjic14-Jul-09 7:23
kmrkonjic14-Jul-09 7:23 
QuestionParent report gives error after drillthrough Pin
chetangarude6-Sep-07 3:57
chetangarude6-Sep-07 3:57 
AnswerRe: Parent report gives error after drillthrough Pin
ShirleySW6-Sep-07 4:17
ShirleySW6-Sep-07 4:17 
GeneralError messages Pin
odeddror4-Jul-07 4:55
odeddror4-Jul-07 4:55 
GeneralRe: Error messages Pin
ShirleySW5-Jul-07 3:29
ShirleySW5-Jul-07 3:29 
Questionpagination on the drill-down report Pin
Spatacoli19-Jun-07 14:03
Spatacoli19-Jun-07 14:03 
AnswerRe: pagination on the drill-down report Pin
ShirleySW20-Jun-07 4:02
ShirleySW20-Jun-07 4:02 
GeneralUpdated code for this demo Pin
JessicaH8-Jun-07 6:44
JessicaH8-Jun-07 6:44 
GeneralRe: Updated code for this demo Pin
odeddror4-Jul-07 7:57
odeddror4-Jul-07 7:57 
GeneralRe: Updated code for this demo Pin
Multitrax28-Aug-07 5:44
Multitrax28-Aug-07 5:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.