How To Show Total In GridView Footer

If you build any kind of report, very often requirement is to display result of some aggregate function bellow of numeric column. For example, that could be an average earning per customer, total call time, summary of time spent on project, highest or lowest value in column, number of products in stock, total price, row count etc. This information acts as some kind of conclusion for certain column and helps user to better understand report.

GridView control can be used to show this kind of report, where sum total of column (or other aggregate function) technically can be placed anywhere on the screen, but it is usually displayed bellow calculated column, in the GridView's footer area.

Example of showing total in GridView's footer

In this example, I will use popular Northwind database. It is not required, if you don't have this database already installed, you can use any other database or even create new one with one table and few rows. Only thing important is that you have a table with one numeric column. In this example, numeric column "ProductSales" is type of float and contains sales amounts for several products. Web form output will look like this:

Drag one GridView and one SqlDataSource control from toolbox to the web form. SqlDataSource control defines data source and GridView will show the data. Here is the markup code of GridView and SqlDataSource control:

<asp:GridView ID="GridView1" runat="server" DataSourceID="sdsNorthwind"
  AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333"
  GridLines="None" ShowFooter="True" onrowdatabound="GridView1_RowDataBound">
  
  <%--This example consists of two columns, second column is numeric and formatted as currency--%>
  <Columns>
  <asp:BoundField DataField="ProductName" HeaderText="Product Name" FooterText="Total:" />
  <asp:BoundField DataField="ProductSales" HeaderText="Product Sales"
  DataFormatString="{0:c}" />
  </Columns>
  
  <%--Set styles to get better GridView appearance--%>
  <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
  <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
  </asp:GridView>
  
  <%--SqlDataSource control opens [Sales by Category] view of Northwind database--%>
  <asp:SqlDataSource ID="sdsNorthwind" runat="server"
  ConnectionString="<%$ ConnectionStrings:NorthwindConnStr %>"
  SelectCommand="SELECT [ProductName] ,[ProductSales]
   FROM [NORTHWIND].[dbo].[Sales by Category] WHERE CategoryID = 1"></asp:SqlDataSource>

Important thing is to set ShowFooter property to True because by default Footer element is not displayed. This markup code will load data from database and show records in GridView, but Total value is still not displayed. Standard GridView hasn't some property to calculate aggregate functions automatically and we can't finish this task using only markup. To show total, we need to calculate it using custom ASP.NET server side code.

In this approach, we'll use GridView RowDataBound event. Code would look like this:

[ C# ]

using System;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  
  public partial class GridView_Total : System.Web.UI.Page
  {
    // Declare variable used to store value of Total
    private decimal TotalSales = (decimal)0.0;
  
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
      // check row type
      if (e.Row.RowType == DataControlRowType.DataRow)
        // if row type is DataRow, add ProductSales value to TotalSales
        TotalSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "ProductSales"));
      else if (e.Row.RowType == DataControlRowType.Footer)
        // If row type is footer, show calculated total value
        // Since this example uses sales in dollars, I formatted output as currency
        e.Row.Cells[1].Text = String.Format("{0:c}", TotalSales);
    }
  }

[ VB.NET ]

Partial Class GridView_Total_VB
  Inherits System.Web.UI.Page
  
    ' Declare variable used to store value of Total
    Private TotalSales As Decimal = 0.0
  
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
      ' check row type
      If e.Row.RowType = DataControlRowType.DataRow Then
        ' if row type is DataRow, add ProductSales value to TotalSales
        TotalSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "ProductSales"))
      ElseIf e.Row.RowType = DataControlRowType.Footer Then
        ' If row type is footer, show calculated total value
        ' Since this example uses sales in dollars, I formatted output as currency
        e.Row.Cells(1).Text = String.Format("{0:c}", TotalSales)
      End If
    End Sub
  End Class

Showing total in GridView footer using helper function

