This post is about displaying 1 or more “totals” rows within the footer row of an ASP.NET GridView control, although by “totals” you could easily substitute some other mathmatical function. This topic has been done to death, although in this post I will demonstrate a technique I used to display query totals in a GridView backed by paged data. Paged access to data via a GridView has also been done to death, and while the standard techniques we’re all used to are easy, they’re almost too easy, and don’t work well when your data sets contain hundreds of thousands of rows.
No, paged access to data really means, only returning pages of say 10 of the rows your interested in, out of the possible thousands or more that actually satisfy the query, and using the UI controls to allow the user to page through those results.
So, “real” paged access to data is easy enough, especially now since SQL Server 2005 has native support for this using the SELECT ROW_NUMBER() OVER (ORDER BY ...) statement. But now there is a problem to solve, how do you display “query” totals if you’re only returning a single page of data? Well, my solution involved the back end returning an extra result-set containing a single row of the same schema as the page data result-set, and this single row contains the query totals. The column names of the totals result-set were the same as the page data result-set, except that they were prefixed with the “T_” characters. So now in the UI, I’ve got a page of data and the query totals which I can display in the footer of the GridView.
To display footer data, ensure that the ShowFooter property of the GridView is set to true, then create an event handler for the RowDataBound event of the GridView:
... gvGridView.RowDataBound += OnGridView_RowDataBound; ...
My technique for displaying multiple rows within the footer was simple, in each table cell of the footer I set the text of the cell to 1 or more <div>'s where the <div>'s themselves contain the totals values. Since <div>'s are block elements (unless you change that behaviour) they appear as rows within the GridView footer cell. Since nothing speaks better than code, here’s the code:
private void OnGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
// create a nice border underneath the last "data row"
GridViewRow lastGridRow = gvGridView.Rows[gvGridView.Rows.Count - 1];
if (lastGridRow != null)
lastGridRow.Cells[0].Style.Add("border-bottom", "solid 1px #9F9F9F");
// display total labels underneath the columns which are not "totalled" i.e. Column 0
var yaCell = e.Row.Cells.Count > 0 ? e.Row.Cells[0] : null;
if (yaCell != null)
{
yaCell.Text = "
<div class=\"pagetotals\">Page Average:</div>
";
yaCell.Text += "
<div class=\"pagetotals\">Page Totals:</div>
";
yaCell.Text += "
<div class=\"querytotals\">Query Totals:</div>
";
yaCell.Style.Remove("padding");
yaCell.Style.Add("padding", "2px 2px 2px 2px");
}
// these are the columns that are "totalled", these start from Column 1
for (var cidx = 1; cidx < e.Row.Cells.Count; cidx++)
{
// create a nice border underneath the last "data row"
if (lastGridRow != null)
lastGridRow.Cells[cidx].Style.Add("border-bottom", "solid 1px #9F9F9F");
// current column
var dc = _MDQ.Results.Columns[cidx];
// calculate the page average value for this column, note the use of the Compute() function
int pa = 0;
if (!Int32.TryParse(_MDQ.Results.Compute(string.Format("AVG([{0}])", dc.ColumnName), "").ToString(), out pa))
pa = 0;
// calculate the page total value for this column, note the use of the Compute() function
int pt = 0;
if (!Int32.TryParse(_MDQ.Results.Compute(string.Format("SUM([{0}])", dc.ColumnName), "").ToString(), out pt))
pt = 0;
// the query total for this column comes from the "extra" result-set
int qt = 0;
if (!Int32.TryParse(_MDQ.Totals.Rows[0][string.Format("T_{0}", dc.ColumnName)].ToString(), out qt))
qt = 0;
// create the div's and set the cell text
var sb = new StringBuilder(string.Empty);
sb.AppendFormat("
<div class='pagetotals' style='text-align:right;'>{0}</div>
",
pa > 0 ? pa.ToString("#,###,###") : " ");
sb.AppendFormat("
<div class='pagetotals' style='text-align:right;'>{0}</div>
",
pt > 0 ? pt.ToString("#,###,###") : " ");
sb.AppendFormat("
<div class='querytotals' style='text-align:right;'>{0}</div>
",
qt > 0 ? qt.ToString("#,###,###") : " ");
e.Row.Cells[cidx].Text = sb.ToString();
// add some styling
e.Row.Cells[cidx].Style.Remove("padding");
e.Row.Cells[cidx].Style.Add("padding", "2px 2px 2px 2px");
}
}
}
Notice that for the Page average and Page total values I’m using the Compute() function of the DataTable object, which is quite convenient. Also, styling of the footer rows and the <div>'s via CSS is particularly important as this completes the “Totals” look.
GridView Totals
RSS - Posts
My 