CodeSnip: How to Display Sum Total in the Footer of the GridView Control: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
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): 52898/ 28

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 Decimal) As Decimal
  TotalUnitPrice += Price
  Return Price
End Function
Function GetTotal() As Decimal
  Return TotalUnitPrice
End Function

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: 2007-05-30 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: 2007-05-21 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: 2007-05-11 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: hi
Name: James
Date: 2007-04-01 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: 2007-03-27 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: 2007-02-12 11:24:57 AM
Comment:
Thank you.

Title: Great Example
Name: FwI
Date: 2007-02-08 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: 2007-01-18 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: 2007-01-16 6:06:50 AM
Comment:
Good to see that things have changed since http://aspalliance.com/494.

Title: it worked
Name: Tobs
Date: 2007-01-15 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: 2007-01-09 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: 2006-12-29 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: 2006-12-20 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: 2006-12-20 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: 2006-11-30 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: 2006-11-21 4:21:39 AM
Comment:
Thanks a lot for putting helping hands

Title: what if you have 20 columns
Name: aron
Date: 2006-11-07 3:51:41 PM
Comment:
what if you want the sum's of 20+ columns?

Title: MMMh...
Name: Ryoushin
Date: 2006-10-12 5:13:48 AM
Comment:
How about paged results?...

Title: SOLVED - Retain sum value while editing a row
Name: Jon Medel
Date: 2006-09-22 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: 2006-09-20 10:15:35 PM
Comment:
Worked like a champ! Thank you.

Title: really good
Name: violet nguyen
Date: 2006-09-15 12:33:33 PM
Comment:
Good article and solution.Thanks alots

Title: Help
Name: Eric
Date: 2006-09-08 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: 2006-06-13 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: 2006-06-13 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: 2006-06-09 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: 2006-06-09 11:11:00 AM
Comment:
The code is given in both VB.NET and C#

Title: Help me
Name: young
Date: 2006-06-09 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: 2006-05-09 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: 2006-04-07 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: 2006-04-07 5:24:51 AM
Comment:
please upload a similar code in c#

Title: Smart One...
Name: Ted
Date: 2006-03-24 4:25:19 PM
Comment:
Big help for me thank you very to you...

Title: good!!
Name: yoginder
Date: 2006-03-24 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: 2006-03-14 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: 2006-03-07 1:50:13 AM
Comment:
Good Article

Product Spotlight