Tutorial 15: Displaying Summary Information in the GridView's Footer

 

Scott Mitchell

March 2007

Summary: In this tutorial, we'll create a page that lists the categories in a drop-down list with the selected category's products displayed in a GridView. The GridView will include a footer row that shows the average price and total number of units in stock and on order for products in that category. (15 printed pages)

Download the ASPNET_Data_Tutorial_15_CS.exe sample code.

Download the ASPNET_Data_Tutorial_15_CS.exe sample code.

Contents of Tutorial 15 (Visual C#)

Introduction
Step 1: Adding the Categories DropDownList and Products GridView
Step 2: Displaying a Footer in the GridView
Step 3: Computing the Summary Data
Step 4: Displaying the Summary Data in the Footer
Conclusion

Introduction

In addition to seeing each of the products' prices, units in stock, units on order, and reorder levels, a user might also be interested in aggregate information, such as the average price, the total number of units in stock, and so on. Such summary information is often displayed at the bottom of the report in a summary row. The GridView control can include a footer row into whose cells we can programmatically inject aggregate data.

This task presents us with three challenges:

  1. Configuring the GridView to display its footer row
  2. Determining the summary data; that is, how to compute the average price or the total of the units in stock
  3. Injecting the summary data into the appropriate cells of the footer row

In this tutorial, we'll see how to overcome these challenges. Specifically, we'll create a page that lists the categories in a drop-down list with the selected category's products displayed in a GridView. The GridView will include a footer row that shows the average price and total number of units in stock and on order for products in that category.

Figure 1. Summary information is displayed in the GridView's footer row.

This tutorial, with its category-to-products master/detail interface, builds upon the concepts that were covered in the earlier Master/Detail Filtering with a DropDownList tutorial. If you have not yet worked through the earlier tutorial, please do so before you continue with this one.

Step 1: Adding the Categories DropDownList and Products GridView

Before concerning ourselves with adding summary information to the GridView's footer, let's first just build the master/detail report. After we've completed this first step, we'll look at how to include summary data.

Start by opening the SummaryDataInFooter.aspx page in the CustomFormatting folder. Add a DropDownList control and set its ID to Categories. Next, click on the Choose Data Source link from the DropDownList's smart tag, and opt to add a new ObjectDataSource named CategoriesDataSource that invokes the GetCategories() method of the CategoriesBLL class.

Click here for larger image

Figure 2. Add a new ObjectDataSource named CategoriesDataSource. (Click on the picture for a larger image.)

Click here for larger image

Figure 3. Have the ObjectDataSource invoke the CategoriesBLL class's GetCategories() method. (Click on the picture for a larger image.)

After configuring the ObjectDataSource, the wizard returns us to the DropDownList's Data Source Configuration wizard, from which we must specify what data-field value should be displayed and which one should correspond to the value for the ListItems of the DropDownList. Have the CategoryName field displayed, and use the CategoryID as the value.

Click here for larger image

Figure 4. Use the CategoryName and CategoryID fields as the text and value, respectively, for the ListItems. (Click on the picture for a larger image.)

At this point, we have a DropDownList (Categories) that lists the categories in the system. We now must add a GridView that lists those products that belong to the selected category. Before we do, however, take a moment to check the Enable AutoPostBack check box in the DropDownList's smart tag. As discussed in the Master/Detail Filtering with a DropDownList tutorial, by setting the AutoPostBack property of the DropDownList to true, the page will be posted back each time that the DropDownList value is changed. This will cause the GridView to be refreshed, showing those products for the newly selected category. If the AutoPostBack property is set to false (the default), changing the category won't cause a postback and, therefore, won't update the listed products.

Click here for larger image

Figure 5. Check the Enable AutoPostBack check box in the DropDownList's smart tag. (Click on the picture for a larger image.)

Add a GridView control to the page, in order to display the products for the selected category. Set the ID of the GridView to ProductsInCategory, and bind it to a new ObjectDataSource named ProductsInCategoryDataSource.

Click here for larger image

Figure 6. Add a new ObjectDataSource named ProductsInCategoryDataSource. (Click on the picture for a larger image.)

Configure the ObjectDataSource, so that it invokes the GetProductsByCategoryID(categoryID) method of the ProductsBLL class.

Click here for larger image

Figure 7. Have the ObjectDataSource invoke the GetProductsByCategoryID(categoryID) method. (Click on the picture for a larger image.)

Because the GetProductsByCategoryID(categoryID) method takes in an input parameter, we can specify the source of the parameter value in the final step of the wizard. In order to display those products from the selected category, have the parameter pulled from the DropDownList named Categories.

Click here for larger image

Figure 8. Get the categoryID parameter value from the selected Categories DropDownList. (Click on the picture for a larger image.)

After completing the wizard, the GridView will have a BoundField for each of the product properties. Let's clean up these BoundFields, so that only the BoundFields named ProductName, UnitPrice, UnitsInStock, and UnitsOnOrder are displayed. Feel free to add any field-level settings to the remaining BoundFields (such as formatting the UnitPrice as a currency). After you make these changes, the GridView's declarative markup should look similar to the following:

<asp:GridView ID="ProductsInCategory" runat="server" 
AutoGenerateColumns="False" DataKeyNames="ProductID" 
DataSourceID="ProductsInCategoryDataSource" EnableViewState="False">

    <Columns>

        <asp:BoundField DataField="ProductName" HeaderText="Product"

          SortExpression="ProductName" />

        <asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}"

            HeaderText="Price"

            HtmlEncode="False" SortExpression="UnitPrice">

            <ItemStyle HorizontalAlign="Right" />

        </asp:BoundField>

        <asp:BoundField DataField="UnitsInStock"

         HeaderText="Units In Stock" SortExpression="UnitsInStock">

            <ItemStyle HorizontalAlign="Right" />

        </asp:BoundField>

        <asp:BoundField DataField="UnitsOnOrder"

           HeaderText="Units On Order" SortExpression="UnitsOnOrder">

            <ItemStyle HorizontalAlign="Right" />

        </asp:BoundField>

    </Columns>

