ASP.NET 2.0 Reporting Revisited: Part II

Creating Drill-through Reports Using the ReportViewer

asp:Feature

LANGUAGES: C# | VB.NET

ASP.NET VERSIONS: 2.0

 

ASP.NET 2.0 Reporting Revisited: Part II

Creating Drill-through Reports Using the ReportViewer

 

By Dennis E. Rehm

 

In Part I we created a summary report with totals. Our goal is to be able to click on a total and go directly to a detail report itemizing those items that make up the total. We created the first detail report, but didn t linked them together. If you want to work along, you ll need to have completed the work in the first article. We re simply going to pick up from the end of that article.

 

Linking the Detail Report to the Summary Report

We created linkage on the detail report side. Now we need to create the linkage on the summary report.

 

Open the summary report, OrderSummary.rdlc. We re using a matrix control to display the data in our summary report. We ll need to enhance the matrix control in order to call the detail report we just created. We ll create a simple drill-through first, then enhance it when we create the second detail report.

 

Right-click the Data cell, the one that contains =Sum(Fields!TotalOrderAmt.Value). Select Properties from the pop-up menu. The Textbox Properties window will open. Click on the Navigation tab. Your window should look like Figure 1.

 


Figure 1: Creating the jump.

 

Under Hyperlink action click the Jump to report radiobutton. Select OrderDetailInd in the dropdown list. Click the Parameters button to open the Parameters window (see Figure 2). Here we define our first parameter. Under Parameter Name enter ProductID. Under Parameter Value, select =Fields!ProductID.Value from the dropdown list. The second parameter is named CountryRegionCode and its value is =Fields!CountryRegionCode.Value. When you ve defined both parameters, click the OK button. This returns you to the Textbox Properties window. Again, click the OK button.

 


Figure 2: Defining the jump parameters.

 

The report now has the linkage in place. The Order Summary report looks the same, but it now has hyperlink behavior defined. We need to write a little code, then we ll be ready to test it. Save the report and close the Report Designer.

 

Enhancing the Web Page for Drill-through to the Individual Order Detail Report

Open the Web page that contains the ReportViewer. Now that we ve added the drill-through report, we need an ObjectDataSource for it. Drag to the Web page an ObjectDataSource from the Data section of the Toolkit. Use the SmartTag and select Configure Data Source. In the Configure Data Source window, select the OrderDetailIndRptTableAdapter in the dropdown list under Choose your business object. Click the Next button. Nothing needs to be changed for Define Data Methods. Click the Next button. You ll see our ProductID and CountryRegionCode parameters in Define Parameters. Do not link the parameters to anything. Click the Finish button.

 

We want to give this ObjectDataSource a more descriptive ID. Select it and in the Properties panel, change the ID to objDetailInd. The Web page now has two ObjectDataSources, one for the original summary report and one for the individual order detail report (see Figure 3).

 


Figure 3: Adding an ObjectDataSource.

 

The only thing left to do is hook things together in code. Go to the code for the Web page. Add the following statement at the top of the code page:

 

VB

Imports Microsoft.Reporting.WebForms

 

C#

using Microsoft.Reporting.WebForms;

 

In C# we need to define the drill-through event for the ReportViewer. Go to the Web page Designer and click the Source button at the bottom. Then add OnDrillthrough= ReportViewer1_Drillthrough as shown here:

 

 Font-Names="Verdana" Font-Size="8pt" OnDrillthrough=

 "ReportViewer1_Drillthrough" Height="400px" Width="799px">

 

In VB.NET we can simply add the ReportViewer1_Drillthrough event with its code (see Figure 4). In this ReportViewer1_Drillthrough event, we ll link the parameters from the order details SQL statement with the Report Parameters defined for the individual order detail report. This event will be fired when the user clicks on an amount cell in the summary report. In this case, the ProductID and CountryRegionCode will be passed to the SQL statement to retrieve the requested order rows ID. We are checking for the appropriate report name as a precaution, as well as preparation for drill-through to different reports.

 

VB

Protected Sub ReportViewer1_Drillthrough(ByVal sender As Object, _

   ByVal e As Microsoft.Reporting.WebForms.DrillthroughEventArgs) _

   Handles ReportViewer1.Drillthrough

   Dim drillthroughReport As LocalReport = _

   CType(e.Report, LocalReport)

   If drillthroughReport.ReportPath.Contains("OrderDetailInd") Then

   objDetailInd.SelectParameters("ProductID").DefaultValue = _

   e.Report.GetParameters("ProductID").Values(0)

     objDetailInd.SelectParameters("CountryRegionCode").DefaultValue = _

 e.Report.GetParameters("CountryRegionCode").Values(0)

     Dim dataSource As New ReportDataSource _

    ("OrderDetailIndRpt_DataTable1", objDetailInd)

     drillthroughReport.DataSources.Add(dataSource)

   End If

   End Sub

 

