[Solved] telerik:GridCalculatedColumn problem

[John]

Hello all,

I am having trouble with the calculated column. everything works well until the calculated column is added then I get error message "Identifier expected".

Have gone through the documentations and examples but cannot figure out what's wrong and why it's not working.
Here is the code below. Any insight would be appreciated. Thanks!



 <telerik:RadGrid

     ID="ReportGrid"

     runat="server"

     DataSourceID="SearchResultDataSource"

 

         >

          

          

         <MasterTableView

         DataSourceID="SearchResultDataSource"

         AllowPaging="false"

 

         >

              

              

             <Columns>

                 

                                

                 <telerik:GridBoundColumn

                 DataField="name_last_first"

                 HeaderText="Name"

                  SortExpression="name_last_first"

                   UniqueName="name_last_first"

                                     

                     Aggregate="CountDistinct"

                      FooterAggregateFormatString="<b>{0:d}</b>"

 

                      />

 

                  

                 <telerik:GridNumericColumn

                 DataField="07" HeaderText="July"

                  SortExpression="07" UniqueName="07"

                   

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                   />

 

 

 

                 <telerik:GridNumericColumn

                 DataField="08"

                 HeaderText="Aug" SortExpression="08"

                 UniqueName="08"                  

                

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                 />

 

 

                 <telerik:GridNumericColumn DataField="09" HeaderText="Sept" SortExpression="09" UniqueName="09"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

 

 

                 <telerik:GridNumericColumn DataField="10" HeaderText="Oct" SortExpression="10" UniqueName="10"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

 

 

                 <telerik:GridNumericColumn DataField="11" HeaderText="Nov" SortExpression="11" UniqueName="11"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

 

 

                 <telerik:GridNumericColumn DataField="12" HeaderText="Dec" SortExpression="12" UniqueName="12"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

                 <telerik:GridNumericColumn DataField="01" HeaderText="Jan" SortExpression="01" UniqueName="01"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

                 <telerik:GridNumericColumn DataField="02" HeaderText="Feb" SortExpression="02" UniqueName="02"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

 

 

                 <telerik:GridNumericColumn DataField="03" HeaderText="Mar" SortExpression="03" UniqueName="03"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

                

 

                 <telerik:GridNumericColumn DataField="04" HeaderText="April" SortExpression="04" UniqueName="04"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

 

                 <telerik:GridNumericColumn DataField="05" HeaderText="May" SortExpression="05" UniqueName="05"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

                 <telerik:GridNumericColumn

                 DataField="06"

                 HeaderText="June"

                  SortExpression="06"

                  UniqueName="06"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

/>

 

 

 

          <telerik:GridCalculatedColumn

          HeaderText="Opened"

          UniqueName="Opened"           

          Aggregate="Sum" 

          FooterAggregateFormatString="<b>{0:d}</b>"

          DataFormatString="{0:d}"

          DataType="System.Int32"

          DataFields="01,02,03,04,05,06,07,08,09,10,11,12"

          Expression="{0}+{1}+{2}+{3}+{4}+{5}+{6}+{7}+{8}+{9}+{10}+{11}"                     

          />

    

 

             </Columns>

 

 

              <GroupByExpressions>

                  

                 <telerik:GridGroupByExpression>

                     <GroupByFields>

                        <telerik:GridGroupByField FieldName="unit_type" HeaderText=" " SortOrder="Ascending"  />

                     </GroupByFields>

                     

                     <SelectFields>

                         <telerik:GridGroupByField FieldName="unit_type" HeaderText=" " HeaderValueSeparator=" "  SortOrder="Ascending"  />

                     </SelectFields>

                 </telerik:GridGroupByExpression>

 

 

 

 

                 <telerik:GridGroupByExpression>

                     <GroupByFields>

                         <telerik:GridGroupByField FieldName="unit_name" HeaderText=" " SortOrder="Ascending"  />

                     </GroupByFields>

                                             

                     <SelectFields>

                         <telerik:GridGroupByField FieldName="unit_name" HeaderText=" " HeaderValueSeparator=" "  SortOrder="Ascending"  />

                     </SelectFields>

                 </telerik:GridGroupByExpression>

 

 

             </GroupByExpressions>

 

 

 

 

         </MasterTableView>

 

     </telerik:RadGrid>

 

 

 <asp:SqlDataSource ID="SearchResultDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

     SelectCommand="rpt_getOpenAndAssignedByMonthForUnit" EnableCaching="False" SelectCommandType="StoredProcedure">

 

     <SelectParameters>

    <asp:ControlParameter

    Name="int_fy"

    Type="Int32" 

    PropertyName="Text"

    ControlID="YearTxt"

    />

