Gridview Totals


This example will be on calculating totals on gridviews in asp.net.

Languages and tools used:

  • ASP.NET
  • C#
  • Visual Studio

To start with you will need a gridview. You will need to note the ‘DataField’ for each column because you will be referencing these when you go in to calculate a running total. Also, make sure you have the ‘OnRowDataBound’ and ‘ShowFooter’ attributes set as well.

<asp:GridView ID="GridView6" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="leadsbysourcedaily" EnableModelValidation="True" OnRowDataBound="bind" ShowFooter="True">
   <Columns>
	   <asp:BoundField DataField="Campaign" HeaderText="Campaign" SortExpression="Campaign" />
	   <asp:BoundField DataField="Purchase" HeaderText="Purchase" SortExpression="Purchase" ItemStyle-CssClass="center" />
	   <asp:BoundField DataField="Refinance" HeaderText="Refinance" SortExpression="Refinance" ItemStyle-CssClass="center" />
	   <asp:BoundField DataField="Other" HeaderText="Other" SortExpression="Other" ItemStyle-CssClass="center" />
   </Columns>
   <FooterStyle CssClass="footertotal" />
   <AlternatingRowStyle CssClass="altrow" />
</asp:GridView>
<asp:SqlDataSource ID="leadsbysourcedaily" runat="server" ConnectionString="<%$ ConnectionStrings:yourConnString %>" SelectCommand="yourQuery"></asp:SqlDataSource>

Now that we have a gridview we will need to go into the codebehind page and write our code to calculate and display the totals.

// Declare our totals here so they don't get set to 0 on each bind
int totalPurchase = 0;
int totalRefinance = 0;
int totalOther = 0;
int grandTotal = 0;

protected void bind(object sender, GridViewRowEventArgs e)
{
    // Time to put in totals!
    // First check to see if the row is a data row
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // Add each cell to the running totals
	    // Check for null in each cell, otherwise we will get errors
        if (DataBinder.Eval(e.Row.DataItem, "Purchase") != DBNull.Value)
            totalPurchase += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Purchase"));
        else
            totalPurchase += 0;
        if (DataBinder.Eval(e.Row.DataItem, "Refinance") != DBNull.Value)
            totalRefinance += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Refinance"));
        else
            totalRefinance += 0;
        if (DataBinder.Eval(e.Row.DataItem, "Other") != DBNull.Value)
            totalOther += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Other"));
        else
            totalOther += 0;
    }
	// Now check if the row is the footer
    else if (e.Row.RowType == DataControlRowType.Footer)
    {
        // Calc grandTotal
        grandTotal = totalPurchase + totalRefinance + totalOther;

        // Display the totals in the appropriate cells
        e.Row.Cells[0].Text = "Total (" + grandTotal + ")";
        e.Row.Cells[1].Text = totalPurchase.ToString();
        e.Row.Cells[2].Text = totalRefinance.ToString();
        e.Row.Cells[3].Text = totalOther.ToString();

        // Clear vars for other grids that bind
        totalPurchase = 0;
        totalRefinance = 0;
        totalOther = 0;
        grandTotal = 0;
    }
}

The really nice thing is that if you have multiple grids that use the same column names you can have them all reference this code!

Next weeks example will either be an intro to making charts or conditional data formatting in gridviews. See you then!