C#

protected void ReportViewer1_Drillthrough

    (object sender, Microsoft.Reporting.WebForms.DrillthroughEventArgs e)

{

   LocalReport drillthroughReport = (LocalReport)e.Report;

   ReportParameterInfoCollection rptparms =

     drillthroughReport.GetParameters();

   if (drillthroughReport.ReportPath.Contains("OrderDetailInd"))

   {

     objDetailInd.SelectParameters["ProductID"].DefaultValue =

       rptparms["ProductID"].Values[0];

     objDetailInd.SelectParameters["CountryRegionCode"].DefaultValue =

       rptparms["CountryRegionCode"].Values[0];

     ReportDataSource dataSource = new ReportDataSource

        ("OrderDetailIndRpt_DataTable1", objDetailInd);

     drillthroughReport.DataSources.Add(dataSource);

   }

}

Figure 4: Writing the code to link the drill-through report with the Summary report.

 

Note the name of the ReportDataSource. It is OrderDetailIndRpt_DataTable1 , which is the name of the dataset concatenated with the name of the datatable. You need to know this. Because it is a literal, IntelliSense cannot help you.

 

We re ready to test. Run the application and open the Web page. Select a row and click on the total amount under US - Individual. Note the Product Name and the Total Amount. In a moment, the drill-through report should appear. If you select the row with the product Water bottle - 30 oz., the Web page should look like Figure 5. Go to the last page and scroll to the bottom. The total amount on the detail report should match the total amount you clicked on. (Note: If you click on the total amount under Other, you ll still get the total for Individual. We ll add that enhancement next.) Congratulations! You ve just created a drill-through report.

 


Figure 5: The Summary report ready to drill through.

 

If your report doesn t work properly, carefully review all the steps regarding the Individual Detail Orders report. When it is working, we re ready to enhance the drill-through with a second detail report. This will go more quickly.

 

Creating the Detail Dataset for Resellers

Again, we start with a dataset to retrieve the required data. This time we ll retrieve data about orders for a single Product purchased by others.

 

Create a dataset in the App-Code folder named OrderDetailOtherRpt.xsd. The SQL will retrieve the orders from resellers for a single Product ID and either US or Non orders. This is done in the WHERE clause with the @ProductID and @CountryRegionCode parameter. This report will display both quantity and amount, whereas the summary report displayed only an amount (see Figure 6).

 

SELECT Cat.Name AS CategoryName, Subcat.Name AS SubcategoryName,

 Det.ProductID, Prod.Name AS ProductName,

 Terr.NAME AS TerritoryName, Cust.CustomerID,

 Det.OrderQty, Det.LineTotal

FROM Sales.SalesOrderHeader Hdr

INNER JOIN Sales.SalesOrderDetail Det

 ON Hdr.SalesOrderID = Det.SalesOrderID

INNER JOIN Production.Product Prod

 ON Det.ProductID = Prod.ProductID

INNER JOIN Production.ProductSubcategory Subcat

 ON Prod.ProductSubcategoryID = Subcat.ProductSubcategoryID

INNER JOIN Production.ProductCategory Cat

 ON Subcat.ProductCategoryID = Cat.ProductCategoryID

INNER JOIN Sales.SalesTerritory Terr

 ON Hdr.TerritoryID = Terr.TerritoryID

INNER JOIN Sales.Customer Cust

 ON Hdr.CustomerID = Cust.CustomerID

WHERE Prod.ProductID = @ProductID

 AND Cust.CustomerType <> 'I'

 AND (Terr.CountryRegionCode = @CountryRegionCode OR

    (Terr.CountryRegionCode <> 'US' AND @CountryRegionCode <> 'US'))

ORDER BY TerritoryName, Cust.CustomerID

Figure 6: The SQL statement for sales to resellers.

 

This SQL statement is very similar to the SQL statement for our summary report, except that we are excluding orders from individuals and we are not doing a GROUP BY to create totals. As with the individual detail report, we are checking the CountryRegionCode in the WHERE clause. We want to match US or anything else.

 

Remember to go into the Advanced Options and uncheck the Generate Insert, Update and Delete statements box. If you re having problems creating this dataset, follow the step-by-step instructions in the Creating the Summary Dataset section in Part I. When you ve finished with the wizard, you ll see the dataset with the column list. Note the symbol of a key by CustomerID. Click on the CustomerID column and right-click the key beside it. Select Delete key from the pop-up menu to delete it. (The Customer ID is not a primary key for this dataset.) The completed dataset should look like the one in Figure 7.

 


Figure 7: The Reseller Sales dataset.

 

