Radgrid group aggregates fail when auto-generating columns. Exception: Column '[columnName]' does not belong to table .

[Zach]

I have a radgrid building columns dynamically using the autogenerate feature. I am hooked into the columnCreated event to set some column properties including Aggregate = GridAggregateFunction.Sum. If I do not set an aggregate function, the grid builds functions appropriately. If I set the aggregate function, I receive a stack trace listed below.

I see the exception is happening in System.Data.DataRow.GetColumn() however I have confirmed in the dataTable that the column name exists. Sample table output from the query is below as well as my markup and relevant parts of my code-behind.

Exception Details: System.ArgumentException: Column 'Q1.2023' does not belong to table .
...

[ArgumentException: Column 'Q1.2023' does not belong to table .]
   System.Data.DataRow.GetDataColumn(String columnName) +3129649
   Telerik.Web.UI.GridDataSetHelper.CalculateAggregates(DataRow SourceRow, DataRow DestRow, Int32 rowCountInGroup, GridTableView view, Boolean& changeRowCount) +1190
   Telerik.Web.UI.GridDataSetHelper.InsertGroupByInto(GridTableView gridTableView, DataTable DestTable, DataTable SourceTable, String RowFilter, Int32 FirstIndexInPage, Int32 LastIndexInPage, Boolean ApplyPaging, Boolean IsCustomPaging) +1432
   Telerik.Web.UI.GridEnumerableFromDataView.PerformTransformation() +3451
...

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.9181.0

 

Sample data row

Team		DoS     Broker		BrokerID	UserType	Live	Active	PeriodID	Q1.2023		Q2.2023		Q3.2023		Q4.2023
TeamName	DosName	BrokerName	120614		100001		Y	Y	Goal		262610.00	342715.00	139790.00	146724.50

 

Markup

<telerik:RadGrid ID="rgBrokerGoals" runat="server" RenderMode="Lightweight" AutoGenerateColumns="false" Skin="Metro" AllowSorting="false" ShowGroupPanel="false" ShowFooter="true" EnableViewState="True" >
	<HeaderStyle Font-Bold="true" />
        <ClientSettings ReorderColumnsOnClient="True" AllowDragToGroup="True" AllowColumnsReorder="True">
        <Selecting AllowRowSelect="True" />
        <Resizing AllowRowResize="True" AllowColumnResize="True" EnableRealTimeResize="True" ResizeGridOnColumnResize="False" />
        <ClientEvents OnRowDblClick="OnRowDblClick_EditBrokerGoal" />
        <Scrolling UseStaticHeaders="true" AllowScroll="true" ScrollHeight="800px" />
    </ClientSettings>
    <GroupingSettings RetainGroupFootersVisibility="true" />
    <ExportSettings FileName="BrokerGoals">
        <Excel Format="Xlsx" AutoFitColumnWidth="AutoFitAll" />
    </ExportSettings>
    <MasterTableView AutoGenerateColumns="true" DataKeyNames="BrokerID" ClientDataKeyNames="BrokerID" Font-Size="9" TableLayout="Auto" CommandItemDisplay="Top" AllowMultiColumnSorting="true" GroupLoadMode="Server"
        RetainExpandStateOnRebind="true" NoMasterRecordsText="Select a year from the dropdown." ShowGroupFooter="true" EnableViewState="true" >
        <CommandItemSettings ShowExportToExcelButton="false" ShowRefreshButton="true" ShowAddNewRecordButton="false" />
        <CommandItemTemplate>
            <div style="display:inline;">
                <telerik:RadSwitch ID="swRollupTerminatedBrokers" runat="server" Skin="Metro" CommandName="ShowHideTerminatedBrokers" Width="180px" AutoPostback="true" BorderStyle="none" >
                    <ToggleStates>
                        <ToggleStateOff Text="Rollup Separated Brokers" />
                        <ToggleStateOn Text="Show Separated Brokers" />
                    </ToggleStates>   
                </telerik:RadSwitch>
            </div>
            <div style="float:right; display:inline;">
                <telerik:RadButton runat="server" ID="RefreshButton" OnClientClicked="rebindGrid" AutoPostBack="false" Text="Refresh" ToolTip="Refresh" Skin="Metro" BorderStyle="none" >
                    <Icon PrimaryIconCssClass="rgRefreshIcon" />
                </telerik:RadButton>
            </div>
        </CommandItemTemplate>

        <GroupByExpressions>
            <telerik:GridGroupByExpression>
                <GroupByFields>
                    <telerik:GridGroupByField FieldName="Team" />
                </GroupByFields>
                <SelectFields>
                    <telerik:GridGroupByField FieldName="Team" HeaderText="Team" FieldAlias="Team" />
                </SelectFields>
            </telerik:GridGroupByExpression>
            <telerik:GridGroupByExpression>
                <GroupByFields>
                    <telerik:GridGroupByField FieldName="DoS" />
                </GroupByFields>
                <SelectFields>
                    <telerik:GridGroupByField FieldName="DoS"   HeaderText="SM" FieldAlias="SM" />
                </SelectFields>
            </telerik:GridGroupByExpression>
        </GroupByExpressions>
        <Columns>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

 

Relevant Code-behind

Note that some event handlers have been omitted as the code does not reach them. The exception is thrown, as far as I can tell, after all of the columnCreated events have fired and but before reaching ItemCreated and ItemDataBound . Changing Aggregate function to Custom and setting a break on the OnCustomAggregate handler does not produce any sort of result either.

