[Solved] RadGrid Grouping Error - Calculated Column

[Scott]

I have a RadGrid with some bound columns and calculated columns. There is a calculated column for each month in a year which calculates a value multiplied by a UnitPrice. I am getting an error when I try to group by any of the columns, even the bound columns. For example, I have a column called "July" and when I try to group by any of the columns I get an error saying "Column 'JulyResult' does not belong to table." There is no column in my DataTable called 'JulyResult', I assume this column is created because it's a calculated column. The same error happens for all the other calculated columns. I am using the NeedDataSource event to set the RadGrid's datasource to a DataTable. I am using Telerik RadControls for ASP.NET Ajax Q1 2011 SP2. Any ideas why I am getting this error? Thanks.

[Princy]

Hi,

Here is the sample code snippet I tried with Grouping of Calculated Column.

ASPX:

<telerik:RadGrid AutoGenerateColumns="false" ID="RadGrid1" DataSourceID="SqlDataSource1"
    AllowFilteringByColumn="True" EnableLinqExpressions="false" ShowGroupPanel="true"
    GroupingEnabled="true" ShowFooter="True" runat="server" GridLines="None" AllowPaging="true">
    <PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
    <MasterTableView>
        <Columns>
            <telerik:GridBoundColumn Aggregate="Count" DataField="ProductID" HeaderText="Product ID"
                FooterText="Total products: ">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="ProductName" HeaderText="Product name" SortExpression="ProductName"
                UniqueName="ProductName">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn Aggregate="Sum" DataField="UnitPrice" HeaderText="Unit price"
                FooterText="Total price: ">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn Aggregate="Sum" DataField="UnitsInStock" HeaderText="Units in stock"
                FooterText="All units in stock: ">
            </telerik:GridBoundColumn>
            <telerik:GridCalculatedColumn HeaderText="Total Price" UniqueName="TotalPrice" DataType="System.Double"
                DataFields="UnitPrice, UnitsInStock" Expression="{0}*{1}" FooterText="Total : "
                Aggregate="Sum">
            </telerik:GridCalculatedColumn>
        </Columns>
    </MasterTableView>
    <ClientSettings EnableRowHoverStyle="true" AllowDragToGroup="true">
    </ClientSettings>
</telerik:RadGrid>

Please provide the code if it doesn't help.

Thanks,
Princy.

[Scott]

Princy,

I have tried your code suggestion, however I am now getting a javasript error in Visual Studio when I try to group by a column. Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerServerErrorException: Exception has been thrown by the target of an invocation. It might be worth noting that I am using Linq-to-Sql to query a SQL database and storing the results in a DataTable which becomes the DataSource for my RadGrid. I use the NeedDataSource event to query the data and set the datasource.

Here is my RadGrid code:

<telerik:RadGrid ID="MainGrid" runat="server" Width="100%" Height="498px"

                    AllowSorting="True" AutoGenerateColumns="False"

                    CellSpacing="0" ShowFooter="True" GridLines="None"

                    onneeddatasource="RadGrid1_NeedDataSource" Skin="WebBlue" PageSize="20"

                    ShowGroupPanel="True" EnableHeaderContextMenu="True"

                    onprerender="RadGrid1_PreRender" ongroupschanging="RadGrid1_GroupsChanging"

                    ViewStateMode="Enabled" onsortcommand="RadGrid1_SortCommand">

                    <GroupingSettings ShowUnGroupButton="True" />

                    <ExportSettings FileName="SUDRejectGridExport" HideStructureColumns="True">

                    </ExportSettings>

                    <ClientSettings AllowDragToGroup="True">

                        <Scrolling AllowScroll="True" UseStaticHeaders="True" />

                    </ClientSettings>

<MasterTableView CommandItemDisplay="Top"

                        EnableHeaderContextAggregatesMenu="True"

                        ShowGroupFooter="True" UseAllDataFields="True" GroupLoadMode="Client">

<CommandItemSettings ExportToPdfText="Export to PDF" ShowAddNewRecordButton="False"

        ShowExportToCsvButton="True" ShowExportToExcelButton="True"

        ShowExportToPdfButton="True" ShowExportToWordButton="True"></CommandItemSettings>

 

<RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">

<HeaderStyle Width="20px"></HeaderStyle>

</RowIndicatorColumn>

 

<ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">

<HeaderStyle Width="20px"></HeaderStyle>