Creating the Other Detail Orders Report

Now we can create the report that will be the Other Detail Orders report. When we click on the OrderSummary report to drill-through, we ll determine whether the amount clicked is for individuals or for others. If the amount is for individuals, we ll open the OrderDetailInd report. If the amount is for others, we ll open the OrderDetailOther report that we re going to build.

 

Add a report named OrderDetailOther to the Reports folder. (See the Creating the Summary Report section in Part I for a more detailed description of the process.) Drag a matrix control to the report. Make sure it s in the upper left-hand corner. Then drag the Category column from the Website Data Sources panel under OrderDetailOtherRpt and DataTable1 and drop it on the cell labeled Rows.

 

Next add Subcategory in the rows. Set Name to matrix1_SubcategoryName, Expression to =Fields!SubcategoryName.Value, Parent group to =Fields!CategoryName.Value, and check the box next to Group header should remain visible while scrolling. (If you ve forgotten how to do this, refer back to the Creating the Summary Report section in Part I.) Click the OK button.

 

Repeat this process to add Product as a Row Group. Set Name to matrix1_ProductName, Expression to =Fields!ProductName.Value, Parent group to =Fields!SubcategoryName.Value, and check the Group header should remain visible while scrolling checkbox. Click the OK button.

 

Add one more Row Group, this time for the Customer ID. You should be comfortable doing this now.

 

Go back to Edit Group for the Category Name. Click the checkbox by Group header should remain visible while scrolling and click the OK button. This completes the setup of data to define the rows.

 

Drag the TerritoryName column from the Website Data Sources panel and drop it on the cell labeled Columns. We only need to set this one cell.

 

To define the order quantities and amounts, drag the OrderQty column from the Website Data Sources panel and drop it on the cell labeled Data. To add the amount, right-click the cell with the OrderQty and select Add Column from the pop-up menu. A second column is added, as well as a new row with a label Order Qty above the OrderQty cell. Drag LineTotal to the new cell. A label will be created for us. Change the label to Order Amt.

 

Set all cells to 8 pt. font. Select the order amount cell and set the Format property in the Properties panel to c . Select the TerritoryName cell and center it. Reduce the width of the order quantity column. Set the CustomerID cell to be left-aligned. When you re done, the result should look similar to Figure 8.

 


Figure 8: The Reseller Sales report.

 

Now we need to build the linkage between the summary report and this detail report. Right-click in the Report Designer, but not in the report. Select Report Parameters from the pop-up menu. Click the Add button to add a report parameter. The first parameter expected by the other detail orders SQL statement is named @ProductID. We ll name our report parameter ProductID. The datatype of our parameter needs to be an integer. Click the Add button again. Define a second parameter named CountryRegionCode. Its datatype is a string. Your report parameters should look like they did for the OrderDetailInd report (see Figure 9). Click the OK button, save the report, and close it.

 


Figure 9: Defining the report parameters.

 

Linking the Other Detail Report to the Summary Report

Our summary report is already built to drill-through to one detail report. We need to enhance the linkage to drill-through to the appropriate detail report depending on the column that is clicked.

 

Open the summary report, OrderSummary.rdlc. Right-click the cell that contains =Sum(Fields!TotalOrderAmt.Value). Select Properties from the pop-up menu. The Textbox Properties window will open. Click on the Navigation tab. The Jump to report radiobutton is selected and we have OrderDetailInd in the dropdown list. We need to enhance our logic here. Click the button labeled fx just to the left of the Parameters button. The Edit Expression window will open (see Figure 10).

 


Figure 10: Enhancing the jump expression.

 

Enter the following expression:

 

=IIF(Fields!CustomerType.Value= Individual ,

  OrderDetailInd , OrderDetailOther )

 

This is an immediate IF function with the standard format of (test, true result, false result). If the CustomerType is Individual , we ll link to the report OrderDetailInd. If not, we ll link to the report OrderDetailOther. Click the OK button.

 

We built our reports to use the same parameters; they don t need to be changed. Click the OK button on the Textbox Properties window.

 

The report now has the enhanced linkage in place. We need to add a little code, then we ll be done. Save the report and close the Report Designer.

 

Enhancing the Web Page for Drill-through to Different Detail Reports

Open the Web page that contains the ReportViewer. Now that we ve added another drill-through report, we need an ObjectDataSource for it. Drag to the Web page an ObjectDataSource from the Data section of the Toolkit. Use the SmartTag and select Configure Data Source. In the Configure Data Source window, select the OrderDetailOtherRptTableAdapter in the dropdown list under Choose your business object. Click the Next button. Nothing needs to be changed for Define Data Methods. Click the Next button. You ll see the ProductID and CountryRegionCode parameters in Define Parameters. Do not link the parameters to anything. Click the Finish button.

 

