Displaying Summary Information (sum or total) in the GridView's Footer

Challenge

"I have a GridView and I need to show the aggregate sum of a column in the footer row"

Introduction

In this article I will go through steps to enable aggregation sum (total) of a column in the asp.net GridView Footer.

Step1: Prepare Database Connection

In this article I used the AdventureWorks Database and I will query table "PurchaseOrderDetail".

In the web.config set the connection string as following:

<connectionStrings>

    <add name="dbconnection" connectionString="Data Source=(local);Integrated Security=true;Initial Catalog=AdventureWorks"/>

</connectionStrings>

Step2: Prepare the aspx Page


<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Displaying Summary Information in the GridView's Footer</title>

<style type="text/css">

.Gridview

{

font-family:Verdana;

font-size:10pt;

font-weight:normal;

color:black;

}

</style>

</head>

<body>

<form id="form1"runat="server">

<div>

<asp:GridView ID="gvPurchaseOrderDetail" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"

HeaderStyle-ForeColor="White" HeaderStyle-Font-Bold="true" DataKeyNames="ProductID"

runat="server" ShowFooter="true" AllowPaging="true" PageSize="5" AutoGenerateColumns="false"

DataSourceID="sqldsPurchaseOrderDetail" OnRowDataBound="gvPurchaseOrderDetail_RowDataBound">

<FooterStyle Font-Bold="true" BackColor="#61A6F8" ForeColor="black" />

<Columns>

  <asp:BoundField DataField="ProductID" HeaderText="Product ID" />

  <asp:BoundField DataField="PurchaseOrderDetailID" HeaderText="Purchase Order ID" />

  <asp:TemplateField HeaderText="Order Quantity">

  <ItemTemplate>

   <asp:Label ID="lblOrderQty" runat="server" Text='<%#Eval("OrderQty") %>' />

  </ItemTemplate>

  <FooterTemplate>

   <asp:Label ID="lbltxtTotal" runat="server" Text="Total Price" />

  </FooterTemplate>

  </asp:TemplateField>

  <asp:TemplateField HeaderText="Price">

  <ItemTemplate>

   <asp:Label ID="lblPrice" runat="server" />

  </ItemTemplate>

  <FooterTemplate>

   <asp:Label ID="lblTotal" runat="server" />

  </FooterTemplate>

  </asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="sqldsPurchaseOrderDetail" runat="server" SelectCommand="select * from [AdventureWorks].[Purchasing].[PurchaseOrderDetail]" ConnectionString="<%$ ConnectionStrings:dbconnection %>"></asp:SqlDataSource>

</div>

</form>

</body>

</html>

Step3: Prepare Code Behind

decimal total = 0;

protected void gvPurchaseOrderDetail_RowDataBound(object sender, GridViewRowEventArgs e)

{

 if (e.Row.RowType == DataControlRowType.DataRow)

 {

Label lblPrice = (Label)e.Row.FindControl("lblPrice");

decimal price = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "UnitPrice")) * Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "OrderQty"));

lblPrice.Text = price.ToString();

total += price;

 }

 if (e.Row.RowType == DataControlRowType.Footer)

 {

Label lblTotal = (Label)e.Row.FindControl("lblTotal");

lblTotal.Text = total.ToString();

 }

}

Result:

Total in GridView


Published Friday, July 20, 2012 9:52 AM by jhallal
Filed under:

Comments

# re: Displaying Summary Information (sum or total) in the GridView's Footer

Tuesday, September 25, 2012 2:17 AM by Rule the world

This code is not working for me.I have given user input into the grid view. may be that is why its not working.

# re: Displaying Summary Information (sum or total) in the GridView's Footer

Thursday, October 4, 2012 4:41 PM by Jonathan ZZ

Thank you.  Solved my problem.

# re: Displaying Summary Information (sum or total) in the GridView's Footer

Sunday, November 4, 2012 1:50 AM by Junaid

using System.Linq;

then use the code like this

dt.AsEnumerable().Select(x => x.Field<decimal>("UnitPrice")).Sum().ToString();

# re: Displaying Summary Information (sum or total) in the GridView's Footer

Wednesday, November 7, 2012 5:09 PM by Natty

Quite brilliant I would like to compliment you sir.  I am always looking for a challenge.  This took me a bit longer than usual but I did finally figure it out.  Thank you for the brain exercise.  Your blog is worth visiting.

# re: Displaying Summary Information (sum or total) in the GridView's Footer

Tuesday, September 10, 2013 7:02 AM by Tatan

It works!! Thanks!!

# re: Displaying Summary Information (sum or total) in the GridView's Footer

Wednesday, October 2, 2013 9:10 AM by sithembiso

i tried this code its working but the this is that my grdiview allows me to edit and delete the row, but after i added this code i cant update/delete the row, it shows the erroe saying OBJECT REFERENCE NOT SET TO AN INSTANCE OF AN OBJECT

PLEASE HELP ME

# re: Displaying Summary Information (sum or total) in the GridView's Footer

Friday, October 4, 2013 3:05 AM by mukesh

i found the code for ,,, calculation row on row creation in run time....

Leave a Comment

(required) 
(required) 
(optional)
(required)