RadGrid question about Grouping, Aggregate functions and Column Reorder

[Jules]

I'm facing an issue with RadGrid control. I have a dataset grouped by a "level" field, and RadGrid shows subtotals per level. RadGrid allows to reorder columns. The issue is that, when reordering a column, its subtotal is not reordered alongside the column data and header; so, if a text column is reordered between numeric columns, the subtotal of the previous column appears on the group footer for that column. Please refer to attachments 1 and 2, to see an example.

Thanks
Regards
Julio MX

[Princy]

Hi Julio,

Please try this,this is an example which i tried and it works fine at my end,if further issues persist please post your code.

ASPX:

<telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
           AllowSorting="true">
           <ClientSettings AllowColumnsReorder="true">
           </ClientSettings>
           <MasterTableView ShowFooter="true">
               <GroupByExpressions>
                   <telerik:GridGroupByExpression>
                       <SelectFields>
                           <telerik:GridGroupByField FieldName="OrderID" Aggregate="Count" />
                       </SelectFields>
                       <GroupByFields>
                           <telerik:GridGroupByField FieldName="OrderDate" SortOrder="Descending" />
                       </GroupByFields>
                   </telerik:GridGroupByExpression>
               </GroupByExpressions>
               <Columns>
                   <telerik:GridBoundColumn SortExpression="OrderID" HeaderText="OrderID" DataField="OrderID"
                       Aggregate="Count">
                   </telerik:GridBoundColumn>
                   <telerik:GridBoundColumn SortExpression="OrderDate" HeaderText="Date Ordered" DataField="OrderDate"
                       UniqueName="OrderDate" DataFormatString="{0:D}">
                   </telerik:GridBoundColumn>
                   <telerik:GridBoundColumn DataField="ShipPostalCode" HeaderText="ShipPostalCode" Aggregate="CountDistinct"
                       UniqueName="ShipPostalCode">
                   </telerik:GridBoundColumn>
                   <telerik:GridBoundColumn SortExpression="Freight" HeaderText="Freight" DataField="Freight"
                       UniqueName="Freight" Aggregate="Sum">
                   </telerik:GridBoundColumn>
               </Columns>
           </MasterTableView>
       </telerik:RadGrid>

Thanks
Princy

[Jules]

Hi Princy; I tried your code but unfortunately it didn't work. Here is the code of the RadGrid:

<telerik:RadGrid ID="ReportGrid" runat="server" AllowMultiRowSelection="False" EnableEmbeddedSkins="False"
    AutoGenerateColumns="False" AllowSorting="True" ShowFooter="True" GridLines="None"
    EnableLinqExpressions="False" OnItemDataBound="MyDownlineGridControlBase_ItemDataBound"
    OnColumnCreated="MyDownlineGridControlBase_ColumnCreated"
    OnNeedDataSource="MyDownlineGridControlBase_NeedDataSource"
    OnPreRender="MyDownlineGridControlBase_PreRender"
    OnInit="MyDownlineGridControlBase_Init"
    Width="100%" meta:resourcekey="ReportGrid"
    CssClass="ReportGrid SupVolGrid">
    <HeaderContextMenu EnableEmbeddedSkins="False" />
    <GroupingSettings RetainGroupFootersVisibility="true"/>
    <MasterTableView GroupLoadMode="Client" ShowGroupFooter="true" TableLayout="Fixed"
        ClientDataKeyNames="DistributorID,PrimaryEmail,SponsorID"
        Width="100%" AllowSorting="true"  CssClass="" GroupsDefaultExpanded="false">
        <Columns />
        <RowIndicatorColumn Visible="True" />
        <GroupByExpressions>
            <telerik:GridGroupByExpression>
                <GroupByFields>
                    <telerik:GridGroupByField FieldName="SPLevelMakeTop" />
                </GroupByFields>
                <SelectFields>
                    <telerik:GridGroupByField FieldName="SPLevelMakeTop" />
                </SelectFields>
            </telerik:GridGroupByExpression>
        </GroupByExpressions>
    </MasterTableView>
    <ClientSettings Scrolling-UseStaticHeaders="false"
        AllowColumnsReorder="true" ReorderColumnsOnClient="true"
        AllowExpandCollapse="true" ClientEvents-OnGridCreated="GridCreated">
        <Selecting AllowRowSelect="false" />
        <Resizing AllowRowResize="False" EnableRealTimeResize="True" ResizeGridOnColumnResize="True"
            AllowColumnResize="True" />
    </ClientSettings>
    <FilterMenu EnableEmbeddedSkins="False" />