Public Class BrokerGoals
    Inherits Page

    Private Property RollupTerminatedBrokersChecked As Boolean = True

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Trim(HttpContext.Current.Session("UserID") & "") = "" Then Response.Redirect("../index.aspx")
        If IsPostBack Then
            rgBrokerGoals.Rebind()
        Else
            ddlPeriodStart.DataBind()
            ddlPeriodStart.SelectedText = $"{Date.Today.Year}.Q1"
            ddlPeriodEnd.DataBind()
            ddlPeriodEnd.SelectedText = $"{Date.Today.Year}.Q4"
        End If

        btnAddGoal.Attributes.Add("onclick", "NewGoal();return false;")
        btnAddGoal.NavigateUrl = "javascript:NewGoal();return false;"
        btnAddGoal.Visible = Functions.CheckFieldSecurity(251, HttpContext.Current.Session("UserID"))

    End Sub

Protected Sub rgBrokerGoals_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles rgBrokerGoals.NeedDataSource
        Try
            Dim commandItems = rgBrokerGoals.MasterTableView.GetItems(GridItemType.CommandItem)
            If commandItems.Count > 0 Then
                Dim rollupBrokersSwitch = CType(rgBrokerGoals.MasterTableView.GetItems(GridItemType.CommandItem)?(0)?.FindControl("swRollupTerminatedBrokers"), RadSwitch)
                RollupTerminatedBrokersChecked = rollupBrokersSwitch.Checked
            End If
            Dim dt As New DataTable
            Using db = Functions.GetSqlConnection()
                Using cmd As New SqlCommand("JN_BrokerGoals_ByPeriod", db)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add("@PeriodStart", SqlDbType.Date).Value = SetDateValue(ddlPeriodStart.SelectedValue)
                    cmd.Parameters.Add("@PeriodEnd", SqlDbType.Date).Value = SetDateValue(ddlPeriodEnd.SelectedValue)
                    cmd.Parameters.Add("@RollupTerminatedBrokers", SqlDbType.Bit).Value = RollupTerminatedBrokersChecked
                    Using da As New SqlDataAdapter(cmd)
                        da.Fill(dt)
                    End Using
                End Using
            End Using

            CType(sender, RadGrid).DataSource = dt
        Catch ex As Exception
            'Error Handling
        End Try
    End Sub

    Private Sub rgBrokerGoals_ColumnCreated(sender As Object, e As GridColumnCreatedEventArgs) Handles rgBrokerGoals.ColumnCreated
        If e.Column.UniqueName.StartsWith("Q") Then
            Dim column = CType(e.Column, GridNumericColumn)
            column.Aggregate = GridAggregateFunction.Sum
            column.NumericType = NumericType.Currency
            column.HeaderText = column.UniqueName
            column.DataFormatString = "{0:C}"

        ElseIf {"PeriodID", "Active", "Live", "UserType", "BrokerID", "DoS", "Team"}.Contains(e.Column.UniqueName) Then
            e.Column.Display = False
        End If
    End Sub

    Protected Sub rgBrokerGoals_ItemCreated(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles rgBrokerGoals.ItemCreated
        If TypeOf e.Item Is GridGroupHeaderItem Then
            AddHandler e.Item.PreRender, AddressOf HeaderItem_PreRender
        ElseIf TypeOf e.Item Is GridGroupFooterItem Then
            AddHandler e.Item.PreRender, AddressOf FooterItem_PreRender
        End If
    End Sub

    Private Sub rgBrokerGoals_ItemDataBound(sender As Object, e As GridItemEventArgs) Handles rgBrokerGoals.ItemDataBound
        If TypeOf e.Item Is GridDataItem Then
            Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
            Dim brokerID As Long = dataItem.GetDataKeyValue("BrokerID")
            Dim varUserType As Integer? = JearParse.SetIntValue(dataItem("UserType").Text)

            Dim BrokerIsLive As Boolean = JearParse.SetStringValue(dataItem("Live").Text) = "Y"
            Dim BrokerIsInSales = Function(UserType As Long?) As Boolean
                                      Return {100001, 100002, 100009}.Contains(If(UserType, -1))
                                  End Function


            ' If user is not active/in sales, put asterisk next to their name.
            If (BrokerIsLive AndAlso Not BrokerIsInSales(varUserType)) OrElse Not BrokerIsLive Then
                dataItem("Broker").Text &= "*"
                For Each cell As TableCell In dataItem.Cells
                    cell.ForeColor = Drawing.Color.Red
                Next
            End If

            dataItem.Enabled = IsNumeric(brokerID) andAlso brokerID <> -1
        End If
    End Sub
End Class

[Vasko]

Hi Zach,

Thank you for the provided code snipepts.

Having auto-generated columns in RadGrid and using aggregate functions is a specific scenario, for which you need to set the UseAllDataFields attribute of the Grid's MasterTableView to true. Could you give this a try to see if it will help you resolve the issue?

For more information regarding the topic, I suggest taking a look at the following thread.

Kind regards,
Vasko
Progress Telerik

[Zach]

This may well resolve the issue. Unfortunately, I had already begun implementing a work around and building the columns server-side instead. If at all possible, the information above should really be included on the AutoGenerateColumns section of the online documentation as well as with the Column Aggregates section.

[Vasko]

Hello Zach,

Thank you for your feedback. This information should indeed be mentioned in the docs.

I've updated the Working with Autogenerated Columns article and it now mentions the property when using Groups and Column Aggregates.

Kind regards,
Vasko
Progress Telerik