[John]

I set (EnableLinqExpressions="false" ) on the Grid and the error message has disappeared. Not sure why EnableLinqExpression would cause that error, or why it's even True by default rather than False.

But now I get very odd and unexpected results. The values for the calculated columns are all the same for every row, and the numbers are not adding up (not sure where it's getting those numbers from).

Below is a screenshot of the page and the code used to generate that page. Any help or insight would be appreciated.

Thank you.

     <telerik:RadGrid

     ID="ReportGrid"

     runat="server"

     DataSourceID="SearchResultDataSource"

         AllowSorting="True"

         BorderStyle="None"

         GridLines="None"

         ShowFooter="True"

         PageSize="1000"

         Width="100%"

         CellSpacing="2"

         CellPadding="2"

         ShowGroupPanel="False"

         EnableLinqExpressions="false"

         >

          

          

         <MasterTableView

         DataSourceID="SearchResultDataSource"

         AllowPaging="false"

 

 

 

             DataKeyNames="user_id, name_last_first"

 

             AdditionalDataFieldNames="07, 08, 09, 10, 11, 12, 01, 02, 03, 04, 05, 06, unit_type, unit_name"

             HierarchyLoadMode="ServerBind"

             HierarchyDefaultExpanded="True"

             UseAllDataFields="False"

 

 

 

         GroupsDefaultExpanded="true"

 

             AutoGenerateColumns="False"

             AllowFilteringByColumn="false"

 

             CanRetrieveAllData="false"

             TableLayout="Auto"

 

             ShowGroupFooter="true"

             GroupLoadMode="Server"

 

             EnableHeaderContextAggregatesMenu="True"

             EnableHeaderContextFilterMenu="True"

             EnableHeaderContextMenu="True"

             ShowHeadersWhenNoRecords="True"

 

             ShowFooter="True"

             Frame="Border"

             BorderStyle="None"

             GridLines="None"

             Width="100%"

 

             AllowSorting="True"

             ClientIDMode="AutoID"

             PageSize="1000"

 

 

             CommandItemDisplay="TopAndBottom"

 

 

 

              

             >

              

              

             <Columns>                  

                                

                 <telerik:GridBoundColumn

                 DataField="name_last_first"

                 HeaderText="Name"

                 SortExpression="name_last_first"

                 UniqueName="name_last_first"

                                     

                 Aggregate="CountDistinct"

                 FooterAggregateFormatString="Totals: <b>{0:d}</b>"

 

                      />

 

                  

                 <telerik:GridBoundColumn

 

                 DataField="07"

                 HeaderText="July"

                 SortExpression="07"

                 UniqueName="07"

                   

                 Aggregate="Sum"

                 FooterAggregateFormatString="<b>{0:d}</b>"

                   />

 

 

 

                 <telerik:GridBoundColumn

                 DataField="08"

                 HeaderText="Aug" SortExpression="08"

                 UniqueName="08"                  

                

                 Aggregate="Sum"

                 FooterAggregateFormatString="<b>{0:d}</b>"

                 />

 

 

 

 

                 <telerik:GridBoundColumn

                 DataField="09"

                 HeaderText="Sept"

                 SortExpression="09"

                 UniqueName="09"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

 

 

 

 

                 <telerik:GridBoundColumn

                 DataField="10"

                 HeaderText="Oct"

                 SortExpression="10"

                 UniqueName="10"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

 

 

                 <telerik:GridBoundColumn

                 DataField="11"

                 HeaderText="Nov"

                 SortExpression="11"

                 UniqueName="11"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

 

 

                 <telerik:GridBoundColumn

                 DataField="12"

                 HeaderText="Dec"

                 SortExpression="12"

                 UniqueName="12"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

               />

 

 

 

 

 

                 <telerik:GridBoundColumn

                 DataField="01"

                 HeaderText="Jan"

                 SortExpression="01"

                 UniqueName="01"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

 

 

 

 

                 <telerik:GridBoundColumn

                 DataField="02"

                 HeaderText="Feb"

                 SortExpression="02"

                 UniqueName="02"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

 

 

                 <telerik:GridBoundColumn

                 DataField="03"

                 HeaderText="Mar"

                 SortExpression="03"

                 UniqueName="03"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

                

 

                 <telerik:GridBoundColumn

                 DataField="04"

                 HeaderText="April"

                 SortExpression=

                 "04"

                 UniqueName="04"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

 

 

 

 

 

                 <telerik:GridBoundColumn

           

                 DataField="05"

                 HeaderText="May"

                 SortExpression="05"

                 UniqueName="05"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

                

               />

 

 

 

 

                

                 <telerik:GridBoundColumn

          

                 DataField="06"

                 HeaderText="June"

                  SortExpression="06"

                  UniqueName="06"

               Aggregate="Sum"

               FooterAggregateFormatString="<b>{0:d}</b>"

/>

 

 

 

 

          <telerik:GridCalculatedColumn

          DataType="System.Int32"

          HeaderText="Opened"

          UniqueName="Opened"

            

          Aggregate="Sum" 

          FooterAggregateFormatString="<b>{0:d}</b>"

          DataFormatString="{0:d}"

 

          DataFields="07, 08, 09, 10, 11, 12, 01, 02, 03, 04, 05, 06"

          Expression="{01}+{02}+{03}+{04}+{05}+{06}+{07}+{08}+{09}+{10}+{11}"               

          />

 

              <telerik:GridCalculatedColumn
             DataType="System.Int32"
             HeaderText="Average"
             UniqueName="Average"
              
             Aggregate="Avg"  
             FooterAggregateFormatString="<b>{0:d}</b>"
             DataFormatString="{0:d}"

             DataFields="07, 08, 09, 10, 11, 12, 01, 02, 03, 04, 05, 06"
             Expression="({01}+{02}+{03}+{04}+{05}+{06}+{07}+{08}+{09}+{10}+{11})/12"                
             />

 

 

 

             </Columns>

 

 

              <GroupByExpressions>

                  

                 <telerik:GridGroupByExpression>

                     <GroupByFields>

                        <telerik:GridGroupByField FieldName="unit_type" HeaderText=" " SortOrder="Ascending"  />

                     </GroupByFields>

                     

                     <SelectFields>

                         <telerik:GridGroupByField FieldName="unit_type" HeaderText=" " HeaderValueSeparator=" "  SortOrder="Ascending"  />

                     </SelectFields>

                 </telerik:GridGroupByExpression>

 

 

 

 

                 <telerik:GridGroupByExpression>

                     <GroupByFields>

                         <telerik:GridGroupByField FieldName="unit_name" HeaderText=" " SortOrder="Ascending"  />

                     </GroupByFields>

                                             

                     <SelectFields>

                         <telerik:GridGroupByField FieldName="unit_name" HeaderText=" " HeaderValueSeparator=" "  SortOrder="Ascending"  />

                     </SelectFields>

                 </telerik:GridGroupByExpression>

 

 

 

 

 

             </GroupByExpressions>

 

 

 

 

             

             

             <RowIndicatorColumn Visible="True" FilterControlAltText="Filter RowIndicator column"

                 Groupable="True" Reorderable="True" Resizable="True">

             </RowIndicatorColumn>

 

 

             <ExpandCollapseColumn Groupable="True" Reorderable="True" Resizable="True" Visible="True">

                 <HeaderStyle Font-Size="20px" ForeColor="Black" HorizontalAlign="Center"></HeaderStyle>

             </ExpandCollapseColumn>

 

 

         </MasterTableView>

 

 

         <HeaderStyle Font-Bold="True" Wrap="false" />

         <GroupHeaderItemStyle Font-Bold="True" Wrap="false" />

 

 

         <FooterStyle Font-Bold="True" Wrap="false" />

 

 

         <ClientSettings AllowDragToGroup="True" AllowGroupExpandCollapse="True" AllowColumnHide="True"

             AllowExpandCollapse="True" AllowRowHide="True" AllowRowsDragDrop="True" ReorderColumnsOnClient="True"

             AllowColumnsReorder="True">

 

             <Selecting AllowRowSelect="True" />

 

             <Scrolling AllowScroll="False" FrozenColumnsCount="2" UseStaticHeaders="True" SaveScrollPosition="True"

                 EnableVirtualScrollPaging="false" />

 

             <Resizing AllowColumnResize="True" AllowRowResize="True" ResizeGridOnColumnResize="True"

                 EnableRealTimeResize="True" ClipCellContentOnResize="False" />

 

         </ClientSettings>

 

         <FilterMenu EnableImageSprites="False">

         </FilterMenu>

 

 

 

 

         <GroupingSettings ShowUnGroupButton="True" RetainGroupFootersVisibility="true" GroupByFieldsSeparator=" " />

 

  

     </telerik:RadGrid>

 

 

 

 

 

 

 

 

 

 

 

 

 <asp:SqlDataSource ID="SearchResultDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

     SelectCommand="rpt_getOpenAndAssignedByMonthForUnit" EnableCaching="False" SelectCommandType="StoredProcedure">

 

       </asp:SqlDataSource>

[John]

Solved the problem. It was taking the column names as literals. So changed the column names from "01 through 12" to "Jan through Dec" and now the column is doing what it was intended to do :) Duh!

