ASP.NET 2.0 Reporting Revisited: Part II

asp:Feature

LANGUAGES:C# | VB.NET

ASP.NETVERSIONS: 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 isto be able to click on a total and go directly to a detail report itemizingthose items that make up the total. We created the first detail report, but didn tlinked them together. If you want to work along, you ll need to have completedthe work in the first article. We re simply going to pick up from the end ofthat article.

Linking the Detail Report to the Summary Report

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

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

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

Figure 1: Creating the jump.

Under Hyperlink action click the Jump to reportradiobutton. Select OrderDetailInd in the dropdown list. Click the Parametersbutton to open the Parameters window (see Figure 2). Here we define our firstparameter. Under Parameter Name enter ProductID. Under Parameter Value, select=Fields!ProductID.Value from the dropdown list. The second parameter is namedCountryRegionCode and its value is =Fields!CountryRegionCode.Value. When you vedefined both parameters, click the OK button. This returns you to the TextboxProperties window. Again, click the OK button.

Figure 2: Defining the jumpparameters.

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

Enhancing the Web Page for Drill-through to the IndividualOrder Detail Report

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

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

Figure 3: Adding anObjectDataSource.

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 thecode page:

VB

Imports Microsoft.Reporting.WebForms

C#

using Microsoft.Reporting.WebForms;

