ASP.NET 2.0 Reporting

CoverStory

LANGUAGES:C# | VB.NET

ASP.NETVERSIONS: 2.0

ASP.NET 2.0 Reporting

Creating Subreports Using the ReportViewer

By Dennis E. Rehm

You ve designed a great interactive Web application nowusers want reports they can print, save, or e-mail. It s a rare Web applicationthat doesn t have some reporting component to it. We re going to look at aMicrosoft solution and use the built-in Visual Studio Report Designer and itscontrol, the ReportViewer. If your Web application is hosted by a service, theymay not support third-party products, but they probably will support theReportViewer.

We will create an advanced report that includes twosubreports. The Visual Studio Report Designer and ReportViewer are included inall versions of Visual Studio 2005, except Express. (If you have Visual WebDeveloper 2005 Express, you can download the Report Designer and ReportVieweras part of a feature pack for SQL Server 2005 at http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en.Scroll to the bottom of the page and look for SQLServer2005_ReportAddin.msi.) Theinterface for the Report Designer is similar to Reporting Services.

We ll first create a report and then embed two subreports.We ll do this using the AdventureWorks OLTP database. (The AdventureWorksdatabase can be downloaded from CodePlex at http://www.codeplex.com/MSFTDBProdSamples.Installation instructions are included.)

The process of creating functioning subreports requiresmeticulous attention to detail. If you do something wrong, your only indicationmay be the message Error: Subreport could not be shown. There are fewdiagnostics to help you pinpoint and resolve the problem. If you encounter thismessage, please review each step carefully. In the worst case, simply deletethe objects and start over.

We ll be using the following three tables from theAdventureWorks database: Product, ProductCostHistory, andProductListPriceHistory. See Figure 1 for the data model. Product has aone-to-many relationship with the other two tables, although not all productshave cost and list-price history. Marketinghas requested a report that shows each product with its cost and list-pricehistory. We re going to build that report together.

Figure 1: Data model for the Product,ProductCostHistory, and ProductListPriceHistory tables.

Creating the Master Dataset

We first must create a dataset with the data required forthe master report: the Products. We ll create a SQL Server log-in to access thedata in the AdventureWorks database. I named my log-in ReportApp, gave it thepassword reportapp, and assigned it the db_owner role in the AdventureWorksdatabase. This is not how you would structure database permissions in aproduction system, but it will allow us to focus on building the report ratherthan setting up security for this example.

Go to the Solution Explorer and right-click in theproject. Select Add New Item from the menu. Select Dataset from the Add NewItem window and name it ProductHistoryRpt.xsd. Click the Add button.

If you don t have an App_Code folder in your project,Visual Studio will ask you to create one. Your ProductHistoryRpt dataset willbe placed in the App_Code folder.

The TableAdapter Configuration Wizard will open and askyou to select a connection (see Figure 2). Click the New Connection button. TheAdd Connection window will open. Enter or select the server where you installedthe AdventureWorks database. Select the Use SQL Server Authentication radio button.Enter The SQL Server log-in and password you created. I used ReportApp as theUser Name and reportapp as the Password. Check the Save my password box and theSelect or enter a database name radio button (select the AdventureWorksdatabase in the dropdown list). You can click the Test Connection button to becertain, but if you can see the AdventureWorks database in the dropdown list,you ve successfully made a connection to the server (your window should looklike Figure 3). Click the OK button to return to the TableAdapter ConfigurationWizard with your new connection selected.

Figure 2: Select a connection.

Figure 3: The Add Connection dialogbox.

Click the Next button. You ll be asked to save theconnection string to the configuration file. You should do so. The default nameis fine.

Next, choose a command type. Select the Use SQL Statementsradio button (see Figure 4). For this example, we ll use SQL statements toretrieve the data. In a production system I prefer stored procedures, which canbe granted permissions independent of the underlying tables (but using SQLstatements is simpler for our purposes here). Click the Next button.

Figure 4: Choose a command type.

If you re familiar with the data model and the SQLstatement is short, you can type the SQL statement. Otherwise, click the QueryBuilder button and build the SQL statement graphically. We ll restrict ourresult set to ProductID greater than 700 (because those products have morehistory):

SELECT ProductID, Name, ProductNumber, Color,

   StandardCost, ListPrice

FROM Production.Product

WHERE ProductID > 700

Click the Advanced Options button and uncheck the boxGenerate Insert, Update and Delete statements (see Figure 5). Because this is areport, no data modification statements will be required. Click the OK buttonto close that window and then click the Next button.

Figure 5: The Advanced Optionsdialog box.

Uncheck the box by Create methods to send updates directlyto the database (see Figure 6). Again, no data will be modified when generatingthe report. Click the Next button.

Figure 6: The TableAdapterConfiguration Wizard.

The wizard will display a summary of the items to begenerated. Click the Finish button. Your dataset should look like the one inFigure 7. Save and close your dataset. You ve now created the master datasetthat will retrieve the data to drive the report.

Figure 7: The dataset.

Creating the Master Report

We ll place all our reports in a separate folder withinthe project. In the Solution Explorer, right-click the project and select NewFolder from the pop-up menu. Name the folder Reports. Right-click this newReports folder and select Add New Item. Select Report from the installedtemplates and name it ProductHistory.rdlc. Click the Add button. The ReportDesigner will open and you should see ProductHistory.rdlc in the Reports folderin the Solution Explorer. If not, close the Report Designer, then drag thereport and drop it in the Reports folder. Then double-click it to open it againin the Report Designer.

The Report Designer has a panel labeled Website DataSources. If it s not displayed for you, go to the menu at the top of the windowand select the Data menu option. Under that is a single menu item: Show DataSources; select it and the Website Data Sources panel will be displayed. If it snot docked, you can dock it. The lower left corner is its usual home. OurProductHistoryRpt dataset is displayed as a Website Data Source. Expand Productand you can see the data elements we selected in our SQL statement (see Figure8).

Figure 8: The data elements weselected in our SQL statement.

We are going to create the master report using a tableformat. Drag the Table control from the Report Items toolbox and drop it on thereport in the Report Designer. By default the table has a Header row, a Detailsrow, and a Footer row. It also has three columns. We will not need the Footerrow. Right-click on the row descriptor column of the table and unselect TableFooter. It will disappear. (If you do not see the row descriptors, clickanywhere inside the Table control.) Table controls are very easy to populate simplydrag a column from the Website Data Sources and drop it on the Details row inthe column where you want it. For our report, we want to drag Name, ProductNumber, Color, StandardCost, and ListPrice. (Note that the ProductID is notused in the report. It is there simply for reference by the subreports.) Eachdata element is dropped in the Details row and a label is placed in the Headerrow based on the column name. This Header label can be changed.

You ll notice there are five columns of data, but onlythree columns in our table. Right-click in the area above the Header row andselect Insert Column to the Right. Do it a second time; now we have our fivetable columns for our five data columns. I made my Heading labels bold andadjusted the width and text of the labels to better match the data. I also liketo use 8 pt. type for reports. Click on the List Price data element and in theProperties panel set the Format to c. This will display the amount with adollar sign and two decimal places.

Your report should now look similar to Figure 9. Becauseinformation about the connection to the database is stored in the web.config,we won t be able to preview the report here in the Report Designer. We ll lookat the report as part of a Web page in the next step. Save the report and closethe Report Designer.

Figure 9: Report design.

Displaying the Report

Open the Web page that will contain the report (I m goingto use default.aspx). Place a label on the page and name it lblMsg. It shouldcontain no text. We ll use this label to display messages later.

Drag a ReportViewer control from the Data section of theToolbox and drop it on the Web page. The first time you use a ReportViewer inyour application, your web.config file will be modified to include referencesto the ReportViewer assemblies and provider. This is done automatically.

The ReportViewer SmartTag will be open; you can select areport to associate with the ReportViewer. Select the ProductHistory report inthe Choose Report dropdown list. When you select the ProductHistory report, you llsee an ObjectDataSource created on the Web page and a Choose Data Sources itemwill be added to the SmartTag. The ObjectDataSource has an ID ofObjectDataSource1. We want to give it a more meaningful ID. Click on the ObjectDataSource;in the Properties panel, change its ID to objProducts.

Go back to the ReportViewer SmartTag and click on it.Select Choose Data Sources. A window opens to allow us to map the Report DataSources to Data Source Instances on the Web page. Note that our Report DataSource has a name that is the name of the dataset (ProductHistoryRpt), with thename of the datatable concatenated to it (ProductHistoryRpt_Products). Again, seeFigure 8 and note the names of the dataset and datatable. This will be usedlater in our subreport. However, because we renamed our ObjectDataSource, themapping is incorrect. Click the Data Source Instances and choose objProductsfrom the dropdown list. Click the OK button and the mapping is complete.

You can make the ReportViewer control wider and taller asneeded for the report. See Figure 10 for my Web page with the controls on it.We are now ready to test the basic report. Run the Web application and go tothe Web page with the report. It may be easiest if you set the report Web pageas the Start Page. Because the SQL statement for our ProductHistoryRpt datasethas no criteria, it will be executed as soon as the page is built. You shouldsee the Web page with the report populated with data (see Figure 11 for anexample).

Figure 10: My Web page with thecontrols on it.

Figure 11: The Web page with thereport populated with data.

If you have errors connecting to the database, openweb.config and verify your connection string. Be sure to use your server nameand the User ID and Password you have for the AdventureWorks database:

     connectionString="Data Source=YourServerName;         InitialCatalog=AdventureWorks;         IntegratedSecurity=False;         UserID=ReportApp;Password=reportapp"    providerName="System.Data.SqlClient"/>   You must have the basic report working in order tocontinue. When you have your report displaying in the Web page, you are readyto add your first subreport.  Creating the Cost History Dataset Let s start the first subreport. Again, we start with adataset to retrieve the data required for the subreport. This time, we llretrieve data about the Cost History of a Product. Create a dataset in the App-Codefolder named ProductCostHistoryRpt.xsd. The SQL will retrieve the cost historyrows for a single Product ID. This is done in the WHERE clause with the@ProductID parameter:  SELECT ProductID, StartDate, EndDate,    StandardCost, ModifiedDateFROM Production.ProductCostHistoryWHERE ProductID = @ProductIDORDER BY StartDate DESC   Remember to go into the Advanced Options and uncheck thebox Generate Insert, Update and Delete statements. Also, uncheck Create methodsto send updates directly to the database. If you re having problems creatingthis dataset, follow the step-by-step instructions in the earlier section, Creatingthe Master Dataset. The completed dataset should look like Figure 12.  
Figure 12: The completed dataset.  Creating the Cost History Report Now we can create the report that will be the Cost Historysubreport. It is designed like our ProductHistory report. Add a report namedProductCostHistory to the Reports folder. (See the earlier section Creating theMaster Report for a more detailed description of this process.)   Drag a Table control to the report and drag the StartDate,EndDate, StandardCost, and ModifiedDate columns from the ProductCostHistoryRptdataset in the Website Data Sources to the report columns in the Table controlon the report. You ll need to add a column for the master report. You also can removethe Footer row. Once again, we re not using the ProductID in the report, wehave it simply for reference.   I used an 8 pt. font for the report. Also, I made the headingbackground dark gray and the foreground white. This creates a divider whenviewing the subreport within the report. Click on each of the three dates andset the Format property in the Properties panel to d. This will display thedate in the common month/day/year format and suppress the time part of thedatetime. When you re done, the result should look similar to Figure 13.  
Figure 13: Report design.   Now we need to build something to link the product in themaster report to its cost history in this subreport. Right-click in the ReportDesigner, but not in the report. Select Report Parameters from the pop-up menu.Add a report parameter for the parameter expected by the cost history SQLstatement, which we named @ProductID. The name of the report parameter does notneed to match the SQL parameter, but it makes everything clearer if it does. We llname our report parameter ProductID. The datatypes of the SQL parameter andreport parameter must match. In this case, the ProductID in the database is aninteger, so our parameter needs to be an integer. Your report parameter shouldlook like Figure 14. Click the OK button, save the report, and close it.  
Figure 14: The report parameter.  Adding the First Subreport to the Report Open the master report, ProductHistory.rdlc. We are usinga Table control to display the data in our master report. We ll need to enhancethe Table control to add a subreport. First, we need a second Details row.Click on the Table control so the borders are visible. Right-click on theDetails row descriptor and select Insert Row Below. A second Details row willbe displayed. This row has five columns just like the row above it. We want tomerge the four columns on the right, the columns for Product Number, Color,Standard Cost, and List Price. Click in the second Details row under ProductNumber and, holding the mouse button down, slide your cursor over the threecolumns to the right. When you release the mouse button, the four right-mostcolumns in the new, second Details row should be selected. Right-click withinthe selected area and select Merge Cells. Now we have one wide column perfectfor a subreport. (We re using the four right columns so the subreport isindented from the Product information above it.)   Drag a subreport control to the new single merged cell.The cell will turn gray. Right-click the subreport and select Properties fromthe pop-up menu. If the General tab is not selected, click it to select it.Enter CostHistory as the name and select ProductCostHistory in the Subreportdropdown list (see Figure 15). Click the Parameters tab. Under Parameter Name,type ProductID; under Parameter Value, select =Fields!ProductID.Value from thedropdown list (see Figure 16). This will map the cost history report parameterto the data column in the master report.  
Figure 15: Enter CostHistory andselect ProductCostHistory.  
Figure 16: Map the cost historyreport parameter to the data column in the master report.   The report now has the cost history subreport in place. Toidentify the subreport as cost history, enter Cost History: in the tablecontrol cell under the Product Name (see Figure 17). Save the report and closethe Report Designer.  
Figure 17: Identify the subreport ascost history.  Enhancing the Web Page for the Subreport Open the Web page that contains the ReportViewer. Now thatwe ve added a subreport, we need an ObjectDataSource for it. Drag to the Webpage an ObjectDataSource from the Data section of the Toolkit. Use the SmartTagand select Configure Data Source. In the Configure Data Source window, selectthe ProductCostHistoryTableAdapter 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 parameter inDefine Parameters. Do not try to link the parameter to anything. Click theFinish button.   We want to give this ObjectDataSource a more descriptiveID. Select it and in the Properties panel, change the ID to objProductCost. TheWeb page now has two ObjectDataSources, one for the original master report andone for the subreport (see Figure 18).  
Figure 18: The Web page now has twoObjectDataSources.   With the Cost History subreport embedded in our masterreport, and with an ObjectDataSource in place to retrieve the cost historydata, we are ready to wire things together. Go to the code for the Web page.Add an Imports statement for Reporting:   VBImports Microsoft.Reporting.WebForms   C#using Microsoft.Reporting.WebForms;   In the Load event for the page, create an event handler toprocess the subreport. This code should be executed every time (whether thepage is a postback or not):   VBAddHandler ReportViewer1.LocalReport.SubreportProcessing, _ AddressOfReportViewer1_SubreportProcessing   C#ReportViewer1.LocalReport.SubreportProcessing +=   new SubreportProcessingEventHandler( ReportViewer1_SubreportProcessing);   We ve created an event handler name by concatenating thename of the control and the name of the event. In this case, it sReportViewer1_SubreportProcessing.   In the ReportViewer1_SubreportProcessing event, we ll linkthe parameter from the cost history SQL statement with the Report Parametersdefined for the cost history subreport within the master report. This eventwill be fired once for each master row. In this case, the Product ID will bepassed to the SQL statement to retrieve cost history rows for that Product ID(see Figure 19).   VBProtected Sub ReportViewer1_SubreportProcessing _  (ByVal sender As Object,_  ByVal e AsSubreportProcessingEventArgs)  Try   objProductCost.SelectParameters("ProductID").DefaultValue = _     e.Parameters("ProductID").Values(0)  Catch ex As Exception   lblMsg.Text =ex.Message End Try e.DataSources.Add(NewReportDataSource _    ("ProductCostHistoryRpt_ProductCostHistory", _    objProductCost.ID)) End Sub   C#protected void ReportViewer1_SubreportProcessing    (object sender,SubreportProcessingEventArgs e) { try {   objProductCost.SelectParameters["ProductID"].DefaultValue =   e.Parameters["ProductID"].Values[0];  } catch (Exception ex) {   lblMsg.Text =ex.Message;  } e.DataSources.Add(newReportDataSource    ("ProductCostHistoryRpt_ProductCostHistory",    objProductCost.ID)); }Figure 19: ProductID passed to the SQL statement to retrieve cost history rows for that ProductID.   Note the name of the ReportDataSource in the laststatement. It is ProductCostHistoryRpt_ProductCostHistory , which is the nameof the dataset concatenated with the name of the datatable. We looked at this inthe earlier section, Displaying the Report. This is important to understand asyou create your own subreports. Because it is a literal, IntelliSense cannothelp you.   At this point, if you are using Visual Basic, your codefor the Web page should look like the code in Figure 20. When you run theapplication and open the Web page, the page should look like Figure 21. If itdoesn t, carefully review all the steps regarding the Cost History subreport.When it is working, we re ready to add a second subreport. This will go morequickly.  
Figure 20: Code for the Web pageusing Visual Basic.  
Figure 21: Run the application andopen the Web page.  Creating the List Price History Dataset Again, we start with a dataset to retrieve the datarequired for the subreport. This time we ll retrieve data about the List PriceHistory of a Product. Create a dataset named ProductListPriceHistoryRpt. Usethe following SQL statement:  SELECT ProductID, StartDate, EndDate,    ListPrice, ModifiedDateFROM Production.ProductListPriceHistoryWHERE ProductID = @ProductIDORDER BY StartDate DESC   Remember to uncheck the options that would generateadditional SQL statements and database methods. If you re having problemscreating this dataset, follow the step-by-step instructions in the earliersection, Creating the Master Dataset.  Creating the List Price History Report Add to the Reports folder a report namedProductListPriceHistory. See the earlier section Creating the Master Report fora more detailed description of the process.   Drag a Table control to the report and drag the StartDate,EndDate, ListPrice, and ModifiedDate columns from theProductListPriceHistoryRpt dataset in the Website Data Sources to the reportcolumns in the Table control on the report. You ll need to add a column as wedid for the cost history report. You also can remove the Footer row. Once again,we are not using the ProductID in the report.   I used an 8 pt. font for the report, a dark graybackground for the heading, and a white foreground. This creates a divider whenviewing the subreport within the report. Click on each of the three dates andset the Format property in the Properties panel to d. Click on the List Pricedata element and set the Format property to c.   Right-click in the Report Designer, but not in the report.Select Report Parameters from the pop-up menu. Add a report parameter for theSQL statement, which we named @ProductID. Again, we ll name our reportparameter ProductID. The parameter needs to be an integer. Click the OK button,save the report, and close it.  Adding the Second Subreport to the Report Open the master report, ProductHistory.rdlc. We added asecond Details row for the Cost History subreport. We ll add another Detailsrow for the List Price History subreport. Click on the Table control so theborders are visible. Right-click on the second Details row descriptor andselect Insert Row Below. Merge the four columns on the right as we did for thecost history.   Drag a subreport control to the new single merged cell.The cell will turn gray. Right-click the subreport and select Properties fromthe pop-up menu. On the General tab enter ListPriceHistory as the name andselect ProductListPriceHistory in the Subreport dropdown list. Click theParameters tab. Under Parameter Name type ProductID and under Parameter Valueselect =Fields!ProductID.Value from the dropdown list.   The report now has the list-price history subreport inplace. Add List Price History: in the cell to the left of the List PriceHistory subreport. See Figure 22 for the completed report. Save the report andclose the Report Designer.  
Figure 22: The completed report.  Enhancing the Web Page for the Second Subreport Open the report Web page. Drag an ObjectDataSource to the Webpage for the second subreport. Use the SmartTag and set Configure Data Sourceto the ProductListPriceHistoryTableAdapter. No other changes are needed.   Rename the ObjectDataSource ID to objProductListPrice. TheWeb page now has three ObjectDataSources, one for the original master reportand one for each of the subreports.   Because we have one subreport on our Web page, our codewill need to differentiate between the subreports. We ll do this in theReportViewer1_SubreportProcessing event. The ReportPath allows us to identifywhich subreport we are processing (see Figure 23).   VBTry If e.ReportPath ="ProductCostHistory" Then  objProductCost.SelectParameters("ProductID").DefaultValue = _      e.Parameters("ProductID").Values(0)   e.DataSources.Add(NewReportDataSource _        ("ProductCostHistoryRpt_ProductCostHistory",_        objProductCost.ID))  ElseIf e.ReportPath ="ProductListPriceHistory" Then   objProductListPrice.SelectParameters("ProductID").DefaultValue= _       e.Parameters("ProductID").Values(0)    e.DataSources.Add(NewReportDataSource _        ("ProductListPriceHistoryRpt_ProductListPriceHistory", _        objProductListPrice.ID))   End If Catch ex As Exception lblMsg.Text = ex.Message End Try   C#try {   if (e.ReportPath =="ProductCostHistory") {     objProductCost.SelectParameters["ProductID"].DefaultValue=    e.Parameters["ProductID"].Values[0];      e.DataSources.Add(newReportDataSource    ("ProductCostHistoryRpt_ProductCostHistory",    objProductCost.ID));    }   else if (e.ReportPath== "ProductListPriceHistory") {     objProductListPrice.SelectParameters["ProductID"].DefaultValue=    e.Parameters["ProductID"].Values[0];      e.DataSources.Add(newReportDataSource    ("ProductListPriceHistoryRpt_ProductListPriceHistory",     objProductListPrice.ID));    } } catch (Exception ex) {   lblMsg.Text =ex.Message;  } }}Figure 23: Identifywhich subreport we are processing.   If you ve done everything right, when you run theapplication and open the Web page, the page should look like Figure 24.Congratulations! You ve created a report with two embedded subreports. If thereport doesn t work properly, carefully review the steps to add the List PriceHistory subreport.  
Figure 24: The finished product.  Conclusion The Visual Studio Report Designer and the Web pageReportViewer contain a lot of power. Getting access to that power requires alittle code and some attention to detail. We could add selection criteria tothe Web page and allow the user to define the products they d like to see.There are other enhancements we could make, but this will get you started withsubreports.   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].