[David]

I have the same problem and can't rename DataFields, so they are "numeric".
What can i do to make the following work for me?     

                          <telerik:GridCalculatedColumn DataFields="1001,5001"
                                                              Expression="{0}+{1}"
                                                              HeaderText="Total" DataType="System.Decimal"
                                                              FilterControlWidth="60px"  ItemStyle-HorizontalAlign="Right"  
                                                              ItemStyle-Width="60px" ItemStyle-CssClass="label_SS_8" DataFormatString="{0:C0}"
                                                              Aggregate="Sum" FooterStyle-Width="60px" FooterStyle-HorizontalAlign="Right"/>   

[Princy]

Hi David,

I'm afraid when you set numeric values as DataFields, these values get added and are not treated as column name. One suggestion is that you can try to add the contents of two columns and display it in a label using GridTemplateColumn as shown below:

ASPX:

<telerik:GridTemplateColumn HeaderText="Total">
    <ItemTemplate>
        <asp:Label ID="lblTotal" runat="server"></asp:Label>
    </ItemTemplate>
</telerik:GridTemplateColumn>

C#:

protected void rgrdSample_ItemDataBound(object sender, GridItemEventArgs e)
{
  if (e.Item is GridDataItem)
  {
   GridDataItem dataItem = (GridDataItem)e.Item;
   Label lblTotal = (Label)dataItem.FindControl("lblTotal");
   int total = (Convert.ToInt16(dataItem["1001"].Text) + Convert.ToInt16(dataItem["5001"].Text));
   lblTotal.Text = total.ToString();
  }
}

Thanks,
Princy

[David]

Thank you Princy.
I ended up with similar, more generic solution. Here is the code that somebody might find useful:

            If (TypeOf e.Item Is GridDataItem) Then

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

                Dim FinalTotal As Decimal = 0
                For Each column As GridColumn In grdAggregateSAM.MasterTableView.RenderColumns
                    If (TypeOf column Is GridBoundColumn) Then

                        If column.UniqueName.StartsWith("X") Then

                            Dim value As Decimal = 0
                            If IsNumeric(gridItem(column.UniqueName).Text.Replace("$", "")) Then

                                If gridItem(column.UniqueName).Text.Contains("(") Then
                                    value = Convert.ToDecimal(gridItem(column.UniqueName).Text.Replace("$", "").Replace("(", "").Replace(")", "")) * (-1)
                                Else
                                    value = Convert.ToDecimal(gridItem(column.UniqueName).Text.Replace("$", ""))
                                End If

                            End If

                            FinalTotal = FinalTotal + value

                        End If

                        If column.UniqueName = "FinalTotal" Then
                            gridItem(column.UniqueName).Text = FinalTotal
                        End If

                    End If
                Next
            End If