ASP.NET 2.0 Reporting

Creating Subreports Using the ReportViewer

CoverStory

LANGUAGES: C# | VB.NET

ASP.NET VERSIONS: 2.0

 

ASP.NET 2.0 Reporting

Creating Subreports Using the ReportViewer

 

By Dennis E. Rehm

 

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

 

We will create an advanced report that includes two subreports. The Visual Studio Report Designer and ReportViewer are included in all versions of Visual Studio 2005, except Express. (If you have Visual Web Developer 2005 Express, you can download the Report Designer and ReportViewer as 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.) The interface 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 AdventureWorks database can be downloaded from CodePlex at http://www.codeplex.com/MSFTDBProdSamples. Installation instructions are included.)

 

The process of creating functioning subreports requires meticulous attention to detail. If you do something wrong, your only indication may be the message Error: Subreport could not be shown. There are few diagnostics to help you pinpoint and resolve the problem. If you encounter this message, please review each step carefully. In the worst case, simply delete the objects and start over.

 

We ll be using the following three tables from the AdventureWorks database: Product, ProductCostHistory, and ProductListPriceHistory. See Figure 1 for the data model. Product has a one-to-many relationship with the other two tables, although not all products have cost and list-price history. Marketing has requested a report that shows each product with its cost and list-price history. 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 for the master report: the Products. We ll create a SQL Server log-in to access the data in the AdventureWorks database. I named my log-in ReportApp, gave it the password reportapp, and assigned it the db_owner role in the AdventureWorks database. This is not how you would structure database permissions in a production system, but it will allow us to focus on building the report rather than setting up security for this example.

 

Go to the Solution Explorer and right-click in the project. Select Add New Item from the menu. Select Dataset from the Add New Item 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 will be placed in the App_Code folder.

 

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

 


Figure 2: Select a connection.

 


Figure 3: The Add Connection dialog box.

 

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

 