</telerik:RadGrid>

Columns are dynamically created on DataBind, and the Aggregate Function is set on:

private void AddColumn(GridTableView tableView, GridColumnDefinition columnDef, string headerText)
{
    GridBoundColumn boundCol = new GridBoundColumn();
    tableView.Columns.Add(boundCol);
    boundCol.DataField = columnDef.DataField;
    boundCol.DataFormatString = columnDef.DataFormatString;
    boundCol.UniqueName = columnDef.UniqueName;
    boundCol.HeaderText = headerText;
    boundCol.DataType = columnDef.DataType;
    if (columnDef.Aggregate == GridColumnDefinition.SUM)
    {
        boundCol.FooterAggregateFormatString = "{0:F2}";
        boundCol.Aggregate = GridAggregateFunction.Sum;
    }
}

This method is called on RadGrid_NeedDataSource Event. So I wonder if the fact oo adding columns on run time instead of declaratively could affect group footers.

Thanks
Regards
Julio MX

[Princy]

Hi Julio,

Below is the entire code,where i added columns dynamically.This works fine for me. Please add the columns into Page_Init event.

ASPX:

   <telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false" >
           <GroupingSettings RetainGroupFootersVisibility="true" />
           <MasterTableView ShowFooter="true">
               <GroupByExpressions>
                   <telerik:GridGroupByExpression>
                       <GroupByFields>
                           <telerik:GridGroupByField FieldName="ContactName" />
                       </GroupByFields>
                       <SelectFields>
                           <telerik:GridGroupByField FieldName="CustomerID" />
                       </SelectFields>
                   </telerik:GridGroupByExpression>
               </GroupByExpressions>
           </MasterTableView>
           <ClientSettings Scrolling-UseStaticHeaders="false" AllowColumnsReorder="true" ReorderColumnsOnClient="true"
               AllowExpandCollapse="false">
               <Selecting AllowRowSelect="false" />
               <Resizing AllowRowResize="False" EnableRealTimeResize="True" ResizeGridOnColumnResize="True"
                   AllowColumnResize="True" />
           </ClientSettings>
       </telerik:RadGrid>

C#:

protected void Page_Init(object source, System.EventArgs e)
    {
          RadGrid1.DataSourceID = "SqlDataSource1";
        GridBoundColumn boundColumn;
        boundColumn = new GridBoundColumn();
        boundColumn.DataField = "CustomerID";
        boundColumn.HeaderText = "CustomerID";
        boundColumn.Aggregate = GridAggregateFunction.Count;
        RadGrid1.MasterTableView.Columns.Add(boundColumn);
        boundColumn = new GridBoundColumn();
        boundColumn.DataField = "ContactName";
        boundColumn.HeaderText = "Contact Name";
        boundColumn.FooterAggregateFormatString = "{0:F2}";
        boundColumn.Aggregate = GridAggregateFunction.First;
        RadGrid1.MasterTableView.Columns.Add(boundColumn);
    }

Thanks
Princy

[Jules]

Hi Princy:
Please review this sample project. The issue is that the totals by group are not reordered with its corresponding column. It consists on a simple radgrid:

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik"%>

<asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">

    <style type="text/css">

        .clientData
        {
            font-weight:bold;
        }

    </style>

    <telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false" >
           <GroupingSettings RetainGroupFootersVisibility="true" />
           <MasterTableView GroupLoadMode="Client" ShowGroupFooter="true" TableLayout="Fixed">
               <GroupByExpressions>
                   <telerik:GridGroupByExpression>
                       <GroupByFields>
                           <telerik:GridGroupByField FieldName="Country" />
                       </GroupByFields>
                       <SelectFields>
                           <telerik:GridGroupByField FieldName="Country" />
                       </SelectFields>
                   </telerik:GridGroupByExpression>
               </GroupByExpressions>
           </MasterTableView>
           <ClientSettings Scrolling-UseStaticHeaders="false"
               AllowColumnsReorder="true" ReorderColumnsOnClient="true" ColumnsReorderMethod="Reorder"
               AllowExpandCollapse="false">
               <Selecting AllowRowSelect="false" />
               <Resizing AllowRowResize="False" EnableRealTimeResize="True" ResizeGridOnColumnResize="True"
                   AllowColumnResize="True" />
           </ClientSettings>
       </telerik:RadGrid>

</asp:Content>

And test data:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Telerik.Web.UI;

public partial class _Default : Page
{
    DataTable data;

    protected void Page_Init(object sender, EventArgs e)
    {
        createData();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        setGridColumns();
        RadGrid1.DataSource = data;
        RadGrid1.DataBind();
    }

    private void createData()
    {
        setDataColumns();
        setData();
    }

    private void setDataColumns()
    {
        data = new DataTable();
        data.Columns.Add("Country", typeof(string));
        data.Columns.Add("ClientID", typeof(string));
        data.Columns.Add("Income", typeof(double));
        data.Columns.Add("Cost", typeof(double));
        data.Columns.Add("Profit", typeof(double));
    }

    private void setData()
    {
        data.Rows.Add("US", "A001", 3222, 213, 3009);
        data.Rows.Add("US", "A002", 3766, 207, 3559);
        data.Rows.Add("US", "A003", 4257, 223, 4034);
        data.Rows.Add("US", "A004", 2104, 242, 1862);
        data.Rows.Add("MX", "A005", 3056, 248, 2808);
        data.Rows.Add("MX", "A006", 4956, 270, 4686);
        data.Rows.Add("MX", "A007", 3642, 259, 3383);
        data.Rows.Add("MX", "A008", 2838, 213, 2625);
        data.Rows.Add("MX", "A009", 2401, 256, 2145);
        data.Rows.Add("MX", "A010", 2246, 283, 1963);
        data.Rows.Add("MX", "A011", 3045, 268, 2777);
        data.Rows.Add("MX", "A012", 3952, 224, 3728);
        data.Rows.Add("MX", "A013", 3772, 219, 3553);
        data.Rows.Add("CA", "A014", 2255, 249, 2006);
        data.Rows.Add("CA", "A015", 2796, 235, 2561);
        data.Rows.Add("CA", "A016", 3356, 270, 3086);
        data.Rows.Add("CA", "A017", 3567, 218, 3349);
        data.Rows.Add("CA", "A018", 4454, 222, 4232);
        data.Rows.Add("CA", "A019", 4725, 294, 4431);
        data.Rows.Add("CA", "A020", 2747, 248, 2499);
        data.Rows.Add("CA", "A021", 2820, 271, 2549);
        data.Rows.Add("BR", "A022", 2271, 271, 2000);
        data.Rows.Add("BR", "A023", 4707, 245, 4462);
        data.Rows.Add("BR", "A024", 3482, 273, 3209);
        data.Rows.Add("BR", "A025", 4713, 239, 4474);
        data.Rows.Add("BR", "A026", 2256, 292, 1964);
        data.Rows.Add("BR", "A027", 2514, 283, 2231);
        data.Rows.Add("BR", "A028", 2799, 225, 2574);
        data.Rows.Add("BR", "A029", 4360, 295, 4065);
        data.Rows.Add("BR", "A030", 4072, 274, 3798);
        data.Rows.Add("BR", "A031", 2866, 268, 2598);
        data.Rows.Add("BR", "A032", 4243, 202, 4041);
    }

