SumColumn: Custom DataGrid Column that automatically shows Sum/Count/Average in DataGrid Footer.

Title:       SumColumn: Custom DataGrid Column that automatically shows Sum/Count/Average in DataGrid Footer.
Author:      Syed Aziz ur Rahman
Environment: DotNet 1.x, NT 4.0, Win95/98/XP/2003
Keywords:    Sum Average DataGrid Column Custom DataGridColumn ASP.Net
Level:       Intermediate
Section      DotNet
Sub-Section   ASP.Net

• Download demo project - 6 Kb
• Download source - 2 Kb

Contents

• Introduction
• Background
  
  • Query database for the sum
  • Use DataTable Compute() Method
  • Use DataGrid ItemDataBound Event
• Problem
• SumColumn for the rescue
  
  • Introduction to Solution
  • 1st Pass: Creating the Initial Draft
  • 2nd Pass: Bindable with all DataSources
  • 3rd Pass: Customizable Output
  • 4th Pass: Tweaking the Design Time Output
• Conclusion
• Note
• References
• Revision History

Introduction

A normal requirement in any ASP.Net application is to sum/average up the values in a DataGrid column to the Footer. In this article we will compare the normal methods and later create our own custom DataGrid column "SumColumn", derived from BoundColumn to avoid repetition of the same code.

Back to top

Background

Let us consider the example of a DataGrid showing the list of employees. Columns are Name, Salary and Bonus. Normal solutions are

• Query database for the sum:
  One way is to compute the sum of the data through a SQL query. For example, to compute the sum of all salaries in Employee Table, the following SQL statement could be used:
  

  SELECT SUM(Salary)
  FROM Employees

  After firing the query using ADO.Net, we will save the result of query in a local variable e.g. internalSum. Dividing internalSum by the no of Items in DataGrid will yield average. Then we have to loop through the DataGrid Items Collection to find the Footer and set the value.
  

  For Each dgItem In Me.dgEmployee.Items
      If dgItem.ItemType = ListItemType.Footer Then
           	values = "Sum : " & internalSum & "<br>"
  	values += "Average : " & (internalSum / Me.dgEmployee.Items.Count)
  	dgItem.Cells(0).Text = values
      End If
  Next

  Disadvantages:
  
  • Two round trips to the database server; one for records and one for sum.
  • Also, if you want to compute sum for Bonus as well, then one will need one more SQL query i.e. no of queries will be (n+1) where n is the number of columns you want to sum up.
  • To show the sum in the Footer, one has to loop through the Items collection of the DataGrid, checking the Footer ItemType, setting the value.
  
  
  
• Use DataTable  Compute() Method:
  Another way is to use the Compute() method of the DataTable i.e. to get the same output as in first approach, we have to do like
  

  internalSum = dtEmployee.Compute("SUM(Salary)", String.Empty).ToString
  internalAvg = dtEmployee.Compute("AVG(Salary)", String.Empty).ToString

  Now do the above described looping technique and set the value in DataGrid footer.

  Disadvantages:
  

  • The results of the SQL query, the one that's being bound to the DataGrid, must be retrieved in a DataTable; no custom collection, Array or ArrayList will work.
  • To show the sum in the Footer, one has to loop through the Items collection of the DataGrid.

   

