CodeSnip: How to Display Sum Total in the Footer of the GridView Control
page 1 of 1
Published: 06 Mar 2006
Unedited - Community Contributed
Abstract
In this article, Sushila demonstrates how to display the sum total of a column in the footer of the GridView control.
by Sushila Bowalekar Patel
Feedback
Average Rating: 
Views (Total / Last 10 Days): 224577/ 372

 

Introduction

In this article, we will see how to display the sum total of a column in the footer of the GridView control using Visual Studio 2005.

For the purpose of this article, we will use the Products Table from the Northwind Database and the GridView control for data binding. The GridView control will display the data and SqlDataSource is supplied in the web.config file as shown in Listing 1.

Listing 1

<connectionStrings>
<add name="DummyDB"
  connectionString="Server=localhost;Integrated  Security=True;Database=NorthWind;Persist,
  Security Info=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Our goal is to display the total of the column UnitPrice in the footer as shown in Figure 1.

Figure 1

By default, the GridView's Showfooter property is set to false. We'll change it to true. As we are calculating the field UnitPrice, we'll use TemplateField's ItemTemplate to display UnitPrice and FooterTemplate to display the total.

Listing 2: Display Sum Total in Footer of GridView Control

<asp:GridView ID="GridView1"
  ShowFooter="true" DataKeyNames="ProductId"
  AutoGenerateColumns="false" runat="server"
  DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="Productid" HeaderText="Product Id" />
<asp:BoundField DataField="ProductName" FooterText="Total" HeaderText="Product Name" />
<asp:TemplateField HeaderText="Unit Price" FooterStyle-Font-Bold="True">
<ItemTemplate>
  <%# GetUnitPrice(decimal.Parse(Eval("UnitPrice").ToString())).ToString("N2") %>
</ItemTemplate>
<FooterTemplate>
  <%# GetTotal().ToString("N2") %>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:DummyDB %>"
SelectCommand="Select * from Products">
</asp:SqlDataSource>

Finally, we'll use Helper functions named GetUnitPrice and GetTotal to display the UnitPrice in the ItemTemplate and Total in the FooterTemplate. For instance, for each row of the GridView, a price value is passed to the GetUnitPrice function returning variable Price.

Listing 3: Using Helper Functions

[Visual Basic .NET]