    private void setGridColumns()
    {
        foreach (DataColumn column in data.Columns)
        {
            createGridColumn(column);
        }
    }

    private void createGridColumn(DataColumn column)
    {
        GridBoundColumn gColumn = new GridBoundColumn();
        gColumn.DataField = column.ColumnName;
        gColumn.DataType = column.DataType;
        gColumn.HeaderText = column.ColumnName;
        if (column.DataType == typeof(double))
        {
            gColumn.DataFormatString = "{0:F2}";
            gColumn.FooterAggregateFormatString = "{0:F2}";
            gColumn.Aggregate = GridAggregateFunction.Sum;
        }
        else
        {
            gColumn.ItemStyle.CssClass = "clientData";
        }
        RadGrid1.MasterTableView.Columns.Add(gColumn);
    }
}

You can create a ASP.NET Web forms project, add the Telerik.Web.UI reference, and paste this code on Default.aspx.

So, when you pick a numeric column and place it between alphanumeric columns, the group subtotals are not updated; if you reorder numeric columns, the corresponding group subtotals are not reordered.

Thanks, 
Regards
Julio MX

[Princy]

Hi,

Please take a look into the following code snippet I tried which worked as expected.

ASPX:

<telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false">
    <MasterTableView ShowGroupFooter="true" TableLayout="Auto">
        <GroupByExpressions>
            <telerik:GridGroupByExpression>
                <GroupByFields>
                    <telerik:GridGroupByField FieldName="Country" />
                </GroupByFields>
                <SelectFields>
                    <telerik:GridGroupByField FieldName="Country" />
                </SelectFields>
            </telerik:GridGroupByExpression>
        </GroupByExpressions>
    </MasterTableView>
    <ClientSettings Scrolling-UseStaticHeaders="false" AllowColumnsReorder="true" ColumnsReorderMethod="Reorder"
        AllowExpandCollapse="false">
        <Selecting AllowRowSelect="false" />
        <Resizing AllowRowResize="False" EnableRealTimeResize="True" ResizeGridOnColumnResize="True"
            AllowColumnResize="True" />
    </ClientSettings>
</telerik:RadGrid>

C#:

