Displaying Multiple Query Totals in an ASP.NET Paged GridView


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 &gt; 0 ? e.Row.Cells[0] : null;
		if (yaCell != null)
		{
			yaCell.Text = "
<div>Page Average:</div>
";
			yaCell.Text += "
<div>Page Totals:</div>
";
			yaCell.Text += "
<div>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 &lt; e.Row.Cells.Count; cidx++)
		{
			// create a nice border underneath the last &quot;data row&quot;
			if (lastGridRow != null)
				lastGridRow.Cells[cidx].Style.Add(&quot;border-bottom&quot;, &quot;solid 1px #9F9F9F&quot;);

			// 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(&quot;AVG([{0}])&quot;, dc.ColumnName), &quot;&quot;).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(&quot;SUM([{0}])&quot;, dc.ColumnName), &quot;&quot;).ToString(), out pt))
				pt = 0;

			// the query total for this column comes from the &quot;extra&quot; result-set
			int qt = 0;
			if (!Int32.TryParse(_MDQ.Totals.Rows[0][string.Format(&quot;T_{0}&quot;, dc.ColumnName)].ToString(), out qt))
				qt = 0;

			// create the div&#039;s and set the cell text
			var sb = new StringBuilder(string.Empty);
			sb.AppendFormat(&quot;
<div class='pagetotals' style='text-align:right;'>{0}</div>
",
					pa &gt; 0 ? pa.ToString("#,###,###") : "&nbsp;");

			sb.AppendFormat("
<div class='pagetotals' style='text-align:right;'>{0}</div>
",
					pt &gt; 0 ? pt.ToString("#,###,###") : "&nbsp;");

			sb.AppendFormat("
<div class='querytotals' style='text-align:right;'>{0}</div>
",
					qt &gt; 0 ? qt.ToString("#,###,###") : "&nbsp;");

			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.

gridviewtotals01
GridView Totals

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

2 thoughts on “Displaying Multiple Query Totals in an ASP.NET Paged GridView

  1. Nice explanation, but your code example doesn’t actually show multiple divs in the footers. The idea comes across though.

  2. Hi Doug, yeah you’re right the code sample shows multiple divs, but the picture doesn’t show the multiple query total rows.

    I’ll update that since thats what the article is all about after all 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s