</ExpandCollapseColumn>

 

    <Columns>

        <telerik:GridBoundColumn DataField="Machine" Display="True" 

            FilterControlAltText="Filter Machine column" HeaderText="Machine"

            UniqueName="Machine" HeaderStyle-Width="150px">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn DataField="Driver"

            FilterControlAltText="Filter Driver column" HeaderText="Driver"

            UniqueName="Driver" HeaderStyle-Width="100px">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn DataField="Component"

            FilterControlAltText="Filter Component column" HeaderText="Component"

            UniqueName="Component" HeaderStyle-Width="100px">

        </telerik:GridBoundColumn>

        <telerik:GridCalculatedColumn DataType="System.Double" HeaderText="J" UniqueName="July"

            DataFields="July,Q1UnitCost" Expression="{0}*{1}" DataFormatString="{0:C}" 

            Groupable="True" HeaderButtonType="LinkButton" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="August,Q1UnitCost" Expression="{0}*{1}" HeaderText="A" UniqueName="August" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataFields="September,Q1UnitCost" Expression="{0}*{1}" DataType="System.Double"

            HeaderText="S" UniqueName="September" DataFormatString="{0:C}" Aggregate="Sum" >

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="October,Q2UnitCost" Expression="{0}*{1}" HeaderText="O"

            UniqueName="October" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="November,Q2UnitCost" Expression="{0}*{1}" HeaderText="N"

            UniqueName="November" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="December,Q2UnitCost" Expression="{0}*{1}" HeaderText="D"

            UniqueName="December" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="January,Q3UnitCost" Expression="{0}*{1}" HeaderText="J"

            UniqueName="January" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="February,Q3UnitCost" Expression="{0}*{1}" HeaderText="F"

            UniqueName="February" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="March,Q3UnitCost" Expression="{0}*{1}" HeaderText="M"

            UniqueName="March" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="April,Q4UnitCost" Expression="{0}*{1}" HeaderText="A"

            UniqueName="April" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="May,Q4UnitCost" Expression="{0}*{1}" HeaderText="M"

            UniqueName="May" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="June,Q4UnitCost" Expression="{0}*{1}" HeaderText="J"

            UniqueName="June" DataFormatString="{0:C}" Aggregate="Sum">

        </telerik:GridCalculatedColumn>

        <telerik:GridCalculatedColumn DataType="System.Double"

            DataFields="January,February,March,April,May,June,July,August,September,October,November,December,Q1UnitCost,Q2UnitCost,Q3UnitCost,Q4UnitCost"

            Expression="(({0}+{1}+{2})*{14})+(({3}+{4}+{5})*{15})+(({6}+{7}+{8})*{12})+(({9}+{10}+{11})*{13})"

            HeaderText="Total" UniqueName="Total" DataFormatString="{0:C}" >

        </telerik:GridCalculatedColumn>

    </Columns>

 

<EditFormSettings>

<EditColumn FilterControlAltText="Filter EditCommandColumn column"></EditColumn>

</EditFormSettings>

</MasterTableView>

 

<FilterMenu EnableImageSprites="False"></FilterMenu>

 

<HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default"></HeaderContextMenu>

                </telerik:RadGrid>


[Princy]

Hi Scott,

I could replicate the error "Column 'JulyResult' does not belong to table."  when i tried your code snippet. Please try setting the MasterTableView property UseAllDataFields to false.

Thanks,
Princy.

[Scott]

Princy,

I have tried your suggestion of setting the MasterTableView's UseAllDataFields property to false, however I am still getting the same error. I am also getting this error when I try to sort by one of the month columns. I noticed the SortExpression in the SortCommand event is JulyResult, and it does not seem to change even when I explicitly set the SortExpression for the column. Maybe there is a way I can add my calculation expression to the SortExpression or GroupByExpression to prevent the table from looking for "JulyResult"?

Thanks,
Scott

[Princy]

Hi,

Unfortunately I couldn't replicate the issue. Sorting is working fine in my end. Can you provide the code you wrote inside SortCommand event.

Thanks,
Princy.

[Scott]

Princy,

I have removed my code in the SortCommand event, since I was just trying to change the SortCommand. But I was attempting to set "e.SortExpression" to "July." But really since the July column is calculated, I want it to sort based on the actual value, "{0} * {1}." However, I am not sure if that syntax is allowed in the SortExpression. The only other code I have for the grid is the NeedDataSource event, where I refresh the DataTable's data and set the Grid's DataSource to the DataTable.DefaultView property. When I refresh the DataTable, I use Linq to re-query the SQL tables and re-add the rows to the DataTable. I guess I might have to resort to performing my own sorting on the underlying DataTable in the SortCommand event and grouping in the GroupsChanging event?

Thanks,
Scott