DataTable data;
   protected void Page_Load(object sender, EventArgs e)
   {
       createData();
       if (!IsPostBack)
       {
           RadGrid1.DataSource = data;
           setGridColumns();
       }
   }
   private void createData()
   {
       setDataColumns();
       setData();
   }
   private void setDataColumns()
   {
       data = new DataTable();
       data.Columns.Add("Country", typeof(string));
       data.Columns.Add("ClientID", typeof(string));
       data.Columns.Add("Income", typeof(double));
       data.Columns.Add("Cost", typeof(double));
       data.Columns.Add("Profit", typeof(double));
   }
   private void setData()
   {
       data.Rows.Add("US", "A001", 3222, 213, 3009);
       data.Rows.Add("US", "A002", 3766, 207, 3559);
       data.Rows.Add("US", "A003", 4257, 223, 4034);
       data.Rows.Add("US", "A004", 2104, 242, 1862);
       data.Rows.Add("MX", "A005", 3056, 248, 2808);
       data.Rows.Add("MX", "A006", 4956, 270, 4686);
       data.Rows.Add("MX", "A007", 3642, 259, 3383);
       data.Rows.Add("MX", "A008", 2838, 213, 2625);
       data.Rows.Add("MX", "A009", 2401, 256, 2145);
       data.Rows.Add("MX", "A010", 2246, 283, 1963);
       data.Rows.Add("MX", "A011", 3045, 268, 2777);
       data.Rows.Add("MX", "A012", 3952, 224, 3728);
       data.Rows.Add("MX", "A013", 3772, 219, 3553);
       data.Rows.Add("CA", "A014", 2255, 249, 2006);
       data.Rows.Add("CA", "A015", 2796, 235, 2561);
       data.Rows.Add("CA", "A016", 3356, 270, 3086);
       data.Rows.Add("CA", "A017", 3567, 218, 3349);
       data.Rows.Add("CA", "A018", 4454, 222, 4232);
       data.Rows.Add("CA", "A019", 4725, 294, 4431);
       data.Rows.Add("CA", "A020", 2747, 248, 2499);
       data.Rows.Add("CA", "A021", 2820, 271, 2549);
       data.Rows.Add("BR", "A022", 2271, 271, 2000);
       data.Rows.Add("BR", "A023", 4707, 245, 4462);
       data.Rows.Add("BR", "A024", 3482, 273, 3209);
       data.Rows.Add("BR", "A025", 4713, 239, 4474);
       data.Rows.Add("BR", "A026", 2256, 292, 1964);
       data.Rows.Add("BR", "A027", 2514, 283, 2231);
       data.Rows.Add("BR", "A028", 2799, 225, 2574);
       data.Rows.Add("BR", "A029", 4360, 295, 4065);
       data.Rows.Add("BR", "A030", 4072, 274, 3798);
       data.Rows.Add("BR", "A031", 2866, 268, 2598);
       data.Rows.Add("BR", "A032", 4243, 202, 4041);
   }
   private void setGridColumns()
   {
       GridBoundColumn boundColumn;
       boundColumn = new GridBoundColumn();
       RadGrid1.MasterTableView.Columns.Add(boundColumn);
       boundColumn.DataField = "Country";
       boundColumn.HeaderText = "Country";
       boundColumn.Aggregate = GridAggregateFunction.Count;
       boundColumn = new GridBoundColumn();
       RadGrid1.MasterTableView.Columns.Add(boundColumn);
       boundColumn.DataField = "ClientID";
       boundColumn.HeaderText = "ClientID";
       boundColumn.Aggregate = GridAggregateFunction.Count;
       boundColumn = new GridBoundColumn();
       RadGrid1.MasterTableView.Columns.Add(boundColumn);
       boundColumn.DataField = "Income";
       boundColumn.HeaderText = "Income";
       boundColumn.Aggregate = GridAggregateFunction.Sum;
       boundColumn = new GridBoundColumn();
       RadGrid1.MasterTableView.Columns.Add(boundColumn);
       boundColumn.DataField = "Cost";
       boundColumn.HeaderText = "Cost";
       boundColumn.Aggregate = GridAggregateFunction.Sum;
       boundColumn = new GridBoundColumn();
       RadGrid1.MasterTableView.Columns.Add(boundColumn);
       boundColumn.DataField = "Profit";
       boundColumn.HeaderText = "Profit";
       boundColumn.Aggregate = GridAggregateFunction.Sum;
   }

Please remove the ClientSetting ReorderColumnsOnClient.

Thanks,
Princy.

[Jules]

Hi Princy:
I tried an approach with Javascript, and appears to work:

On ClientEvents-OnColumnSwapped="GridColumnSwapped"

function GridColumnSwapped(sender, eventArgs) {

    var sourceColumn = eventArgs.get_gridSourceColumn();
    var targetColumn = eventArgs.get_gridTargetColumn();

    var sourceHeader = sourceColumn.get_element();
    var targetHeader = targetColumn.get_element();

    var sourceIndex = sourceHeader.cellIndex;
    var targetIndex = targetHeader.cellIndex;

    var subTotalRows = $("tr.rgFooter");
    subTotalRows.each(function () {

        var sourceCell = this.cells[sourceIndex];
        var targetCell = this.cells[targetIndex];

        var temp = targetCell.innerHTML;
        targetCell.innerHTML = sourceCell.innerHTML;
        sourceCell.innerHTML = temp;
    });
}

Thanks for your help.
Regards,
Julio MX