Aggregates and Summary Rows

Aggregates and Summary Rows

To round out our discussion of advanced DataGrid controls, let’s examine complex reports in which you have summary rows with partial totals. The sample application extracts from the Northwind database information about the customers and orders. The grid displays all the orders issued in a given year, sorted by customer. Each row contains the total price of the order, which is already an aggregate value because the order is stored as a collection of items. As shown in Figure 6-10, the summary row is inserted between customers and contains the total number of orders and the total price of all orders.

Figure 6-10
A more complex DataGrid control showing partial totals and summary rows.
The Right Way to Query

Don’t let the tremendous number of methods and events in a DataGrid control fool you. A DataGrid control is really best suited for displaying tabular data. Although it provides many tools for combining the code you use to build the grid, these tools rarely affect the structure and content of the data. Despite this limitation, the DataGrid control does let you significantly change the layout of the cells displayed. What implications does this functionality have for your code?

The DataGrid control cannot add summary rows on the page because it has not been designed to—period. But if you have extra rows in the data source, you can modify the cell layout so that the whole row looks like a summary row. An important guideline to follow when you work with the DataGrid control is to provide the control with pre-processed data that closely resembles the final expected output.

Creating Relations Between Tables

Let’s see how to query for all the orders in the Northwind database, grouped by year and customer. Given a year, the following SQL command selects all the orders issued by customers. Only the sum of all item prices for each order is displayed. Figure 6-11 shows the typical result set when the query generates.

SELECT o.customerid, od.orderid, SUM(od.quantity*od.unitprice) AS price 
    FROM Orders o, [Order Details] od
    WHERE Year(o.orderdate) = @nYear AND od.orderid=o.orderid
    GROUP BY o.customerid, od.orderid
    ORDER BY o.customerid
Figure 6-11
The results of a query displaying the total price of each order.

Notice in Figure 6-11 that the price column contains the total amount for each order. The summary row would add all the orders for a customers and also display the name of the customer. There are two basic ways of doing this, one of which uses the SQL language.

The GROUP BY clause of the SELECT statement in the T-SQL language provides the WITH ROLLUP extension that adds predefined summary rows to the result set. Of course, such a summary row has the layout of all other columns, but the content of each column can be customized to some extent. The following statement illustrates how to modify the previous command to allow for summary rows. Figure 6-12 shows the result set.

    CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE '(Total)' END 
        AS AllCustomersSummary,
    CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END 
        AS IndividualCustomerSummary,
    SUM(od.quantity*od.unitprice) AS price 
    FROM Orders o, [Order Details] od
    WHERE Year(o.orderdate) = 1998 AND od.orderid=o.orderid
    GROUP BY o.customerid, od.orderid WITH ROLLUP
    ORDER BY AllCustomersSummary

GROUPING is the T-SQL aggregate function that works in conjunction with ROLLUP in the body of a GROUP BY clause. The use of GROUPING causes a new column to be added to the result set. This column contains a value of 1 if the row has been added by the ROLLUP operator—therefore, it is a summary row. Otherwise, the column has a value of 0. By using a CASE..WHEN..END statement, you can merge this new column with the grouping column. For example, the T-SQL statement below creates a new column called AllCustomers­Summary which normally contains the value of the CustomerID column and the string “(Total)” in all rows created by grouping on that column.

CASE GROUPING(o.customerid) WHEN 0 
    THEN o.customerid 
    ELSE '(Total)' 
END AS AllCustomersSummary
Figure 6-12
The results of a query displaying the total price of each order.

By using the WITH ROLLUP feature, you get data from the source already in a format suitable for display. In the rest of the chapter, though, I’m going to illustrate an alternate, more flexible, but less lightweight, approach. It exploits some new features of ADO.NET (such as data relations) and lets you use summary rows with any number of columns. Key to this approach is the fact that summary rows are stored in a separate result set.

Based on the SQL Server 2000 Northwind database, the query in the preceding code, run for the year 1998, results in 270 records and 81 customers. The next query shows how to get this second result set, with the item totals for each order listed in the summary row. The results are shown in Figure 6-13.

SELECT c.customerid, 
        TotalOrders=COUNT(DISTINCT od.OrderID)
    FROM Customers c, [Order Details] od, Orders o
    WHERE Year(orderdate) = @nYear 
        AND c.CustomerID = o.CustomerID 
        AND o.OrderID = od.OrderID  
    GROUP BY c.CompanyName, c.CustomerID
    ORDER BY c.customerid
