RadGrid for ASP.NET
Contents
Totals in grid footers Send comments on this topic.
How-to > Totals in grid footers

Glossary Item Box

Here are the steps you need to undertake to display totals in the grid columns footer:


  1. Subscribe to the ItemDataBound event of Telerik RadGrid
  2. Calculate the sum of the values in all cells of a specified column
  3. Insert the result in the footer of the corresponding column

Do not forget to set ShowFooter = true for the MasterTableView ,to visualize content in the grid footer.

See the forthcoming implementation for further info (Quantity is integer field in the grid database): 

ASPX/ASCX Copy Code
<rad:RadGrid id="RadGrid1" runat="server">
  <MasterTableView AutoGenerateColumns="False" ShowFooter="True">
    <HeaderStyle BackColor="#33ffff"></HeaderStyle>
    <FooterStyle BackColor="#cc6633"></FooterStyle>
      <Columns>
       <rad:GridBoundColumn HeaderText="Quantity" DataField="Quantity"
        UniqueName= "Quantity" />
      </Columns>
  </MasterTableView>
</rad:RadGrid>

And in the code-behind:

C# Copy Code
int total;
private void RadGrid1_ItemDataBound(object sender, Telerik.WebControls.GridItemEventArgs e)
{
 if(e.Item is GridDataItem)
 {
    GridDataItem dataItem = e.Item as GridDataItem;
    int fieldValue = int.Parse(dataItem["Quantity"].Text);
    total+= fieldValue;
 }
 if(e.Item is GridFooterItem)
 {
    GridFooterItem footerItem = e.Item as GridFooterItem;
    footerItem["Quantity"].Text = "total: " + total.ToString();
 }
}
VB.NET Copy Code
Dim total As Integer
Private Sub RadGrid1_ItemDataBound(ByVal sender As Object, ByVal e As Telerik.WebControls.GridItemEventArgs)
 If (TypeOf e.Item Is GridDataItem) Then
  Dim dataItem As GridDataItem = CType(e.Item,GridDataItem)
  Dim fieldValue As Integer = Integer.Parse(dataItem("Quantity").Text)
  total = (total + fieldValue)
 End If
 If (TypeOf e.Item Is GridFooterItem) Then
  Dim footerItem As GridFooterItem = CType(e.Item,GridFooterItem)
  footerItem("Quantity").Text = "total: " + total.ToString()
 End If
End Sub

 

Displaying totals for all grid pages


If you prefer to show grand total in a column footer which calculates the sum for the data in the column cells in all available pages, you need to:

  1. Subscribe to the DataBound event of RadGrid
  2. Iterate through the rows in the underlying grid source
  3. Sum up the total and insert it in the respective column footer

Below are the code snippets of a sample approach (note that with other type of data sources you may need to traverse and extract the data in a different manner):

ASPX/ASCX Copy Code
<rad:RadGrid ID="RadGrid1" runat="server" Width="500px" Skin="Grey">
               <MasterTableView AutoGenerateColumns="False" ShowFooter="True" AllowPaging="true">
                   <FooterStyle BackColor="#cc6633"></FooterStyle>
                   <Columns>
                       <rad:GridBoundColumn HeaderText="Quantity" DataField="Quantity" UniqueName="Quantity" />
                   </Columns>
               </MasterTableView>
               <PagerStyle Mode="NextPrevAndNumeric" />
</rad:RadGrid>
VB.NET Copy Code
    Dim total As Integer
    Dim grandTotal As Integer

    Private Sub RadGrid1_ItemDataBound(ByVal sender As Object, ByVal e As Telerik.WebControls.GridItemEventArgs) Handles RadGrid1.ItemDataBound
         If (TypeOf e.Item Is GridDataItem) Then
             Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
             Dim fieldValue As Integer = Integer.Parse(dataItem("Quantity").Text)
            total = (total + fieldValue)
         End If
         If (TypeOf e.Item Is GridFooterItem) Then
             Dim footerItem As GridFooterItem = CType(e.Item, GridFooterItem)
            footerItem( "Quantity").Text = "Total: " + total.ToString()
         End If
    End Sub

    Protected Sub RadGrid1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles RadGrid1.DataBound
         Dim gridTable As DataTable = GetDataTable("SELECT * FROM [Order Details]")

         For Each row As DataRow In gridTable.Rows
            grandTotal = grandTotal + Integer.Parse(row("Quantity"))
         Next
         Dim footerItem As GridFooterItem = RadGrid1.MasterTableView.GetItems(GridItemType.Footer)(0)
        footerItem( "Quantity").Text = footerItem("Quantity").Text + "<br> Total for all pages: " + grandTotal.ToString()
    End Sub

    Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.WebControls.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
        RadGrid1.DataSource = GetDataTable("SELECT * FROM [Order Details]")
    End Sub
    Public Function GetDataTable(ByVal query As String) As DataTable
         Dim connection1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & System.Web.HttpContext.Current.Server.MapPath( "~/Grid/Data/Access/NWind.mdb"))
         Dim adapter1 As New OleDbDataAdapter
        adapter1.SelectCommand = New OleDbCommand(query, connection1)
         Dim table1 As New DataTable
        connection1. Open()
        Try
            adapter1.Fill(table1)
        Finally
            connection1. Close()
         End Try
         Return table1
    End Function

 

C# Copy Code
int total;
int grandTotal;

private void RadGrid1_ItemDataBound(object sender, Telerik.WebControls.GridItemEventArgs e)
{
if (e.Item is GridDataItem)
{
 GridDataItem dataItem = (GridDataItem)e.Item;
 int fieldValue = int.Parse(dataItem["Quantity"].Text);
 total = total + fieldValue;
}
if (e.Item is GridFooterItem)
{
 GridFooterItem footerItem = (GridFooterItem)e.Item;
 footerItem["Quantity"].Text = "Total: " + total.ToString();
}
}

protected void RadGrid1_DataBound(object sender, System.EventArgs e)
{
DataTable gridTable = GetDataTable("SELECT * FROM [Order Details]");

foreach (DataRow row in gridTable.Rows)
{
 grandTotal = grandTotal + int.Parse(row["Quantity"]);
}
GridFooterItem footerItem = RadGrid1.MasterTableView.GetItems(GridItemType.Footer)[0];
footerItem["Quantity"].Text = footerItem["Quantity"].Text + "<br> Total for all pages: " + grandTotal.ToString();
}

protected void RadGrid1_NeedDataSource(object source, Telerik.WebControls.GridNeedDataSourceEventArgs e)
{
RadGrid1.DataSource = GetDataTable("SELECT * FROM [Order Details]");
}
public DataTable GetDataTable(string query)
{
OleDbConnection connection1 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + System.Web.HttpContext.Current.Server.MapPath( "~/Grid/Data/Access/NWind.mdb"));
OleDbDataAdapter adapter1 = new OleDbDataAdapter();
adapter1.SelectCommand = new OleDbCommand(query, connection1);
DataTable table1 = new DataTable();
connection1.Open();
try
{
 adapter1.Fill(table1);
}
finally
{
 connection1.Close();
}
return table1;
}

Multi-line footer totals


When you want to "enhance" your footer and display the items in it in multiline mode, you can wire the ItemDataBound event of the grid and add the values which you want to be shown at the bottom of the respective columns footer cells.

The forthcoming code snippets will render two rows in the table footer which will show the total price/units in stock along with the highest price/units quantity for the records displayed. Note that the data will be refreshed accordingly when you sort/page/filter records in the grid:
    ASPX/ASCX Copy Code
    <rad:RadGrid ID="RadGrid1" DataSourceID="AccessDataSource1" AllowSorting="True"
         Skin="Office2007" runat="server" GridLines="None" Width="600px" AllowPaging="true" AllowFilteringByColumn="true"
         OnItemDataBound="RadGrid1_ItemDataBound" OnColumnCreated="RadGrid1_ColumnCreated">
             <MasterTableView Width="100%" AllowMultiColumnSorting="true" DataKeyNames="UnitPrice, UnitsInStock" ShowFooter="true" />
                       <PagerStyle Mode="NextPrevAndNumeric" />
                   </rad:RadGrid>
                   <br />
    <    asp:AccessDataSource ID="AccessDataSource1" DataFile="~/Grid/Data/Access/Nwind.mdb"
    SelectCommand="SELECT ProductName, UnitPrice, UnitsInStock FROM [Products]"
    runat="server" />
    C# Copy Code
       double totalPrice = 0;
           double totalUnits = 0;

           double highestPrice = 0;
           double highestUnitQuantity = 0;

           protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
       {
                if (e.Item is GridFooterItem)
           {
               GridFooterItem footerItem = e.Item     as GridFooterItem;

               footerItem[     "ProductName"].Controls.Add(new LiteralControl("Total: <br/>"));
               footerItem[     "ProductName"].Controls.Add(new LiteralControl("Highest price/units quantity: "));

               footerItem[     "UnitPrice"].Controls.Add(new LiteralControl("$" + totalPrice.ToString()+ "<br/>"));
               footerItem[     "UnitPrice"].Controls.Add(new LiteralControl("$" + highestPrice.ToString()));

               footerItem[     "UnitsInStock"].Controls.Add(new LiteralControl(totalUnits.ToString() + "<br/>"));
               footerItem[     "UnitsInS tock"].Controls.Add(new LiteralControl(highestUnitQuantity.ToString()));
           }
                else if (e.Item is GridDataItem)
           {
               GridDataItem dataItem = e.Item     as GridDataItem;

                    string formatPrice = dataItem.OwnerTableView.DataKeyValues[dataItem.ItemIndex][ "UnitPrice"].ToString();
                    double fieldValue = Double.Parse(formatPrice);
               totalPrice += fieldValue;

                    if (highestPrice == 0 || highestPrice < fieldValue)
               {
                   highestPrice = fieldValue;
               }

               fieldValue = Double.Parse(dataItem.OwnerTableView.DataKeyValues[dataItem.ItemIndex][     "UnitsInStock"].ToString());
               totalUnits += fieldValue;

                    if (highestUnitQuantity == 0 || highestUnitQuantity < fieldValue)
               {
                   highestUnitQuantity = fieldValue;
               }
           }
       }
           protected void RadGrid1_ColumnCreated(object sender, GridColumnCreatedEventArgs e)
       {
                if (e.Column.UniqueName == "UnitPrice")
           {
               (e.Column     as GridBoundColumn).DataFormatString = "{0:$###,###.##}";
           }
       }
    VB.NET Copy Code
    Private totalPrice As Double = 0
    Private totalUnits As Double = 0

    Private highestPrice As Double = 0
    Private highestUnitQuantity As Double = 0

    Protected Sub RadGrid1_ItemDataBound(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles RadGrid1.ItemDataBound
     If TypeOf e.Item Is GridFooterItem Then

       Dim footerItem As GridFooterItem = CType(e.Item, GridFooterItem)

       footerItem("ProductName").Controls.Add(New LiteralControl("Total: <br/>"))
       footerItem("ProductName").Controls.Add(New LiteralControl("Highest price/units quantity: "))

       footerItem("UnitPrice").Controls.Add(New LiteralControl("$" & totalPrice.ToString & "<br/>"))
       footerItem("UnitPrice").Controls.Add(New LiteralControl("$" & highestPrice.ToString))

       footerItem("UnitsInStock").Controls.Add(New LiteralControl(totalUnits.ToString & "<br/>"))
       footerItem("UnitsInStock").Controls.Add(New LiteralControl(highestUnitQuantity.ToString))
     Else
       If TypeOf e.Item Is GridDataItem Then

         Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)

         Dim formatPrice As String = dataItem.OwnerTableView.DataKeyValues(dataItem.ItemIndex)( "UnitPrice").ToString()
         Dim fieldValue As Double = Double.Parse(formatPrice)
         totalPrice += fieldValue

         If highestPrice = 0 OrElse highestPrice < fieldValue Then
           highestPrice = fieldValue
         End If

         fieldValue = Double.Parse(dataItem.OwnerTableView.DataKeyValues(dataItem.ItemIndex)( "UnitsInStock").ToString())
         totalUnits += fieldValue

         If highestUnitQuantity = 0 OrElse highestUnitQuantity < fieldValue Then
           highestUnitQuantity = fieldValue
         End If
       End If
     End If
    End Sub

    Protected Sub RadGrid1_ColumnCreated(ByVal sender As Object, ByVal e As GridColumnCreatedEventArgs) Handles RadGrid1.ColumnCreated
     If e.Column.UniqueName = "UnitPrice" Then
       (CType(e.Column, GridBoundColumn)).DataFormatString = "{0:$###,###.##}"
     End If
    End Sub

     

    Client-side calculation of totals for a template column

    Sometimes, it may be necessary to calculate the sum for a particular column once, when the page loads, and then allow the user to alter the values for a specific row, and have these values update not only in the textbox, where the value has been entered, but also in the footer textbox. This is achievable through custom code, as shown in the sample project listed below.

    Below is the declaration of the grid, which contains a template column, with textboxes for the normal items, and the footer template:

    ASPX/ASCX Copy Code
    <form id="form1" runat="server">
               <rad:RadGrid
                ShowFooter= "true"
                ID= "RadGrid1" runat="server" DataSourceID="AccessDataSource1" GridLines="None"
                OnPreRender= "RadGrid1_PreRender" OnItemDataBound="RadGrid1_ItemDataBound">
                   <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID"
                   DataSourceID= "AccessDataSource1">
                       <Columns>
                           <rad:GridBoundColumn DataField="OrderID" DataType="System.Int32"
                           HeaderText= "OrderID"
                               ReadOnly= "True"
                               SortExpression= "OrderID" UniqueName="OrderID">
                           </rad:GridBoundColumn>
                           <rad:GridTemplateColumn UniqueName="Template1">
                           <ItemTemplate>
    <    asp:TextBox runat="server" ID="TextBox1" Text='<% #Eval("Freight") %>'></asp:TextBox>
                           </ItemTemplate>
                           <FooterTemplate>
                           <asp:TextBox runat="Server" ID="TextBox2">
                           </asp:TextBox>
                           </FooterTemplate>
                           </rad:GridTemplateColumn>
                       </Columns>
                       <ExpandCollapseColumn Visible="False">
                           <HeaderStyle Width="19px" />
                       </ExpandCollapseColumn>
                       <RowIndicatorColumn Visible="False">
                           <HeaderStyle Width="20px" />
                       </RowIndicatorColumn>
                   </MasterTableView>
               </rad:RadGrid><asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Nwind.mdb"
                   SelectCommand= "SELECT TOP 10 [OrderID], [Freight] FROM [Orders]"></asp:AccessDataSource>
           </form>

    This particular implementation calculates the values for the footer textbox once for each time the control (re)binds to data. This will ensure that the textbox value is properly calculated initially, to avoid having to write additional JavaScript. Another important piece of the logic is the assignment of “onblur” and “onfocus” client side events. The client-side functions are passed the clientId’s for the footer textbox, as well as the textbox on which the user focuses, and on which the user will later lose focus. As soon as the user navigates to another textbox, the footer textbox will be updated with the new values:

    C# Copy Code
    public partial class _Default : System.Web.UI.Page
    {
           double sum = 0;
           string clientID;


           protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
       {
                if (e.Item is GridDataItem)
           {
               GridDataItem dataItem = (GridDataItem)e.Item;
               sum+=    double.Parse((dataItem[ "Template1"].FindControl("TextBox1") as TextBox).Text);
           }
                else if (e.Item is GridFooterItem)
           {
               GridFooterItem footer = (GridFooterItem)e.Item;
               (footer[     "Template1"].FindControl("TextBox2") as TextBox).Text = sum.ToString();
               clientID = (footer[    "Template1"].FindControl("TextBox2") as TextBox).ClientID;
           }
       }



           protected void RadGrid1_PreRender(object sender, EventArgs e)
       {
               foreach (GridDataItem dataItem in RadGrid1.MasterTableView.Items)
           {
               (dataItem[     "Template1"].FindControl("TextBox1") as TextBox).Attributes.Add("onblur", "update('" + clientID + "'" + "," + "'" + (dataItem["Template1"].FindControl( "TextBox1") as TextBox).ClientID + "')");
               (dataItem[     "Template1"].FindControl("TextBox1") as TextBox).Attributes.Add("onfocus", "getInitialValue('" + (dataItem["Template1"].FindControl( "TextBox1") as TextBox).ClientID + "')");
           }
       }
    }

     

    VB.NET Copy Code
    Public Class _Default
     Inherits System.Web.UI.Page
     Private sum As Double = 0
     Private clientID As String

     Protected Sub RadGrid1_ItemDataBound(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles RadGrid1.ItemDataBound
      If TypeOf e.Item Is GridDataItem Then
       Dim dataItem As GridDataItem = DirectCast((e.Item), GridDataItem)
       sum = (sum + Double.Parse((DirectCast((dataItem("Template1").FindControl("TextBox1")), TextBox)).Text))
    ElseIf TypeOf e.Item Is GridFooterItem Then
       Dim footer As GridFooterItem = DirectCast((e.Item), GridFooterItem)
       (DirectCast((footer("Template1").FindControl("TextBox2")), TextBox)).Text = sum.ToString
       clientID = (DirectCast((footer("Template1").FindControl("TextBox2")), TextBox)).ClientID
      End If
     End Sub
     Protected Sub RadGrid1_PreRender(ByVal sender As Object, ByVal e As EventArgs) Handles RadGrid1.PreRender
      For Each dataItem As GridDataItem In RadGrid1.MasterTableView.Items
       (DirectCast((dataItem("Template1").FindControl("TextBox1")), TextBox)).Attributes.Add("onblur", ("update('" + (clientID + ("'" + ("," + ("'" + ((DirectCast((dataItem("Template1").FindControl("TextBox1")), TextBox)).ClientID + "')")))))))
       (DirectCast((dataItem("Template1").FindControl("TextBox1")), TextBox)).Attributes.Add("onfocus", ("getInitialValue('" + ((DirectCast((dataItem("Template1").FindControl("TextBox1")), TextBox)).ClientID + "')")))
      Next
     End Sub
    End Class




    The final part of the logic is the client-side script, which handles the updates of the footer textbox. Once the user focuses on a textbox, the script will preserve its initial value. The user is then free to enter a new value. Once the user loses focus on the textbox, the value in the footer textbox will be recalculated. For simplicity, the parsing of the user input is omitted.

     

    JavaScript Copy Code
       <script type="text/javascript">
            var initialValue = 0.0;
         function update(footerBox, changedBox)
         {
             var footerBoxControl = document.getElementById(footerBox);
         var changedBoxControl     = document.getElementById(changedBox);
         var tempValue     = footerBoxControl.value - initialValue;
         footerBoxControl.value     = parseFloat(tempValue) + parseFloat(changedBoxControl.value);
         }

             function getInitialValue(changedBox)
         {
             var changedBoxControl= document.getElementById(changedBox);
         initialValue     = parseFloat(changedBoxControl.value);
         }
           </script>