Next, choose a command type. Select the Use SQL Statements radio button (see Figure 4). For this example, we ll use SQL statements to retrieve the data. In a production system I prefer stored procedures, which can be granted permissions independent of the underlying tables (but using SQL statements 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 SQL statement is short, you can type the SQL statement. Otherwise, click the Query Builder button and build the SQL statement graphically. We ll restrict our result set to ProductID greater than 700 (because those products have more history):

 

SELECT ProductID, Name, ProductNumber, Color,

   StandardCost, ListPrice

FROM Production.Product

WHERE ProductID > 700

 

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

 


Figure 5: The Advanced Options dialog box.

 

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

 


Figure 6: The TableAdapter Configuration Wizard.

 

The wizard will display a summary of the items to be generated. Click the Finish button. Your dataset should look like the one in Figure 7. Save and close your dataset. You ve now created the master dataset that 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 within the project. In the Solution Explorer, right-click the project and select New Folder from the pop-up menu. Name the folder Reports. Right-click this new Reports folder and select Add New Item. Select Report from the installed templates and name it ProductHistory.rdlc. Click the Add button. The Report Designer will open and you should see ProductHistory.rdlc in the Reports folder in the Solution Explorer. If not, close the Report Designer, then drag the report and drop it in the Reports folder. Then double-click it to open it again in the Report Designer.

 

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

 


Figure 8: The data elements we selected in our SQL statement.

 

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

 

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

 

Your report should now look similar to Figure 9. Because information 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 look at the report as part of a Web page in the next step. Save the report and close the Report Designer.

 


Figure 9: Report design.

 

Displaying the Report

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

 

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

 

The ReportViewer SmartTag will be open; you can select a report to associate with the ReportViewer. Select the ProductHistory report in the Choose Report dropdown list. When you select the ProductHistory report, you ll see an ObjectDataSource created on the Web page and a Choose Data Sources item will be added to the SmartTag. The ObjectDataSource has an ID of ObjectDataSource1. 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 Data Sources to Data Source Instances on the Web page. Note that our Report Data Source has a name that is the name of the dataset (ProductHistoryRpt), with the name of the datatable concatenated to it (ProductHistoryRpt_Products). Again, see Figure 8 and note the names of the dataset and datatable. This will be used later in our subreport. However, because we renamed our ObjectDataSource, the mapping is incorrect. Click the Data Source Instances and choose objProducts from the dropdown list. Click the OK button and the mapping is complete.

 

You can make the ReportViewer control wider and taller as needed 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 to the Web page with the report. It may be easiest if you set the report Web page as the Start Page. Because the SQL statement for our ProductHistoryRpt dataset has no criteria, it will be executed as soon as the page is built. You should see the Web page with the report populated with data (see Figure 11 for an example).

 


Figure 10: My Web page with the controls on it.

 


Figure 11: The Web page with the report populated with data.

 

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

 

 

   connectionString="Data Source=YourServerName;

        Initial Catalog=AdventureWorks;

        Integrated Security=False;

        User ID=ReportApp;Password=reportapp"

   providerName="System.Data.SqlClient"/>

 

You must have the basic report working in order to continue. When you have your report displaying in the Web page, you are ready to add your first subreport.

 

Creating the Cost History Dataset

Let s start the first subreport. Again, we start with a dataset to retrieve the data required for the subreport. This time, we ll retrieve data about the Cost History of a Product. Create a dataset in the App-Code folder named ProductCostHistoryRpt.xsd. The SQL will retrieve the cost history rows for a single Product ID. This is done in the WHERE clause with the @ProductID parameter:

 

SELECT ProductID, StartDate, EndDate,

   StandardCost, ModifiedDate

FROM Production.ProductCostHistory

WHERE ProductID = @ProductID

ORDER BY StartDate DESC

 

Remember to go into the Advanced Options and uncheck the box Generate Insert, Update and Delete statements. Also, uncheck Create methods to send updates directly to the database. If you re having problems creating this dataset, follow the step-by-step instructions in the earlier section, Creating the 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 History subreport. It is designed like our ProductHistory report. Add a report named ProductCostHistory to the Reports folder. (See the earlier section Creating the Master 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 ProductCostHistoryRpt dataset in the Website Data Sources to the report columns in the Table control on the report. You ll need to add a column for the master report. You also can remove the Footer row. Once again, we re not using the ProductID in the report, we have it simply for reference.

 

I used an 8 pt. font for the report. Also, I made the heading background dark gray and the foreground white. This creates a divider when viewing the subreport within the report. Click on each of the three dates and set the Format property in the Properties panel to d. This will display the date in the common month/day/year format and suppress the time part of the datetime. 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 the master report to its cost history in this subreport. Right-click in the Report Designer, 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 SQL statement, which we named @ProductID. The name of the report parameter does not need to match the SQL parameter, but it makes everything clearer if it does. We ll name our report parameter ProductID. The datatypes of the SQL parameter and report parameter must match. In this case, the ProductID in the database is an integer, so our parameter needs to be an integer. Your report parameter should look 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 using a Table control to display the data in our master report. We ll need to enhance the 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 the Details row descriptor and select Insert Row Below. A second Details row will be displayed. This row has five columns just like the row above it. We want to merge the four columns on the right, the columns for Product Number, Color, Standard Cost, and List Price. Click in the second Details row under Product Number and, holding the mouse button down, slide your cursor over the three columns to the right. When you release the mouse button, the four right-most columns in the new, second Details row should be selected. Right-click within the selected area and select Merge Cells. Now we have one wide column perfect for a subreport. (We re using the four right columns so the subreport is indented 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 from the 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 Subreport dropdown list (see Figure 15). Click the Parameters tab. Under Parameter Name, type ProductID; under Parameter Value, select =Fields!ProductID.Value from the dropdown list (see Figure 16). This will map the cost history report parameter to the data column in the master report.

 


Figure 15: Enter CostHistory and select ProductCostHistory.

 


Figure 16: Map the cost history report parameter to the data column in the master report.

 

The report now has the cost history subreport in place. To identify the subreport as cost history, enter Cost History: in the table control cell under the Product Name (see Figure 17). Save the report and close the Report Designer.

 


Figure 17: Identify the subreport as cost history.

 

Enhancing the Web Page for the Subreport

Open the Web page that contains the ReportViewer. Now that we ve added a subreport, 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 ProductCostHistoryTableAdapter 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 parameter in Define Parameters. Do not try to link the parameter 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 objProductCost. The Web page now has two ObjectDataSources, one for the original master report and one for the subreport (see Figure 18).

 


Figure 18: The Web page now has two ObjectDataSources.

 

With the Cost History subreport embedded in our master report, and with an ObjectDataSource in place to retrieve the cost history data, we are ready to wire things together. Go to the code for the Web page. Add an Imports statement for Reporting:

 

VB

Imports Microsoft.Reporting.WebForms

 

C#

using Microsoft.Reporting.WebForms;

 

In the Load event for the page, create an event handler to process the subreport. This code should be executed every time (whether the page is a postback or not):

 

VB

AddHandler ReportViewer1.LocalReport.SubreportProcessing, _

 AddressOf ReportViewer1_SubreportProcessing

 

C#

ReportViewer1.LocalReport.SubreportProcessing +=

   new SubreportProcessingEventHandler(

 ReportViewer1_SubreportProcessing);

 

We ve created an event handler name by concatenating the name of the control and the name of the event. In this case, it s ReportViewer1_SubreportProcessing.

 

In the ReportViewer1_SubreportProcessing event, we ll link the parameter from the cost history SQL statement with the Report Parameters defined for the cost history subreport within the master report. This event will be fired once for each master row. In this case, the Product ID will be passed to the SQL statement to retrieve cost history rows for that Product ID (see Figure 19).

 

VB

Protected Sub ReportViewer1_SubreportProcessing _

  (ByVal sender As Object, _

  ByVal e As SubreportProcessingEventArgs)

 Try

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

     e.Parameters("ProductID").Values(0)

 Catch ex As Exception

   lblMsg.Text = ex.Message

 End Try

 e.DataSources.Add(New ReportDataSource _

    ("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(new ReportDataSource

    ("ProductCostHistoryRpt_ProductCostHistory",

   objProductCost.ID));

}

Figure 19: Product ID passed to the SQL statement to retrieve cost history rows for that Product ID.

 

Note the name of the ReportDataSource in the last statement. It is ProductCostHistoryRpt_ProductCostHistory , which is the name of the dataset concatenated with the name of the datatable. We looked at this in the earlier section, Displaying the Report. This is important to understand as you create your own subreports. Because it is a literal, IntelliSense cannot help you.

 

At this point, if you are using Visual Basic, your code for the Web page should look like the code in Figure 20. When you run the application and open the Web page, the page should look like Figure 21. If it doesn 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 more quickly.

 


Figure 20: Code for the Web page using Visual Basic.

 


Figure 21: Run the application and open the Web page.

 

Creating the List Price History Dataset

Again, we start with a dataset to retrieve the data required for the subreport. This time we ll retrieve data about the List Price History of a Product. Create a dataset named ProductListPriceHistoryRpt. Use the following SQL statement:

 

SELECT ProductID, StartDate, EndDate,

   ListPrice, ModifiedDate

FROM Production.ProductListPriceHistory

WHERE ProductID = @ProductID

ORDER BY StartDate DESC

 

Remember to uncheck the options that would generate additional SQL statements and database methods. If you re having problems creating this dataset, follow the step-by-step instructions in the earlier section, Creating the Master Dataset.

 

Creating the List Price History Report

Add to the Reports folder a report named ProductListPriceHistory. See the earlier section Creating the Master Report for a more detailed description of the process.

 

Drag a Table control to the report and drag the StartDate, EndDate, ListPrice, and ModifiedDate columns from the ProductListPriceHistoryRpt dataset in the Website Data Sources to the report columns in the Table control on the report. You ll need to add a column as we did 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 gray background for the heading, and a white foreground. This creates a divider when viewing the subreport within the report. Click on each of the three dates and set the Format property in the Properties panel to d. Click on the List Price data 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 the SQL statement, which we named @ProductID. Again, we ll name our report parameter 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 a second Details row for the Cost History subreport. We ll add another Details row for the List Price History subreport. Click on the Table control so the borders are visible. Right-click on the second Details row descriptor and select Insert Row Below. Merge the four columns on the right as we did for the cost history.

 

Drag a subreport control to the new single merged cell. The cell will turn gray. Right-click the subreport and select Properties from the pop-up menu. On the General tab enter ListPriceHistory as the name and select ProductListPriceHistory in the Subreport dropdown list. Click the Parameters tab. Under Parameter Name type ProductID and under Parameter Value select =Fields!ProductID.Value from the dropdown list.

 

The report now has the list-price history subreport in place. Add List Price History: in the cell to the left of the List Price History subreport. See Figure 22 for the completed report. Save the report and close 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 Web page for the second subreport. Use the SmartTag and set Configure Data Source to the ProductListPriceHistoryTableAdapter. No other changes are needed.

 

Rename the ObjectDataSource ID to objProductListPrice. The Web page now has three ObjectDataSources, one for the original master report and one for each of the subreports.

 

Because we have one subreport on our Web page, our code will need to differentiate between the subreports. We ll do this in the ReportViewer1_SubreportProcessing event. The ReportPath allows us to identify which subreport we are processing (see Figure 23).

 

VB

Try

 If e.ReportPath = "ProductCostHistory" Then

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

      e.Parameters("ProductID").Values(0)

  e.DataSources.Add(New ReportDataSource _

        ("ProductCostHistoryRpt_ProductCostHistory", _

        objProductCost.ID))

 ElseIf e.ReportPath = "ProductListPriceHistory" Then

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

       e.Parameters("ProductID").Values(0)

   e.DataSources.Add(New ReportDataSource _

        ("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(new ReportDataSource

    ("ProductCostHistoryRpt_ProductCostHistory",

   objProductCost.ID));

   }

   else if (e.ReportPath == "ProductListPriceHistory") {

     objProductListPrice.SelectParameters["ProductID"].DefaultValue =

    e.Parameters["ProductID"].Values[0];

     e.DataSources.Add(new ReportDataSource

    ("ProductListPriceHistoryRpt_ProductListPriceHistory",

    objProductListPrice.ID));

   }

 }

 catch (Exception ex) {

   lblMsg.Text = ex.Message;

 }

 }

}

Figure 23: Identify which subreport we are processing.

 

If you ve done everything right, when you run the application and open the Web page, the page should look like Figure 24. Congratulations! You ve created a report with two embedded subreports. If the report doesn t work properly, carefully review the steps to add the List Price History subreport.

 


Figure 24: The finished product.

 

Conclusion

The Visual Studio Report Designer and the Web page ReportViewer contain a lot of power. Getting access to that power requires a little code and some attention to detail. We could add selection criteria to the 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 with subreports.

 

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