In C# we need to define the drill-through event for theReportViewer. Go to the Web page Designer and click the Source button at thebottom. 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_Drillthroughevent with its code (see Figure 4). In this ReportViewer1_Drillthrough event,we ll link the parameters from the order details SQL statement with the ReportParameters defined for the individual order detail report. This event will befired when the user clicks on an amount cell in the summary report. In thiscase, the ProductID and CountryRegionCode will be passed to the SQL statementto retrieve the requested order rows ID. We are checking for the appropriatereport name as a precaution, as well as preparation for drill-through todifferent reports.   VBProtected Sub ReportViewer1_Drillthrough(ByVal sender As Object,_   ByVal e AsMicrosoft.Reporting.WebForms.DrillthroughEventArgs) _   HandlesReportViewer1.Drillthrough   Dim drillthroughReportAs LocalReport = _   CType(e.Report,LocalReport)    IfdrillthroughReport.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 NewReportDataSource _    ("OrderDetailIndRpt_DataTable1",objDetailInd)      drillthroughReport.DataSources.Add(dataSource)    End If   End Sub   C#protected void ReportViewer1_Drillthrough    (object sender,Microsoft.Reporting.WebForms.DrillthroughEventArgs e) {   LocalReportdrillthroughReport = (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];      ReportDataSourcedataSource = new ReportDataSource        ("OrderDetailIndRpt_DataTable1", objDetailInd);      drillthroughReport.DataSources.Add(dataSource);    } }Figure 4: Writingthe 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 Webpage. Select a row and click on the total amount under US - Individual. Notethe Product Name and the Total Amount. In a moment, the drill-through reportshould 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 thebottom. The total amount on the detail report should match the total amount youclicked on. (Note: If you click on the total amount under Other, you ll stillget the total for Individual. We ll add that enhancement next.)Congratulations! You ve just created a drill-through report.  
Figure 5: The Summary report readyto drill through.   If your report doesn t work properly, carefully review allthe steps regarding the Individual Detail Orders report. When it is working, we reready to enhance the drill-through with a second detail report. This will gomore quickly.  Creating the Detail Dataset for Resellers Again, we start with a dataset to retrieve the requireddata. This time we ll retrieve data about orders for a single Product purchasedby others.   Create a dataset in the App-Code folder namedOrderDetailOtherRpt.xsd. The SQL will retrieve the orders from resellers for asingle Product ID and either US or Non orders. This is done in the WHEREclause with the @ProductID and @CountryRegionCode parameter. This report willdisplay both quantity and amount, whereas the summary report displayed only anamount (see Figure 6).  SELECT Cat.Name AS CategoryName, Subcat.Name AS SubcategoryName,  Det.ProductID, Prod.NameAS ProductName,  Terr.NAME AS TerritoryName,Cust.CustomerID,  Det.OrderQty,Det.LineTotalFROM Sales.SalesOrderHeader HdrINNER JOIN Sales.SalesOrderDetail Det ON Hdr.SalesOrderID =Det.SalesOrderIDINNER JOIN Production.Product Prod ON Det.ProductID =Prod.ProductIDINNER JOIN Production.ProductSubcategory Subcat ONProd.ProductSubcategoryID = Subcat.ProductSubcategoryIDINNER JOIN Production.ProductCategory Cat ONSubcat.ProductCategoryID = Cat.ProductCategoryIDINNER JOIN Sales.SalesTerritory Terr ON Hdr.TerritoryID =Terr.TerritoryIDINNER JOIN Sales.Customer Cust ON Hdr.CustomerID =Cust.CustomerIDWHERE Prod.ProductID = @ProductID AND Cust.CustomerType <>'I' AND(Terr.CountryRegionCode = @CountryRegionCode OR    (Terr.CountryRegionCode<> 'US' AND @CountryRegionCode <> 'US')) ORDER BY TerritoryName, Cust.CustomerIDFigure 6: The SQLstatement for sales to resellers.   This SQL statement is very similar to the SQL statementfor our summary report, except that we are excluding orders from individualsand we are not doing a GROUP BY to create totals. As with the individual detailreport, we are checking the CountryRegionCode in the WHERE clause. We want tomatch US or anything else.   Remember to go into the Advanced Options and uncheck the GenerateInsert, Update and Delete statements box. If you re having problems creatingthis dataset, follow the step-by-step instructions in the Creating the SummaryDataset section in Part I. When you ve finished with the wizard, you ll see thedataset with the column list. Note the symbol of a key by CustomerID. Click onthe CustomerID column and right-click the key beside it. Select Delete key fromthe pop-up menu to delete it. (The Customer ID is not a primary key for thisdataset.) The completed dataset should look like the one in Figure 7.  
Figure 7: The Reseller Salesdataset.  Creating the Other Detail Orders Report Now we can create the report that will be the Other DetailOrders report. When we click on the OrderSummary report to drill-through, we lldetermine whether the amount clicked is for individuals or for others. If theamount is for individuals, we ll open the OrderDetailInd report. If the amountis for others, we ll open the OrderDetailOther report that we re going tobuild.   Add a report named OrderDetailOther to the Reports folder.(See the Creating the Summary Report section in Part I for a more detaileddescription of the process.) Drag a matrix control to the report. Make sure it sin the upper left-hand corner. Then drag the Category column from the WebsiteData Sources panel under OrderDetailOtherRpt and DataTable1 and drop it on thecell labeled Rows.   Next add Subcategory in the rows. Set Name tomatrix1_SubcategoryName, Expression to =Fields!SubcategoryName.Value, Parent groupto =Fields!CategoryName.Value, and check the box next to Group header shouldremain visible while scrolling. (If you ve forgotten how to do this, refer backto the Creating the Summary Report section in Part I.) Click the OK button.   Repeat this process to add Product as a Row Group. SetName to matrix1_ProductName, Expression to =Fields!ProductName.Value, Parent groupto =Fields!SubcategoryName.Value, and check the Group header should remainvisible while scrolling checkbox. Click the OK button.   Add one more Row Group, this time for the Customer ID. Youshould be comfortable doing this now.   Go back to Edit Group for the Category Name. Click thecheckbox by Group header should remain visible while scrolling and click the OKbutton. This completes the setup of data to define the rows.   Drag the TerritoryName column from the Website DataSources panel and drop it on the cell labeled Columns. We only need to set thisone cell.   To define the order quantities and amounts, drag theOrderQty column from the Website Data Sources panel and drop it on the celllabeled Data. To add the amount, right-click the cell with the OrderQty andselect Add Column from the pop-up menu. A second column is added, as well as anew row with a label Order Qty above the OrderQty cell. Drag LineTotal to thenew 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 celland set the Format property in the Properties panel to c . Select theTerritoryName cell and center it. Reduce the width of the order quantitycolumn. Set the CustomerID cell to be left-aligned. When you re done, theresult should look similar to Figure 8.  
Figure 8: The Reseller Sales report.   Now we need to build the linkage between the summaryreport and this detail report. Right-click in the Report Designer, but not inthe report. Select Report Parameters from the pop-up menu. Click the Add buttonto add a report parameter. The first parameter expected by the other detailorders SQL statement is named @ProductID. We ll name our report parameterProductID. The datatype of our parameter needs to be an integer. Click the Addbutton again. Define a second parameter named CountryRegionCode. Its datatypeis a string. Your report parameters should look like they did for theOrderDetailInd report (see Figure 9). Click the OK button, save the report, andclose it.  
Figure 9: Defining the reportparameters.  Linking the Other Detail Report to the Summary Report Our summary report is already built to drill-through toone detail report. We need to enhance the linkage to drill-through to theappropriate detail report depending on the column that is clicked.   Open the summary report, OrderSummary.rdlc. Right-clickthe cell that contains =Sum(Fields!TotalOrderAmt.Value). Select Properties fromthe pop-up menu. The Textbox Properties window will open. Click on theNavigation tab. The Jump to report radiobutton is selected and we haveOrderDetailInd in the dropdown list. We need to enhance our logic here. Clickthe button labeled fx just to the left of the Parameters button. The EditExpression window will open (see Figure 10).  
Figure 10: Enhancing the jumpexpression.   Enter the following expression:  =IIF(Fields!CustomerType.Value= Individual ,   OrderDetailInd , OrderDetailOther )   This is an immediate IF function with the standard formatof (test, true result, false result). If the CustomerType is Individual , we lllink to the report OrderDetailInd. If not, we ll link to the reportOrderDetailOther. Click the OK button.   We built our reports to use the same parameters; they don tneed to be changed. Click the OK button on the Textbox Properties window.   The report now has the enhanced linkage in place. We needto add a little code, then we ll be done. Save the report and close the ReportDesigner.  Enhancing the Web Page for Drill-through to Different DetailReports Open the Web page that contains the ReportViewer. Now thatwe 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 Sourcewindow, select the OrderDetailOtherRptTableAdapter in the dropdown list underChoose your business object. Click the Next button. Nothing needs to be changedfor Define Data Methods. Click the Next button. You ll see the ProductID andCountryRegionCode parameters in Define Parameters. Do not link the parametersto anything. Click the Finish button.   Change the name of this ObjectDataSource to objDetailOther.The Web page now has three ObjectDataSources: one for the original summaryreport, one for the individual order detail report, and now one for the otherorder detail report (see Figure 11).  
Figure 11: Adding the finalObjectDataSource.   Finally, we ll enhance the code in theReportViewer1_Drillthrough event; add a test for the second order detail report(see Figure 12; the new code is in italics).   VBProtected Sub ReportViewer1_Drillthrough(ByVal sender As Object,_   ByVal e AsMicrosoft.Reporting.WebForms.DrillthroughEventArgs) _   HandlesReportViewer1.Drillthrough   Dim drillthroughReportAs LocalReport = _   CType(e.Report,LocalReport)    IfdrillthroughReport.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 NewReportDataSource _    ("OrderDetailIndRpt_DataTable1", objDetailInd)      drillthroughReport.DataSources.Add(dataSource)    ElseIfdrillthroughReport.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) { LocalReportdrillthroughReport = (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];    ReportDataSourcedataSource = 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 = newReportDataSource      ("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 Webpage. Select a row and click on the total amount under US - Individual. Youshould see the individual order detail report. Click the browser back button toreturn 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 summaryreport. Click amounts under Non and verify that those also work. If you clickthe amount for the product Bike Wash - Dissolver under Non - Other, you shouldsee 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-throughreport.  Conclusion There s a lot of focus on visual interface and applicationfunctionality, and rightfully so. However, users generally need reports as partof their application. I ve shown you how you can make your reports animpressive part of your application. The Visual Studio Report Designer and the Webpage ReportViewer contain a lot of power. I hope you ve gotten some ideas thatyou ll be able to use for your next application.   Files accompanyingthis article are available for download.  Dennis E. Rehm hasbeen developing software using different languages and tools for 30 years. He sstill 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].