</asp:GridView>

At this point, we have a fully functioning master/detail report that shows the name, unit price, units in stock, and units on order for those products that belong to the selected category.

Click here for larger image

Figure 9. Get the categoryID parameter value from the selected Categories DropDownList. (Click on the picture for a larger image.)

The GridView control can display both a header and footer row. These rows are displayed depending on the values of the ShowHeader and ShowFooter properties, respectively, with ShowHeader defaulting to true and ShowFooter defaulting to false. To include a footer in the GridView, just set its ShowFooter property to true.

Click here for larger image

Figure 10. Set the GridView's ShowFooter property to "true". (Click on the picture for a larger image.)

The footer row has a cell for each of the fields that is defined in the GridView; however, these cells are empty, by default. Take a moment to view our progress in a browser. With the ShowFooter property now set to true, the GridView includes an empty footer row.

Figure 11. The GridView now includes a footer row.

The footer row in Figure 11 doesn't stand out; it has a white background. Let's create a CSS class named FooterStyle in Styles.css that specifies a dark-red background, and then configure the GridView.skin Skin file in the DataWebControls Theme to assign this CSS class to the CssClass property for the FooterStyle of the GridView. If you have to brush up on Skins and Themes, refer back to the Displaying Data with the ObjectDataSource tutorial.

Start by adding the following CSS class to Styles.css, as follows:

.FooterStyle

{

    background-color: #a33;

    color: White;

    text-align: right;

}

The CSS class named FooterStyle is similar in style to the HeaderStyle class, although the HeaderStyle's background color is slightly darker and its text is displayed in a bold font. Furthermore, the text in the footer is right-aligned, whereas the text in the header is centered.

Next, to associate this CSS class with the footer of every GridView, open the GridView.skin file in the DataWebControls Theme and set the CssClass property of the FooterStyle. After this addition, the file's markup should look like the following:

<asp:GridView runat="server" CssClass="DataWebControlStyle">

   <AlternatingRowStyle CssClass="AlternatingRowStyle" />

   <RowStyle CssClass="RowStyle" />

   <HeaderStyle CssClass="HeaderStyle" />

   <FooterStyle CssClass="FooterStyle" />

   <SelectedRowStyle CssClass="SelectedRowStyle" />

</asp:GridView>

As Figure 12 shows, this change makes the footer stand out more clearly.

Figure 12. The GridView's footer row now has a reddish background color.

Step 3: Computing the Summary Data

With the GridView's footer displayed, the next challenge is how to compute the summary data. There are two ways to compute this aggregate information:

  1. Through a SQL query—We could issue an additional query to the database, to compute the summary data for a particular category. SQL includes a number of aggregate functions along with a GROUP BY clause to specify the data over which the data should be summarized. The following SQL query would bring back the needed information:

    SELECT CategoryID, AVG(UnitPrice), SUM(UnitsInStock),
    SUM(UnitsOnOrder)
    FROM Products
    WHERE CategoryID = categoryID
    GROUP BY CategoryID
    

    Of course, you wouldn't want to issue this query directly from the SummaryDataInFooter.aspx page, but instead by creating a method in the ProductsTableAdapter and the ProductsBLL.

  2. Compute this information as it is being added to the GridView—As discussed in Custom Formatting Based upon Data tutorial, the RowDataBound event handler of the GridView fires once for each row that is being added to the GridView after it has been data-bound. By creating an event handler for this event, we can keep a running total of the values that we want to aggregate. After the last data row has been bound to the GridView, we have the totals and the information that we need to compute the average.

Typically, I employ the second approach (it saves a trip to the database and the effort needed to implement the summary functionality in the Data-Access Layer and Business-Logic Layer), but either approach would suffice. For this tutorial, let's use the second option and keep track of the running total by using the RowDataBound event handler.

Create a RowDataBound event handler for the GridView by selecting the GridView in the Designer, clicking the lightning-bolt icon from the Properties window, and double-clicking the RowDataBound event. This will create a new event handler named ProductsInCategory_RowDataBound in the code-behind class of the SummaryDataInFooter.aspx page.