Figure 6-13
The results of the query that provides the data for the summary row.

With the ADO.NET classes, you don’t have to merge these two result sets because they can be more effectively processed in memory during the grid rendering. I grouped the queries in a new stored procedure that takes one input argument—the year.

CREATE PROCEDURE Orders_Summary_By_Customers_And_Year
    (@nYear int)

The data adapter runs the stored procedure and returns two result sets. The former result set is used to populate the data grid; the latter serves to fill up summary rows.

Adding Summary Rows

The DataGrid control does not allow you to add table rows at run time. Even if you could figure out a way to do so, often you would be adding rows to pages that already have the maximum number of rows. These new rows wouldn’t affect the way in which the grid extracts the items for a given page. To prevent any problem with rows and pages, you add extra rows directly to the data source. Ideally, you add an extra blank row between the blocks of records with different customer IDs, but making an insertion requires you to scroll the whole result set. You could more effectively append rows, set the customerid field as appropriate, and then sort. After sorting, each semi-blank row—the summary row—is magically in place.

foreach(DataRow row in dtOrdersSummary.Rows)
    DataRow blank = dtOrders.NewRow();
    blank["CustomerID"] = row["CustomerID"];

    // Application-specific trick. Figure out a reliable
    // way to identify the summary row later while drawing items
    blank["OrderID"] = -1;

The key strategy in the preceding code is inserting information in the summary row that simplifies your detection of it later when you need to display the summary row. During the grid rendering, you hook into the ItemCreated event and check the contents of the row being drawn. If the row is the summary row, the graphical layout changes to display information from the OrdersSummary table. In our example, I decided to mark the summary row with a -1 value in the OrderID field. (This decision is application-specific.) Figure 6-14 shows how the grid looks at this point.

Figure 6-14
The grid with summary totals. An OrderID field of -1 identifies a summary row.
Populating the Summary Row

The layout (font, background color, and number of cells) and contents of the summary row must be modified. Any graphical update can be done in the ItemCreated event. For example, you can group the first two cells and render the whole row with a white background and bold fonts.

DataRowView drv = (DataRowView) e.Item.DataItem;
if ((int) drv["OrderID"] == -1)
    e.Item.BackColor = Color.White;  
    e.Item.Cells[2].Font.Bold = true;
    e.Item.Cells[2].HorizontalAlign = HorizontalAlign.Right;
    e.Item.Cells.RemoveAt(1);    // the order# column
    e.Item.Cells[0].ColumnSpan = 2;

The way you bind the cells with summary text depends on the type of the grid columns involved. If they are templated columns, you simply set the Text property of the desired cell with the desired text. For other column types, including bound columns, the binding approach is a little more complicated. In the case of a bound column, the association between the cell text and the data is handled in the ItemDataBound event. The ItemDataBound event fires after ItemCommand. As a result, any text you set in ItemCreated is soon overwritten by ItemDataBound. To work around this, hook into the ItemDataBound event.

Retrieving Summary Data

When the ItemDataBound event is processed, the grid is working on a row taken from the Orders in-memory table. When the row appears as a summary row, you need to replace this information with data coming from the Orders­Summary table. How do you get the corresponding summary row? Create a data relation between the two tables, which makes this retrieval quite straightforward.

DataColumn dc1 = dtOrders.Columns["CustomerID"];
DataColumn dc2 = dtOrdersSummary.Columns["CustomerID"];
DataRelation dr = new DataRelation("OrdersAndSummary", dc1, dc2);
ds.EnforceConstraints = false;

A DataRelation object creates a relationship between two tables that have a common column. Both tables must be part of the same DataSet object. After the relation is set, you get from each row of the parent table the array of child rows by calling the GetChildRows method.

You set the relation between the two tables upon loading. During the ItemDataBound event, you obtain the corresponding summary row with the following, surprisingly simple, code:

DataRowView drv = (DataRowView) e.Item.DataItem;
DataRow[] a = drv.Row.GetChildRows("OrdersAndSummary");
DataRow drSummaryRow = a[0];    // Only one row selected by design
// Build the string to display
e.Item.Cells[1].Text = strTextToDisplay;

The final result of this code is shown in Figure 6-15. The full source code for the SuperGrid.cs, Summary.aspx, and Summary.cs applications is available on the companion CD. Note that the sample application also combines the summary rows with the select-by-condition feature I examined earlier in the chapter.

Figure 6-15
The SuperGrid control used to display summary rows and partial totals.