• Use DataGrid ItemDataBound Event:
  The idea is to use the ItemDataBound event of the DataGrid, grabbing the corresponding item value of all the rows of the DataGrid and summing it up. We have to keep the count of Items as well to calculate average. Then we could display the values in the Footer.
  

  Private Sub dgEmployee_ItemDataBound(ByVal sender As Object, _ 
  	ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
  	 Handles dgEmployee.ItemDataBound
  
          Select Case e.Item.ItemType
  	    Case ListItemType.Item, ListItemType.AlternatingItem
  	       internalSum += CType(e.Item.Cells(1).Text, Double)
  	       internalCount += 1	
  	    Case ListItemType.Footer
  		e.Item.Cells(0).Text = "Sum : " & Me.FormatDataValue(internalSum) & "<br>"
  		e.Item.Cells(0).Text += "Count : " & internalCount & "<br>"
  		e.Item.Cells(0).Text += "Average : " & _ 
  					Me.FormatDataValue(internalSum / internalCount)
  	End Select
  End Sub
  

  ADVANTAGES:
  

  • The DataSource of the DataGrid can be of any type. It is not restricted to be DataTable as in second solution
  • Only one query as compare to (n+1) queries in first solution.
  • No extra loop of DataGrid Item Collection to find Footer as in first and second solution.

Back to top

Problem

At this point you will be thinking that if the last solution does not have problems, what we are going to do. If it ain’t broken, why fix it. Actually I am tired of repeating the same old code in code behind of pages containing DataGrid. Are you not tired as well? I want to do it in an easier way; a way that is elegant as well. What if we do not have to write a single line of code and it will be done automatically. I am sure you will enjoy it as I do. May be you have heard that quote too

Laziness is the mother of invention.

Back to top

SumColumn for the Rescue

    Introduction to Solution

Now it is the time to pull the cat out of the bag. Solution to this problem is to build the desired functionality right into a custom DataGridColumn that we can then use on any page! Confused, let me explain. We are going to create our own custom DataGrid column which we will refer as “SumColumn”, inherited from DataGrid BoundColumn. Due to its base class, the new SumColumn class will have all of the built-in functionality already present in the BoundColumn class. We just need to implement our sum functionality. Inheriting powerful controls like the BoundColumn class and adding new functionality to them is one of the OO features of the .NET Framework.

Before we dive into creating our custom column class, let's first look at an example that simply uses a normal BoundColumn control to display the data. For this, we set up a simple DataGrid that displays the Name, Salary and Bonus from Employee Table.

<asp:datagridid="dgEmployee"runat="server"ShowFooter="True"AutoGenerateColumns="False">
    	<FooterStyleForeColor="#330099"></FooterStyle>
	<SelectedItemStyleForeColor="#663399" </SelectedItemStyle>
	<ItemStyleForeColor="#330099"></ItemStyle>  
	<HeaderStyleFont-Bold="True"ForeColor="#FFFFCC"></HeaderStyle>  
	<Columns>
		< asp:BoundColumn DataField ="Name" HeaderText ="Name"></ asp:BoundColumn > 
		< asp:BoundColumn DataField ="Bonus" HeaderText ="Bonus"></ asp:BoundColumn > 
		< asp:BoundColumn DataField ="Salary" HeaderText ="Salary"></ asp:BoundColumn >
	</Columns>
</asp:datagrid>

And in the code behind, we would do in the page load event.

dgEmployee.DataSource = GetEmployees()
dgEmployee.DataBind()

Where GetEmployees() will return a DataTable containing the employee records. Normal output will be like (depending upon the data)

Back to top

    1st Pass: Creating the Initial Draft

If you have Visual Studio .NET, create a new VB.Net project of type Class Library named CustomWebControls. You will need to add the reference of System.Web.dll assembly in your project. Otherwise create a file SumColumn.vb. We will make the class step by step as we proceed.

Imports System
Imports System.Web 
Imports System.Web.UI 
Imports System.Web.UI.WebControls 
Imports System.ComponentModel 
Public Class SumColumn
    Inherits BoundColumn 
End Class 

BoundColumn class has two methods that are normally overridden to provide some custom functionality. One is FormatDataValue() and other is InitializeCell(). The FormatDataValue method is normally used along with the DataFormatString property to format numeric and date information. Therefore we are going to use the second one i.e. InitializeCell. This method is much like the DataGrid ItemCreated method. The signature of the method is

Public Overrides Sub InitializeCell(ByVal cell As System.Web.UI.WebControls.TableCell, _ 
	ByVal columnIndex As Integer, ByVal itemType As System.Web.UI.WebControls.ListItemType)

We do not have the value of cell at this time because cell is just being created. We should somehow link ourselves with the binding of the cell. This can be done by attaching a handler on the fly to the cell DataBound event

Public Overrides Sub InitializeCell(ByVal cell As System.Web.UI.WebControls.TableCell, _ 
	ByVal columnIndex As Integer, ByVal itemType As System.Web.UI.WebControls.ListItemType)

        MyBase.InitializeCell(cell, columnIndex, itemType)
        Select Case itemType
            Case ListItemType.AlternatingItem, ListItemType.Item, ListItemType.Footer                
		AddHandler cell.DataBinding, AddressOf CellItemDataBound
        End Select 
End Sub

We have called the InitializeCell method of the base class so that it can do the routine work. We have also filter the ItemType using select case because we only want to deal with Item, AlternalteItem and Footer. Our method, CellItemDataBound has been attached with the DataBinding event of cell. Inside this method we will do our magic. Just recall the third solution described previously.

Private Sub CellItemDataBound(ByVal sender As Object, ByVal e As EventArgs)
        Dim cell As TableCell = CType(sender, TableCell)
        Dim DGI As DataGridItem = CType(cell.NamingContainer, DataGridItem)
        Dim dValue As Decimal
        Dim dataItem As Object = DGI.DataItem

        Select Case DGI.ItemType
            Case ListItemType.AlternatingItem, ListItemType.Item
		 dValue = DGI.DataItem(DataField)
                   internalSum += dValue
		 internalCount += 1
                   cell.Text = Me.FormatDataValue(dValue)

	Case ListItemType.Footer
		cell.Text = "Sum : " & Me.FormatDataValue(internalSum) & "<br>"
		cell.Text += "Count : " & internalCount & "<br>"
		cell.Text += "Average : " & Me.FormatDataValue(internalSum / internalCount)              
	End Select 
End Sub

Code is pretty straight forward. We just grabbed the DataItem out of the sender object, and then check the ItemType. If it is Item or AlternateItem, we set the text of the cell; add to internalSum and increment the internalCount. If it is Footer, we just concatenate the values and set the text of cell. Please note that we have called FormatDataValue method of base class to format the output. No complications till yet.

Compile the class library project. Add the reference of its output to your web project. Register the tag at the top of aspx page.

<%@ Register TagPrefix="Custom" Namespace="CustomWebControls" Assembly="CustomWebControls" %>

Use the new SumColumn instead of BoundColumn for Salary in the aspx.

<Columns>
 < asp:BoundColumn DataField ="Name" HeaderText ="Name"></ asp:BoundColumn > 
 < asp:BoundColumn DataField ="Bonus" HeaderText ="Bonus"></ asp:BoundColumn > 
 <Custom:SumColumn DataField="Salary" HeaderText="Salary" DataFormatString="{0:C}"></Custom:SumColumn>
</Columns>

If we run our web project, the output will be more or less like

Back to top

    2nd Pass: Bindable with all DataSources

Isn’t it great? We did not write a single line of code in the code behind of WebForm and we are getting sum/count/average in the Footer. You can do the same with the bonus column by just changing the column type to SumColumn. But there is just one little problem. This control works well if the DataSource of DataGrid is DataTable or DataReader. If you try to use Array or ArrayList of custom objects, you will probably see the output like

We have to think of a way that can handle any type of DataSource. Hmmm.. What about using <SPAN lang=en-us>PropertyDescriptor class of System.ComponentModel namespace to get the value regardless of the underlying object. We will update the following line in  CellItemDataBound 

       dValue = DGI.DataItem(DataField) 

to the line below

      dValue = Me.GetUnderlyingValue(dataItem)

and add a new method GetUnderlyingValue in our class.

    Protected Function GetUnderlyingValue(ByVal dataItem As Object) As Decimal
 
	Dim boundFieldDesc As PropertyDescriptor = _ 
			TypeDescriptor.GetProperties(dataItem).Find(Me.DataField, True)
 
         If(boundFieldDesc Is Nothing) Then
            Throw New HttpException("Field Not Found: " + Me.DataField)
	End If 
 
	Dim dValue As Object = boundFieldDesc.GetValue(dataItem)
       	Return Decimal.Parse(dValue.ToString())
 
    End Function 

Instead of just pulling the value out of DataItem, we are depending on GetUnderlyingValue Method to get value of the DataItem for us which in turn use TypeDescriptor class to check whether the DataField exists in the underlying object. If succeed, it returns the value to the calling method otherwise throw Exception. Now you can check the output with (nearly) all kinds of DataSource.

Back to top

    3rd Pass: Customizable Output

All seems fine now but there is no control on the output. What I mean is may be you want to just show sum, no average, no count; someone else wants to show sum and average. There are different possibilities so there should be some way to customize the output as per needed. Here is what we can do.

#Region " Attributes "
	Private internalSum As Decimal
	Private internalCount As Integer
	Private _ShowSum As Boolean = True
	Private _ShowCount As Boolean = True
	Private _ShowAverage As Boolean = True
#End Region


#Region " Properties "
Public Property ShowSum() As Boolean
	Get
		Return _ShowSum
	End Get
	Set(ByVal Value As Boolean)
		_ShowSum = Value
	End Set
End Property


Public Property ShowCount() As Boolean
	Get
		Return _ShowCount
	End Get
	Set(ByVal Value As Boolean)
		_ShowCount = Value
	End Set
End Property


Public Property ShowAverage() As Boolean
	Get
		Return _ShowAverage
	End Get
	Set(ByVal Value As Boolean)
		_ShowAverage = Value
	End Set
End Property
#End Region

We exposed three public properties i.e. ShowSum, ShowCount, ShowAverage in our SumColumn class. One can use these properties in aspx to customize the output e.g.

<Custom:SumColumn ShowSum="True" ShowCount="False" ShowAverage="True" DataFormatString="{0:C}" 
	DataField="Salary" HeaderText="Salary"></Custom:SumColumn>

OR

<Custom:SumColumn ShowSum="False" ShowCount="False" ShowAverage="True" DataFormatString="{0:C}" 
	DataField="Salary" HeaderText="Salary"></Custom:SumColumn>

Internally in our class, we can check the values of exposed properties in the CellItemDataBound method to customize the output according to requirement.

Case ListItemType.Footer
	If Me._ShowSum = True Then
		cell.Text = "Sum : " & Me.FormatDataValue(internalSum) & "<br>"
	End If


	If Me._ShowCount = True Then
		cell.Text += "Count : " & internalCount & "<br>"
	End If


	If Me._ShowAverage = True Then
		cell.Text += "Average : " & Me.FormatDataValue(internalSum / internalCount)
	End If
End If

If you update the code and aspx, you may get the output as below

Back to top

    4th Pass: Tweaking the Design Time Output

Yes, I know that now you all are feeling sleepy but please give me just 5 more minutes. If you are not interested in changing the design time output (i.e. when we see the DataGrid in design time using Visual Studio), then you can skip this section. For those who are still reading, check the change in code below.

Case ListItemType.AlternatingItem, ListItemType.Item

	If Me.DesignMode = False Then
		dValue = Me.GetUnderlyingValue(dataItem)
		.....
		.....
		cell.Text = Me.FormatDataValue(dValue)
	Else
		cell.Text = "SumColumn"
	End If

Case ListItemType.Footer
	If Me.DesignMode = False Then
		If Me._ShowSum = True Then
		.....
		.....
		End If
	Else
		cell.Text = "Total"
	End If

I think that the above code is quite self explanatory. We simply used the DesignMode property of the base class BoundColumn to tweak the design time output of the SumColumn .

Back to top

Conclusion

Now we have it. Our own new custom column SumColumn derived from BoundColumn having the functionality of showing the sum/average/count of values of the column in the footer of the DataGrid. This is just one example of a reusable DataGrid column and it is up to you to examine your own applications and find out what could be neatly wrapped up into a custom DataGrid column.

Back to top

Note

• The downloadable code is not of production quality. The whole sole purpose was to initiate a thought that repetitive code can be wrapped inside a module.
• The code contains a custom class Employee which return the records in different formats e.g. DataTable, ArrayList and Array. One can use the sample page Test.aspx to check with all three type of DataSource.
• This control has not been tested using XML DataSource.
  
  Back to top

References

• Creating a Custom DataGridColumn Class By John Dyer   article for beginners.
• Creating Custom Columns for the ASP.Net DataGrid by DataGridGirl    One advance but comprehensive article.
• Metabuilders BoundBooleanColumn   I was inspired by Andy of Metabuilders to make custom columns.

Back to top

Revision History

02-15-2006:

• First revision. Incorporated readers comments: Customizable output i.e. sum as well as average and count. Format the whole article, added a few sections, updated the code.

02-07-2006:

• Original article

Back to top

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 10 of 10 (Total in Forum: 10) (Refresh) FirstPrevNext

Hi... Your image was missed ... YU-TIEN 15:57 4 Dec '07   Hi : On the article image was missed, please upload they thanks .. Sign In·View Thread·PermaLink How to remove text in footer? kidkodiak011 13:13 1 Jun '07   I can't seem to fix this, I'm feeling really defunct. In the footer, to remove the text "sum, count, average" from the VB file has no bearing on the datagrid formatting... how can I only display numeric entries in the footer results? Sign In·View Thread·PermaLink Re: How to remove text in footer? Syed Aziz ur Rahman 22:28 1 Jun '07   Download the source code of the control. Check line no 91-99. Remove the text from the code. Syed Aziz ur Rahman Sign In·View Thread·PermaLink Re: How to remove text in footer? kidkodiak011 12:08 4 Jun '07   Hmm, thats' what I edited and it didn't change the display. Here's what I did: Case ListItemType.Footer If Me.DesignMode = False Then 'set the running total If Me._ShowSum = True Then cell.Text = Me.FormatDataValue(internalSum) & " < br >" End If If Me._ShowCount = True Then cell.Text += Me.FormatDataValue(internalCount) & " < br > " End If If Me._ShowAverage = True Then cell.Text += Me.FormatDataValue(internalSum / internalCount) End If Else cell.Text = "Total" End If I recompiled, and uploaded a new DLL and a new VB source file to my application, but it ignores the fact that I removed the text from the cell.text properties and still shows the Sum/count/average text in the footer results. Sign In·View Thread·PermaLink Re: How to remove text in footer? kidkodiak011 6:47 6 Jun '07   Ok, I'm a monkey, I figured it out and it was my own fault. Any idea on how to format the DataFormatString in the footer? I only want to display a 2-place decimal point, but I don't want to carry a decimal place in the column value themselves... is that possible? Sign In·View Thread·PermaLink Implement within app_code directory kbearle 14:18 17 Aug '06   I think I should be able to implement this just by adding the SumColumn.vb to the app_code directory and then including a registry directive like: <%@ Register   NameSpace="Custom" TagPrefix="custom"   %> The field is then called through the following: <custom:SumColumn DataField="ACLS" HeaderText="ACLS" SortExpression="ACLS" /> after wrapping the sumcolumn class within the Custom namespace: Namespace Custom       Public Class SumColumn ...       End Class End Namespace But, this results in the following error when building the website (eg. compiling) System.Web.UI.WebControls.DataControlFieldCollection must have items of type 'System.Web.UI.WebControls.DataControlField'. 'custom:SumColumn' is of type 'Custom.SumColumn'. So, I must be doing something wrong (and probably it is pretty straightforward).   I'm using ASP.NET 2.0 VS2005.   Any ideas?          Sign In·View Thread·PermaLink 1.50/5 (2 votes) Sum, Average, and Count Calculation Rsproctor 11:54 24 Feb '06   You may want to add a disclaimer that the calculation of the total, count, and average are only good for a non-paginating grid. If your grid has multiple pages, the other pages of data will be left out of the calculation because the data binding event is only being executed on the current page. Sign In·View Thread·PermaLink 1.00/5 (1 vote) Nice...some Improvements! matthewtamm 3:53 8 Feb '06   Always the way...read this just as I was about to insert my usual code and gave this a go.   Works great but I needed some additional functionality that I thought I would share.. (I have ported to c# if anyone is interested!) ------------------------------------------------------------ 1) Added a "FooterFormatString" property...so I can, you guessed it, format the output in the Footer <mycontrols:SumColumnDataField FooterFormatString="{0:C}"... ------------------------------------------------------------ 2) Added a DisplayType enum & property to allow for different views of the data... "Sum" (default), "Count" & "Average" <mycontrols:SumColumnDataField DisplayType="Average" Including the Count & Average was simply added another variable to the class to keep count of the number of rows and then in "CellItemDataBound", checking the DisplayType and setting the cell.Text to the appropriate value. ------------------------------------------------------------ 3) In CellItemDataBound() when displaying the Item & AlternatingItem value, call the base FormatDataValue so any DataFormatString property on the column is preserved... cell.Text = FormatDataValue ( cellValue ) <mycontrols:SumColumnDataField DataFormatString="{0:C}" ------------------------------------------------------------ 4) Took the idea here and created a SumRowDataField.   Allows me to Sum, Count or Average a row in the grid based on a string of column names: <mycontrols:SumRowDataField    footerformatstring="{0:c}"    footerdisplaytype="Average"    dataformatstring="{0:c}"    ComputedFields="column1,columns2,column3" /> Also allows for the footer to Sum, Count or Average independent of the display used for the row's calculation. ------------------------------------------------------------ Thank you! Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: Nice...some Improvements! Syed Aziz ur Rahman 7:04 8 Feb '06   Very nice of you to throw such ideas. I will be implementing them and upload as soon as possible. Syed Aziz ur Rahman Sign In·View Thread·PermaLink Re: Nice...some Improvements! Syed Aziz ur Rahman 6:49 21 Feb '06   Your ideas has been implemented. and article is updated. Check it out now. Syed Aziz ur Rahman Sign In·View Thread·PermaLink 5.00/5 (2 votes)

Last Visit: Wednesday, December 3, 2008 9:17 PM     Last Update:Wednesday, December 3, 2008 9:17 PM