Change the name of this ObjectDataSource to objDetailOther. The Web page now has three ObjectDataSources: one for the original summary report, one for the individual order detail report, and now one for the other order detail report (see Figure 11).

 


Figure 11: Adding the final ObjectDataSource.

 

Finally, we ll enhance the code in the ReportViewer1_Drillthrough event; add a test for the second order detail report (see Figure 12; the new code is in italics).

 

VB

Protected Sub ReportViewer1_Drillthrough(ByVal sender As Object, _

   ByVal e As Microsoft.Reporting.WebForms.DrillthroughEventArgs) _

   Handles ReportViewer1.Drillthrough

   Dim drillthroughReport As LocalReport = _

   CType(e.Report, LocalReport)

   If drillthroughReport.ReportPath.Contains("OrderDetailInd") Then

   objDetailInd.SelectParameters("ProductID").DefaultValue = _

   e.Report.GetParameters("ProductID").Values(0)

     objDetailInd.SelectParameters("CountryRegionCode").DefaultValue = _

 e.Report.GetParameters("CountryRegionCode").Values(0)

     Dim dataSource As New ReportDataSource _

    ("OrderDetailIndRpt_DataTable1", objDetailInd)

     drillthroughReport.DataSources.Add(dataSource)

   ElseIf drillthroughReport.ReportPath.Contains("OrderDetailOther") Then

    objDetailOther.SelectParameters("ProductID").DefaultValue = _

     e.Report.GetParameters("ProductID").Values(0)

    objDetailOther.SelectParameters("CountryRegionCode").DefaultValue = _

     e.Report.GetParameters("CountryRegionCode").Values(0)

    Dim dataSource As New ReportDataSource _

      ("OrderDetailOtherRpt_DataTable1", objDetailOther)

    drillthroughReport.DataSources.Add(dataSource)

    Else

    lblMsg.Text = "The report " + drillthroughReport.ReportPath + _

     " is not recognized."

    End If

   End Sub

 

C#

protected void ReportViewer1_Drillthrough

  (object sender, Microsoft.Reporting.WebForms.DrillthroughEventArgs e)

{

 LocalReport drillthroughReport = (LocalReport)e.Report;

 ReportParameterInfoCollection rptparms =

   drillthroughReport.GetParameters();

 if (drillthroughReport.ReportPath.Contains("OrderDetailInd"))

 {

   objDetailInd.SelectParameters["ProductID"].DefaultValue =

     rptparms["ProductID"].Values[0];

   objDetailInd.SelectParameters["CountryRegionCode"].DefaultValue =

     rptparms["CountryRegionCode"].Values[0];

   ReportDataSource dataSource = new ReportDataSource

      ("OrderDetailIndRpt_DataTable1", objDetailInd);

   drillthroughReport.DataSources.Add(dataSource);

 }

 else if (drillthroughReport.ReportPath.Contains("OrderDetailOther"))

 {

   objDetailOther.SelectParameters["ProductID"].DefaultValue =

     rptparms["ProductID"].Values[0];

   objDetailOther.SelectParameters["CountryRegionCode"].DefaultValue =

     rptparms["CountryRegionCode"].Values[0];

   ReportDataSource dataSource = new ReportDataSource

      ("OrderDetailOtherRpt_DataTable1", objDetailOther);

   drillthroughReport.DataSources.Add(dataSource);

 }

 else

 {

   lblMsg.Text = "The report " + drillthroughReport.ReportPath +

     " is not recognized.";

 }

}

Figure 12: Enhancing the code for the second drill-through report.

 

The report is complete. Run the application and open the Web page. Select a row and click on the total amount under US - Individual. You should see the individual order detail report. Click the browser back button to return to the summary report. Now click on the total amount under US - Other. You should see the other order detail report. Again, go back to the summary report. Click amounts under Non and verify that those also work. If you click the amount for the product Bike Wash - Dissolver under Non - Other, you should see the report shown in Figure 13. If your report doesn t work properly, carefully review all the steps for the second detail report.

 


Figure 13: The Non-US drill-through report.

 

Conclusion

There s a lot of focus on visual interface and application functionality, and rightfully so. However, users generally need reports as part of their application. I ve shown you how you can make your reports an impressive part of your application. The Visual Studio Report Designer and the Web page ReportViewer contain a lot of power. I hope you ve gotten some ideas that you ll be able to use for your next application.

 

Files accompanying this article are available for download.

 

Dennis E. Rehm has been developing software using different languages and tools for 30 years. He s still looking for the silver bullet. He has his own company, Applied Computing, where he does teaching, consulting, and software construction for government, education, and Fortune 500 businesses. You can reach Dennis at mailto:[email protected].

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish