A summary row in a DataGrid is a row that has column totals, averages, or some other type of summary information. A summary row is indispensible for a DataGrid with many numbers. I have used three different summary row techniques with DataGrids and have found each technique has its advantages (and, of course, its disadvantages). In this article, I give examples of each of the techniques and discuss how one technique may be preferable to another in a given situation. In this article, I call these three techniques Rollup, DataTable Loop, and ItemDataBound.
Rollup (Sample | ASPX Code | CS Code | SQL)
ROLLUP is an argument of the GROUP BY clause of a SQL statement. According to the SQL Server Books Online, Rollup "specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set." The big advantage of using ROLLUP is that the data returned from the database already look almost like what you want displayed on the page. This requires less manipulation with the data, making your code cleaner, simpler, and faster.
Another advantage of ROLLUP is that it produces not only summary rows but sub-summary rows. In the example, scores are not only averaged in the final row, but each year is also averaged. As you can see from the code, very little code is needed to display the data: simply some code in the ItemDataBound event for improving how the data is displayed, but nothing that actually changes the data. Subtotals can be done using other techniques, but they typically take considerably more work.
In his sample chapter "Chapter 6: Advanced Data Reporting" from the book Building Web Solutions with ASP.NET and ADO.NET Dino Esposito gives another example of using ROLLUP for displaying summary row information.
DataTable Loop (Sample | ASPX Code | CS Code)
A DataTable loop is where you loop through the DataRows in a DataTable accummulating values for the summary row. After looping, a row is added to the DataTable to hold the summary information. This makes the last row in the DataTable the summary row. An advantage this technique has over ROLLUP is that ROLLUP is only possible if you are using certain database software, such as SQL Server 6.5 (and above) or Oracle 8i (and above). If you are getting data from an XML file, ROLLUP is not possible. In contrast, a DataTable loop can work with any data source.
Another advantage the DataTable technique has over the ItemDataBound technique is that the summary information can be available before the data binding occurs in the DataGrid. This is useful when you need to display summary information at the top of a page. Furthermore, unlike the ItemDataBound technique, the DataTable technique does not need to use global variables for the summary information.
ItemDataBound (Sample | ASPX Code | CS Code)
This technique seems to be a popular way of accumulating summary information in a DataGrid. Dotnetjunkies guru Doug Seven uses this technique in his tutorial Adding a Totals Field in a DataGrid, and Microsoft also uses this technique in their Knowledge Base How To article HOW TO: Create a Summary Row for a DataGrid in ASP.NET by Using Visual Basic .NET
An advantage the ItemDataBound technique has over the other two techniques is its use with DataGrid sorting. Since the summary information is not present before databinding, it is easier to implement DataGrid sorting. Unlike the other two techniques, no special handling needs to be done when sorting. Like the DataTable Loop technique, this technique can be used with any data source.
SummaryRow Class (CS Code)
To reduce code duplication, I used a helper class for holding the summary information and having methods that calculate a sum and an average. This class could easily be extended to include other summary information, such as: Min, Max, and Standard Deviation.
||Advantages: Fast, Simple, Sub-Totals, Fewer lines of code
||Disadvantages: Cannot be used with XML data and certain database software, DataGrid sorting is more complicated|
||Advantages: Total available before data binding, Can use XML as data source, Can have running total
||Disadvantages: Subtotals more difficult than ROLLUP, Extra work of looping through DataTable|
||Advantages: Can use XML as data source, Can have running total, DataGrid sorting simpler than other techniques
||Disadvantages: Subtotals are more difficult than ROLLUP, global variables are required|
|Sub Total Rows
|Summary before binding
|Any Data Source