Dim TotalUnitPrice As Decimal = 0.0
Function GetUnitPrice(ByVal Price As DecimalAs Decimal
  TotalUnitPrice += Price
  Return Price
End Function
Function GetTotal() As Decimal
  Return TotalUnitPrice
End Function

C#

decimal TotalUnitPrice;
decimal GetUnitPrice(decimal Price) 
{  
  TotalUnitPrice += Price;
  return Price;
}
decimal GetTotal()
{
  return TotalUnitPrice;
}

Downloads

[Download Sample]

Conclusion

In this article, you have learned how to display the sum total of a column in the footer of the GridView control with the help of an example.



User Comments

Title: What to do if we delete a row from a datagrid?   
Name: Gokul
Date: 5/30/2007 6:55:48 AM
Comment:
This code is nice. but What to do if we delete a row from a datagrid?
Title: When I delete the row how do i decreament the total   
Name: Arun
Date: 5/21/2007 3:34:24 AM
Comment:
This code is really good. it is working superb. I want the total to be decrement when i delete the row. Please give the solution.
Title: Superb   
Name: Waseem Arfi
Date: 5/11/2007 3:01:40 AM
Comment:
Very nice and compact example on showing how to sum any column. It has saved a lot of time of mine.

Thanks a lot. May GOD bless you.

Regards,
Warfi
Title: happy coding!   
Name: Miss Qureshi
Date: 4/4/2007 7:36:08 AM
Comment:
Yes its Nice code!!
But i want to footer that is show only 1.

-----------------------------------------
Book1 | ASP | ASP Best Book | 500
Book2 | ASP2.0 | ASP Best Book | 500
Book3 | Java | ASP Best Book | 1000
------------------------------------------
Total = 20000
-----------------------------------------
Title: hi   
Name: James
Date: 4/1/2007 6:42:05 AM
Comment:
call the totalprice directly from the data access layer, don't use eval() method
Title: Integrated with DLINQ   
Name: Tony
Date: 3/27/2007 8:03:35 PM
Comment:
I am just considering to use DLINQ to implement the object data source. I return the TotalPrice which is calculated in my object data access layer, but I can not display the TotalPrice in the Footer.
I did Eval("TotalPrice")
Does not work
Title: Good One   
Name: Rekha
Date: 2/12/2007 11:24:57 AM
Comment:
Thank you.
Title: Great Example   
Name: FwI
Date: 2/8/2007 7:20:24 AM
Comment:
Needed to find out how to format data in a column i a GridView. The ItemTemplate-part show me the way...
Title: MultiColums extension   
Name: Stefano Pagana
Date: 1/18/2007 10:14:02 AM
Comment:
decimal[] TotalValue = new decimal[5];
public decimal GetUnitValue(decimal Value, int ArrayPos)
{
TotalValue[ArrayPos] += Value;
return Value;
}
public decimal GetTotal(int ArrayPos)
{
return TotalValue[ArrayPos];
}
Title: Good one!   
Name: sherin tharian
Date: 1/16/2007 6:06:50 AM
Comment:
Good to see that things have changed since http://aspalliance.com/494.
Title: it worked   
Name: Tobs
Date: 1/15/2007 5:16:45 PM
Comment:
Thanks for the download, with this help I managed to translate it to my application and it worked!
Will the sum function also show {0:c} formats? Couldn't find to enable it.

anyway great work

tobs
Title: how if i have a quantity column ?   
Name: mickey
Date: 1/9/2007 3:47:41 AM
Comment:
how if my gridview have a quantity column? how can i get the total for individual item first and finally get a total all items ?

thanks
Title: Working with cells that are NULL   
Name: CK
Date: 12/29/2006 11:21:22 AM
Comment:
I've implemented this solution in my VB.NET code and it works quite well unless a record's Price column is NULL. Anyone have a suggestion for handling NULLs with these functions? -Thanks.
Title: great article   
Name: Jake
Date: 12/20/2006 9:38:06 PM
Comment:
That's the easiest implementation of gridview summary columns that I've seen. Thanks for sharing it!
Title: Total with paging   
Name: Marco Zirino
Date: 12/20/2006 6:38:23 PM
Comment:
If you want to do a total with paging, assuming youre using default paging here is a way:

Private Function SetTotals() As String
' Total all the rows in the grid view
Dim dv As DataView = _
CType(ProductsDS.Select(DataSourceSelectArguments.Empty), DataView)

Dim TotalPrice As Decimal
Dim TotalCount As Integer
For Each dr As DataRow In dv.Table.Rows
TotalPrice += dr.Item("price")
TotalCount += 1
Next

SetTotals = "Total Count: " & TotalCount & " Total Price: $" & Format(TotalPrice, "0.00")

End Function

Don't know if it's the best, but it works
Title: Excellent!!! But How to get the total if Paging is enabled   
Name: Radhika
Date: 11/30/2006 12:31:09 PM
Comment:
Plz help me to get the sum total of a column in a gridview where there is paging enabled!!! Thanks in advance...
Title: sum total in gridview   
Name: jambhukumar
Date: 11/21/2006 4:21:39 AM
Comment:
Thanks a lot for putting helping hands
Title: what if you have 20 columns   
Name: aron
Date: 11/7/2006 3:51:41 PM
Comment:
what if you want the sum's of 20+ columns?
Title: MMMh...   
Name: Ryoushin
Date: 10/12/2006 5:13:48 AM
Comment:
How about paged results?...
Title: SOLVED - Retain sum value while editing a row   
Name: Jon Medel
Date: 9/22/2006 12:55:23 AM
Comment:
I finally figured it out! I couldn't find the answer on the web so I thought I'd share my answer here since this is where my search always ended up looking for gridviews footers and sums on google.

First, remove <%# GetTotal().ToString("N2") %> from the footer template.

Second, you no longer need the GetTotal function, you can dump that too.

Third, you will need to identify which control in which cell in the selected row contains the data you want. For me, it was the 6th cell and the 2nd control in that cell.

Lastly, add the following lines to the gridview's prerender event:

Dim optEditDec As Decimal = 0.0

If GridView1.SelectedIndex > -1 AndAlso GridView1.SelectedRow.RowState > 3 Then
optEditDec = CType(CType(GridView1.SelectedRow.Cells(5).Controls(1), TextBox).Text, Decimal)
End If
gvProposal.FooterRow.Cells(5).Text = (sumfte + optEditDec).ToString


Now, you should be able to get the results you would expect from the footer row. This is untested for invalid textbox text. You may need to add code to make sure you get good decimal data to add.

If you have any problems with this or if I don't make any sense, feel free to email me about it! j@fr.com
Title: Thank you!   
Name: Bob Johnson
Date: 9/20/2006 10:15:35 PM
Comment:
Worked like a champ! Thank you.
Title: really good   
Name: violet nguyen
Date: 9/15/2006 12:33:33 PM
Comment:
Good article and solution.Thanks alots
Title: Help   
Name: Eric
Date: 9/8/2006 2:55:44 PM
Comment:
How do you get the unit price column to display as currency?
Title: Used Bind instead of Evail   
Name: Jeff
Date: 6/13/2006 5:24:28 PM
Comment:
Is it possible to use the bind instead of eval for this statement <%# GetUnitPrice(decimal.Parse(Eval("UnitPrice").ToString())).ToString("N2") %>

When I tried it with bind I got an error.
Title: Bind   
Name: C
Date: 6/13/2006 4:27:00 PM
Comment:
How can I get it to work using Bind instead of eval on this line:
GetUnitPrice(decimal.Parse(Eval("UnitPrice").ToString())).ToString("N2")
Title: I am sorry. My question was not clear. I tried to convert your aspx code to code behind style like this below. But this is not working.   
Name: young
Date: 6/9/2006 2:13:02 PM
Comment:
Dim TotalUnitPrice As Decimal = 0.0
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim col1 As BoundField = New BoundField()
Dim col2 As BoundField = New BoundField()
Dim col3 As TemplateField = New TemplateField()


col1.DataField = "Productid"
col1.HeaderText = "Product Id"
col1.SortExpression = "Productid"

col2.DataField = "ProductName"
col2.HeaderText = "Product Name"
col2.FooterText = "Total"

col3.HeaderText = "Unit Price"
col3.ItemTemplate() = GetUnitPrice(Decimal.Parse(Eval("UnitPrice").ToString())).ToString("N2")
col3.FooterTemplate = GetTotal().ToString("N2")

GridView1.Columns.Add(col1)
GridView1.Columns.Add(col2)
GridView1.Columns.Add(col3)



End Sub

Function GetUnitPrice(ByVal Price As Decimal) As Decimal
TotalUnitPrice += Price
Return Price
End Function
Function GetTotal() As Decimal
Return TotalUnitPrice
End Function
Title: RE:   
Name: Sushila
Date: 6/9/2006 11:11:00 AM
Comment:
The code is given in both VB.NET and C#
Title: Help me   
Name: young
Date: 6/9/2006 11:00:33 AM
Comment:
Does anyone know how to convert this aspx code to VB code behind style?
Title: Good Stuff With Very Little Code   
Name: Charlie
Date: 5/9/2006 6:25:32 PM
Comment:
Excellent example. Much simpler than Microsoft's own at http://msdn2.microsoft.com/en-US/library/system.web.ui.webcontrols.templatefield.footertemplate.aspx.

Thanks.
Title: RE:   
Name: Sushila
Date: 4/7/2006 8:14:33 PM
Comment:
Alex,
The code is given in C# and VB.NET too
Check downloads section
Title: SIMILAR CODE IN C#   
Name: alex
Date: 4/7/2006 5:24:51 AM
Comment:
please upload a similar code in c#
Title: Smart One...   
Name: Ted
Date: 3/24/2006 4:25:19 PM
Comment:
Big help for me thank you very to you...
Title: good!!   
Name: yoginder
Date: 3/24/2006 8:35:07 AM
Comment:
good its help me a lot as i am a beginer so i am trying to get these type of from any where.

As i am geting training for asp.net but as compare to that one this one is better.
Title: Good one!   
Name: Rajeev Gopal
Date: 3/14/2006 9:29:33 AM
Comment:
Nice article.
Good to see that things have changed since http://aspalliance.com/494.

-Rajeev
Title: good!!!   
Name: sai
Date: 3/7/2006 1:50:13 AM
Comment:
Good Article

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 12/18/2014 5:19:06 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search