In previous method, RowDataBound event is called every time new row is created. If report has a lot of rows, like hundreds of rows, then this approach could cause performance issues since GridView1_RowDataBound method is executed for every row and finally one more time for footer. This could slow down web application, especially if you have high traffic and/or some complicated statistic calculation in footer instead of simple total.

As an alternative, you can create separate function that returns footer data only. ASP.NET server side code now would be:

[ C# ]

using System;
using System.Data.SqlClient;
using System.Configuration;
  
public partial class GridView_Total : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    getSUM();
  }
  
  protected void getSUM()
  {
    // SQL query that gets total of product sales where category id = 1
    string SqlQuery = @"SELECT SUM(ProductSales) AS TotalSales
      FROM [NORTHWIND].[dbo].[Sales by Category]
      WHERE CategoryID = 1";
  
    // Declare and open a connection to database
    SqlConnection conn = new SqlConnection(
    ConfigurationManager.ConnectionStrings["NorthwindConnStr"].ConnectionString);
    conn.Open();
  
    // Creates SqlCommand object
    SqlCommand comm = new SqlCommand(SqlQuery, conn);
  
    // Gets total sales
    decimal TotalSales = Convert.ToDecimal(comm.ExecuteScalar());
  
    // Close connection
    conn.Close();
    conn.Dispose();
    comm.Dispose();

    // Adds formatted output to GridView footer
    GridView1.Columns[1].FooterText = String.Format("{0:c}", TotalSales);
  }
}  

[ VB.NET ] 

Imports System.Data.SqlClient
Imports System.Configuration
  
Partial Class GridView_Total_VB
      Inherits System.Web.UI.Page
  
  
  Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    getSUM()
 End Sub
  
  Protected Sub getSUM()
    ' SQL query that gets total of product sales where category id = 1
    Dim SqlQuery As String = "SELECT SUM(ProductSales) AS TotalSales " & _
      "FROM [NORTHWIND].[dbo].[Sales by Category] " & _
      "WHERE CategoryID = 1"
  
    ' Declare and open a connection to database
    Dim conn As SqlConnection = New SqlConnection( _
    ConfigurationManager.ConnectionStrings("NorthwindConnStr").ConnectionString)
    conn.Open()
  
    ' Creates SqlCommand object
    Dim comm As SqlCommand = New SqlCommand(SqlQuery, conn)
  
    ' Gets total sales
    Dim TotalSales As Decimal = Convert.ToDecimal(comm.ExecuteScalar())
  
    ' Close connection
    conn.Close()
    conn.Dispose()
    comm.Dispose()  

    ' Adds formatted output to GridView footer
    GridView1.Columns(1).FooterText = String.Format("{0:c}", TotalSales)
    
    End Sub
End Class

Markup code is almost the same as in previous example. Only difference is that we don't use RowDataBound event, so remove onrowdatabound="GridView1_RowDataBound" part inside GridView tags.

You can use this method in Template columns too (defined with <asp:TemplateField></asp:TemplateField> tag). In case of templated column, you have an additional option to use footer template. Sometimes, on this way you can achieve better look or more customized layout or report. Code would be very similar, just instead of void (or Sub in VB.NET) procedure should return calculated value as string. So, last line:

GridView1.Columns(1).FooterText = String.Format("{0:c}", TotalSales)

should be:

Return String.Format("{0:c}", TotalSales)

In markup code, we'll define footer template and call getSUM method:

<FooterTemplate>
Total sales:   <%# getSUM(); %>
</FooterTemplate>

Conclusion

If you use paging on GridView, notice that first solution with RowDataBound event will calculate only visible rows (rows on currently selected page). If this is a problem, then is probably better to switch to solution with helper function. As another option, you can set GridView.DataSource property to DataSet or DataTable object, instead of using SqlDataSource control. Then, you can calculate data using DataTable.Compute method to get needed value.

Don't forget to set ShowFooter property to true because it is false by default. Happy coding!

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |