RadGrid for ASP.NET AJAX

RadControls for ASP.NET AJAX

There are various cases in which you may want to display results from aggregate functions performed over the columns in the grid in their footer.

Declarative solution

RadGrid provides an intuitive method to define aggregates on a per column basis from design time and render the results inside the respective column's footer. Aggregate calculations are supported for GridBoundColumns and GridCalculatedColumns.

In order to specify how the group aggregates will be evaluated, specify in every GridBoundColumn/GridCalculatedColumn the Aggregate property which accepts values from the GridAggregateFunction enumeration. The options available for the GridAggregateFunction enumeration are listed below:

GridAggregateFunction enumeration members

 

Member

Description

Sum

Returns the sum of all column values in the source.

Min

Returns the min value from the source column.

Max

Returns the max value from the source column.

Last

Returns the last value from the source column.

First

Returns the first value from the source column.

Count

Returns the count of source column values, including null values.

Avg

Returns the average value from the source column values.

Custom

Raises OnCustomAggregate event where the custom result can be set using the e.Result argument.

None

Default value, no aggregate function applied.

Note

RadGrid will calculate aggregates over the entire data source and will respect the filter expression applied (if present)

For further details please refer to this online demo of the product. Find a code extraction below:

CopyASPX
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
  <AjaxSettings>
    <telerik:AjaxSetting AjaxControlID="RadGrid1">
      <UpdatedControls>
        <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
      </UpdatedControls>
    </telerik:AjaxSetting>
  </AjaxSettings>
</telerik:RadAjaxManager>
<telerik:RadGrid ShowGroupPanel="true" AutoGenerateColumns="false" ID="RadGrid1"
  DataSourceID="SqlDataSource1" Skin="Vista" AllowFilteringByColumn="True" AllowSorting="True"
  ShowFooter="True" runat="server" GridLines="None" OnCustomAggregate="RadGrid1_CustomAggregate">
  <PagerStyle Mode="NextPrevAndNumeric" />
  <HeaderStyle Width="150px" />
  <MasterTableView TableLayout="Fixed">
    <Columns>
      <telerik:GridNumericColumn Aggregate="Avg" DataField="OrderID" HeaderText="OrderID"
        SortExpression="OrderID" UniqueName="OrderID">
      </telerik:GridNumericColumn>
      <telerik:GridBoundColumn Aggregate="Count" DataField="ShipName" HeaderText="ShipName"
        SortExpression="ShipName" UniqueName="ShipName">
      </telerik:GridBoundColumn>
      <telerik:GridDateTimeColumn Aggregate="Last" DataField="OrderDate" HeaderText="OrderDate"
        SortExpression="OrderDate" UniqueName="OrderDate" PickerType="None" DataFormatString="{0:d}">
      </telerik:GridDateTimeColumn>
      <telerik:GridDateTimeColumn Aggregate="First" DataField="ShippedDate" HeaderText="ShippedDate"
        SortExpression="ShippedDate" UniqueName="ShippedDate" PickerType="DatePicker" DataFormatString="{0:D}">
      </telerik:GridDateTimeColumn>
      <telerik:GridBoundColumn Aggregate="Min" DataField="ShipCountry" HeaderText="ShipCountry"
        SortExpression="ShipCountry" UniqueName="ShipCountry">
      </telerik:GridBoundColumn>
      <telerik:GridMaskedColumn Aggregate="Max" DataField="ShipPostalCode" HeaderText="ShipPostalCode"
        SortExpression="ShipPostalCode" UniqueName="ShipPostalCode" Mask="#####">
      </telerik:GridMaskedColumn>
      <telerik:GridNumericColumn Aggregate="Sum" DataField="Freight" DataType="System.Decimal"
        HeaderText="Freight" SortExpression="Freight" UniqueName="Freight">
      </telerik:GridNumericColumn>
      <telerik:GridBoundColumn Aggregate="Custom" DataField="CustomerID" DataType="System.String"
        HeaderText="CustomerID" SortExpression="CustomerID" UniqueName="CustomerID">
      </telerik:GridBoundColumn>
    </Columns>
  </MasterTableView>
  <ClientSettings AllowDragToGroup="true" Scrolling-AllowScroll="true" Scrolling-UseStaticHeaders="true" />
</telerik:RadGrid>
<asp:Button ID="Button1" Text="PostBack!" CssClass="button" Style="margin: 10px 22px 10px 0px"
  runat="server"></asp:Button>
Click PostBack to see the state of the grid is preserved.
<br />
<asp:SqlDataSource ID="SqlDataSource1" SelectCommand="SELECT TOP 50 * FROM Orders"
  ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" ProviderName="System.Data.SqlClient"
  runat="server"></asp:SqlDataSource>

Programmatic solution

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):

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):

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:

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:

CopyASPX
 <form id="form1" runat="server">
 <telerik:RadGrid ShowFooter="true" ID="RadGrid1" runat="server" DataSourceID="SqlDataSource1"
   GridLines="None" OnPreRender="RadGrid1_PreRender" OnItemDataBound="RadGrid1_ItemDataBound">
   <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" DataSourceID="SqlDataSource1">
     <Columns>
       <telerik:GridBoundColumn DataField="OrderID" DataType="System.Int32" HeaderText="OrderID"
         ReadOnly="True" SortExpression="OrderID" UniqueName="OrderID">
       </telerik:GridBoundColumn>
       <telerik: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>
       </telerik:GridTemplateColumn>
     </Columns>
   </MasterTableView>
 </telerik:RadGrid>
<asp:SqlDataSource ID="SqlDataSource1" SelectCommand="SELECT TOP 50 * FROM Orders"
   ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" ProviderName="System.Data.SqlClient"
   runat="server">
 </asp:SqlDataSource>
 </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:

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.

CopyJavaScript
<script type="text/javascript">
  var initialValue = 0.0;
  function update(footerBox, changedBox)
  {
  var footerBoxControl = $get(footerBox);
  var changedBoxControl = $get(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>
<!--