protected void ProductsInCategory_RowDataBound(object sender, 
GridViewRowEventArgs e)

{



}

In order to maintain a running total, we must define variables outside of the scope of the event handler. Create the following four page-level variables:

  • _totalUnitPrice, of type decimal
  • _totalNonNullUnitPriceCount, of type int
  • _totalUnitsInStock, of type int
  • _totalUnitsOnOrder, of type int

Next, write the code to increment these four variables for each data row that is encountered in the RowDataBound event handler.

// Class-scope, running total variables...

decimal _totalUnitPrice = 0m;

int _totalNonNullUnitPriceCount = 0;

int _totalUnitsInStock = 0;

int _totalUnitsOnOrder = 0;



protected void ProductsInCategory_RowDataBound(object sender,

  GridViewRowEventArgs e)

{

    if (e.Row.RowType == DataControlRowType.DataRow)

    {

        // Reference the ProductsRow via the e.Row.DataItem property

        Northwind.ProductsRow product =

          (Northwind.ProductsRow)

          ((System.Data.DataRowView)e.Row.DataItem).Row;



        // Increment the running totals (if they are not NULL!)

        if (!product.IsUnitPriceNull())

        {

            _totalUnitPrice += product.UnitPrice;

            _totalNonNullUnitPriceCount++;

        }



        if (!product.IsUnitsInStockNull())

            _totalUnitsInStock += product.UnitsInStock;



        if (!product.IsUnitsOnOrderNull())

            _totalUnitsOnOrder += product.UnitsOnOrder;

    }

}

The RowDataBound event handler starts by ensuring that we're dealing with a DataRow. After that has been established, the Northwind.ProductsRow instance that was just bound to the GridViewRow object in e.Row is stored in the variable named product. Next, running-total variables are incremented by the current product's corresponding values (assuming that they don't contain a database NULL value). We keep track of both the running UnitPrice total and the number of UnitPrice records that are non-NULL, because the average price is the quotient of these two numbers.

With the summary data totaled, the last step is to display it in the GridView's footer row. This task, too, can be accomplished programmatically through the RowDataBound event handler. Recall that the RowDataBound event handler fires for every row that is bound to the GridView, including the footer row. Therefore, we can augment our event handler to display the data in the footer row by using the following code:

protected void ProductsInCategory_RowDataBound(object sender, 
GridViewRowEventArgs e)

{

    if (e.Row.RowType == DataControlRowType.DataRow)

    {

      ... <I>Increment the running totals</I> ...

    }

    else if (e.Row.RowType == DataControlRowType.Footer)

    {

      ... <I>Display the summary data in the footer </I>...

    }

}

Because the footer row is added to the GridView after all of the data rows have been added, we can be confident that by the time we're ready to display the summary data in the footer, the running-total calculations will have completed. The last step, then, is to set these values in the footer's cells.

To display text in a particular footer cell, use e.Row.Cells[index].Text = value, where the Cells indexing starts at 0. The following code computes the average price (the total price divided by the number of products) and displays it—along with the total number of units in stock and units on order—in the appropriate footer cells of the GridView.

protected void ProductsInCategory_RowDataBound(object sender, 
GridViewRowEventArgs e)

{

    if (e.Row.RowType == DataControlRowType.DataRow)

    {

      ... <I>Increment the running totals</I> ...

    }

    else if (e.Row.RowType == DataControlRowType.Footer)

    {

      // Determine the average UnitPrice

      decimal avgUnitPrice = _totalUnitPrice /

(decimal) _totalNonNullUnitPriceCount;
      // Display the summary data in the appropriate cells

      e.Row.Cells[1].Text = "Avg.: " + avgUnitPrice.ToString("c");

      e.Row.Cells[2].Text = "Total: " + _totalUnitsInStock.ToString();

      e.Row.Cells[3].Text = "Total: " + _totalUnitsOnOrder.ToString();

    }

}

Figure 13 shows the report after this code has been added. Note how the ToString("c") causes the average-price summary information to be formatted as a currency.

Figure 13. The GridView's footer row now has a reddish background color.

Conclusion

Displaying summary data is a common report requirement, and the GridView control makes it easy to include such information in its footer row. The footer row is displayed when the ShowFooter property of the GridView is set to true, and it can have the text in its cells set programmatically through the RowDataBound event handler. Computing the summary data can be done by either re-querying the database or using code in the ASP.NET page's code-behind class to compute the summary data programmatically.

This tutorial concludes our examination of custom formatting with the GridView, DetailsView, and FormView controls. Our next tutorial kicks off our exploration of inserting, updating, and deleting data by using these same controls.

Happy programming!

 

About the author

Scott Mitchell, author of six ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer, and he recently completed his latest book, Sams Teach Yourself ASP.NET 2.0 in 24 Hours. He can be reached at mitchell@4guysfromrolla.com or via his blog, which can be found at http://ScottOnWriting.NET.

© Microsoft Corporation. All rights reserved.