Grid - Calculate Total Fro Nested Grid

[Muhammad]

Hi,

I am using Nested RadGrid View and I have two Detail Tables One is Called Group and the other is called Items. What I want to acheive is that each Group contains different number of items. I am successful in displaying the sub-total of each group using GridCalculatedColumn of items but I also want that I can also calculate the Total for the number of groups. I shall be very greatful if someone can help with this problem.

For Example

Quotation 1

Fruits :

Item Name CostPrice Quantity TotalCost
Apple             10           2              20
Orange           20           2             40
                                     Sub-Total 60

Vegetables :

Carrot              5           2                       10
Cuccumber      3            2                        6
                                          Sub-Total     16
                                         
                                              Total :       76  (Need Help Here)



<telerik:RadGrid ID="rgQuotation" runat="server" Width="95%" ShowStatusBar="true"
    Skin="WebBlue" AutoGenerateColumns="False" PageSize="20" AllowSorting="True"
    AllowMultiRowSelection="False" AllowPaging="True" GridLines="None" ShowFooter="True">
    <PagerStyle Mode="NumericPages"></PagerStyle>
    <MasterTableView Width="100%" DataKeyNames="ID" AllowMultiColumnSorting="True" CommandItemDisplay="Top"
        Name="Quotation">
        <Columns>
            <telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditCommandColumn"
                ItemStyle-Width="30px" EditImageUrl="~/Portals/0/Images/Edit.gif">
            </telerik:GridEditCommandColumn>
            <telerik:GridBoundColumn SortExpression="ID" HeaderText="ID" HeaderButtonType="TextButton"
                DataField="ID" UniqueName="ID" ReadOnly="true" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn SortExpression="TariffName" HeaderText="Quotation Name"
                HeaderButtonType="TextButton" DataField="TariffName" UniqueName="TariffName"
                ReadOnly="true">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn SortExpression="RecordType" HeaderText="Rec Type" HeaderButtonType="TextButton"
                DataField="RecordType" UniqueName="RecordType" ReadOnly="true" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn SortExpression="JobID" HeaderText="Job No" HeaderButtonType="TextButton"
                DataField="JobID" UniqueName="JobID" ReadOnly="true" Visible="false">
            </telerik:GridBoundColumn>
        </Columns>
        <EditFormSettings EditFormType="Template" InsertCaption="New Quotation">
            <FormTemplate>
                <div>
                    <asp:Label ID="lblTariffName" runat="server" Text="Quotation Name" CssClass="fieldsetControlStyle"></asp:Label>
                    &nbsp;&nbsp;
                    <asp:TextBox ID="txtTariffName" runat="server" Text='<%# Eval("TariffName") %>'></asp:TextBox>
                    <asp:LinkButton ID="lnkbtnUpdate" runat="server" CausesValidation="True" CommandName='<%# IIf( DataBinder.Eval(Container, "OwnerTableView.IsItemInserted"), "PerformInsert", "Update") %>'
                        CssClass="lnkButton" ToolTip="Update">
                        <asp:Image ID="imgUpdate" runat="server" ImageUrl="~/Portals/0/Images/Update.gif" /></asp:LinkButton>
                    &nbsp;<asp:LinkButton ID="lnkbtnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
                        CssClass="lnkButton" ToolTip="Cancel">
                        <asp:Image ID="imgCancel" runat="server" ImageUrl="~/Portals/0/Images/Cancel.gif" /></asp:LinkButton>
                </div>
            </FormTemplate>
        </EditFormSettings>
        <DetailTables>
            <telerik:GridTableView DataKeyNames="GroupID" HierarchyLoadMode="Client" Width="100%"
                CommandItemDisplay="Top" runat="server" Name="Groups">
                <Columns>
                    <telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditCommandColumn"
                        ItemStyle-Width="30px" EditImageUrl="~/Portals/0/Images/Edit.gif">
                    </telerik:GridEditCommandColumn>
                    <telerik:GridBoundColumn SortExpression="GroupID" HeaderText="GroupID" HeaderButtonType="TextButton"
                        DataField="GroupID" UniqueName="GroupID" ReadOnly="true" Visible="false">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn SortExpression="GroupName" HeaderText="Group Name" HeaderButtonType="TextButton"
                        DataField="GroupName" UniqueName="GroupName">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn SortExpression="TariffID" HeaderText="TariffID" HeaderButtonType="TextButton"
                        DataField="TariffID" UniqueName="TariffID" ReadOnly="true" Visible="false">
                    </telerik:GridBoundColumn>
                    <telerik:GridTemplateColumn>
                        <FooterTemplate>
                            <asp:Label ID="lblTotalCost" runat="server" Text="Total Cost" CssClass="fieldsetControlStyle"></asp:Label>
                        </FooterTemplate>
                    </telerik:GridTemplateColumn>
                </Columns>
                <EditFormSettings EditFormType="Template" InsertCaption="New Group">
                    <FormTemplate>
                        <div>
                            <asp:Label ID="lblGroupName" runat="server" Text="Group Name" CssClass="fieldsetControlStyle"></asp:Label>
                            &nbsp;&nbsp;
                            <asp:TextBox ID="txtGroupName" runat="server" Text='<%# Eval("groupname") %>'></asp:TextBox>
                            <br />
                            <asp:LinkButton ID="lnkbtnUpdate" runat="server" CausesValidation="True" CommandName='<%# IIf( DataBinder.Eval(Container, "OwnerTableView.IsItemInserted"), "PerformInsert", "Update") %>'
                                CssClass="lnkButton">
                                <asp:Image ID="imgUpdate" runat="server" ImageUrl="~/Portals/0/Images/Update.gif" /></asp:LinkButton>
                            &nbsp;<asp:LinkButton ID="lnkbtnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
                                CssClass="lnkButton" ToolTip="Cancel">
                                <asp:Image ID="imgCancel" runat="server" ImageUrl="~/Portals/0/Images/Cancel.gif" /></asp:LinkButton>
                        </div>
                    </FormTemplate>
                </EditFormSettings>
                <CommandItemSettings AddNewRecordText="Add New Group" AddNewRecordImageUrl="~/Portals/0/Images/AddRecord.gif"
                    ShowRefreshButton="false" />
                <DetailTables>
                    <telerik:GridTableView DataKeyNames="ID" Width="100%" Name="Items" CommandItemDisplay="Top"
                        EditMode="InPlace" runat="server" HierarchyLoadMode="Client">
                        <Columns>
                            <telerik:GridEditCommandColumn ButtonType="ImageButton" UniqueName="EditCommandColumn"
                                UpdateImageUrl="~/Portals/0/Images/Update.gif" ItemStyle-Width="40px" EditImageUrl="~/Portals/0/Images/Edit.gif"
                                CancelImageUrl="~/Portals/0/Images/Cancel.gif">
                            </telerik:GridEditCommandColumn>
                            <telerik:GridBoundColumn SortExpression="ID" HeaderText="ID" HeaderButtonType="TextButton"
                                DataField="ID" UniqueName="ID" ReadOnly="true" Visible="false">
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn SortExpression="ItemOrder" HeaderText="ItemOrder" HeaderButtonType="TextButton"
                                DataField="ItemOrder" UniqueName="ItemOrder" ReadOnly="true" Visible="false">
                            </telerik:GridBoundColumn>
                            <%--                            <telerik:GridBoundColumn SortExpression="ItemName" HeaderText="Item Name" DataField="ItemName"
                                UniqueName="ItemName">
                                UniqueName="ItemName">
                            </telerik:GridBoundColumn>--%>
                            <telerik:GridTemplateColumn HeaderText="Item Name">
                                <ItemTemplate>
                                    <asp:Label ID="lblItemName" runat="server" Text='<%# Eval("ItemName") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtItemName" runat="server" Text='<%# Eval("ItemName") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <ItemStyle Width="30%" />
                            </telerik:GridTemplateColumn>
                            <telerik:GridTemplateColumn HeaderText="Rate">
                                <ItemTemplate>
                                    <asp:Label ID="lblUnits" runat="server" Text='<%# Eval("Units") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <telerik:RadNumericTextBox ID="rntbRate" ShowSpinButtons="true" IncrementSettings-InterceptArrowKeys="true"
                                        IncrementSettings-InterceptMouseWheel="true" runat="server" Type="Number" Width="80px"
                                        DbValue='<%# Eval("Units") %>' NumberFormat-DecimalDigits="2" MinValue="1" IncrementSettings-Step="1"
                                        CssClass="fieldsetControlWidth fieldsetLine" />
                                </EditItemTemplate>
                            </telerik:GridTemplateColumn>
                            <telerik:GridTemplateColumn HeaderText="Quantity">
                                <ItemTemplate>
                                    <asp:Label ID="lblQuantity" runat="server" Text='<%# Eval("Quantity") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <telerik:RadNumericTextBox ID="rntbQuantity" ShowSpinButtons="true" IncrementSettings-InterceptArrowKeys="true"
                                        IncrementSettings-InterceptMouseWheel="true" runat="server" Type="Number" Width="80px"
                                        DbValue='<%# Eval("Quantity") %>' NumberFormat-DecimalDigits="0" MinValue="0"
                                        IncrementSettings-Step="1" CssClass="fieldsetControlWidth fieldsetLine">
                                        <ClientEvents OnBlur="Blur" OnFocus="Focus" />
                                    </telerik:RadNumericTextBox>
                                </EditItemTemplate>
                            </telerik:GridTemplateColumn>
                            <telerik:GridTemplateColumn HeaderText="C.P">
                                <ItemTemplate>
                                    <asp:Label ID="lblCostPrice" runat="server" Text='<%# Format(DataBinder.Eval(Container.DataItem,"CostPrice"),"###,##0.00") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <telerik:RadNumericTextBox ID="rntbCostPrice" ShowSpinButtons="true" IncrementSettings-InterceptArrowKeys="true"
                                        IncrementSettings-InterceptMouseWheel="true" runat="server" Type="Number" Width="80px"
                                        DbValue='<%# Eval("CostPrice") %>' NumberFormat-DecimalDigits="2" MinValue="0"
                                        IncrementSettings-Step="1" CssClass="fieldsetControlWidth fieldsetLine" />
                                </EditItemTemplate>
                            </telerik:GridTemplateColumn>
                            <telerik:GridTemplateColumn HeaderText="S.P">
                                <ItemTemplate>
                                    <asp:Label ID="lblSellingPrice" runat="server" Text='<%# Format(DataBinder.Eval(Container.DataItem,"SellingPrice"),"###,##0.00") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <telerik:RadNumericTextBox ID="rntbSellingPrice" ShowSpinButtons="true" IncrementSettings-InterceptArrowKeys="true"
                                        IncrementSettings-InterceptMouseWheel="true" runat="server" Type="Number" Width="80px"
                                        DbValue='<%# Eval("SellingPrice") %>' NumberFormat-DecimalDigits="2" MinValue="0"
                                        IncrementSettings-Step="1" CssClass="fieldsetControlWidth fieldsetLine" />
                                </EditItemTemplate>
                            </telerik:GridTemplateColumn>
                            <telerik:GridCalculatedColumn HeaderText="C.P Total" DataType="System.Double" FooterText="C.P Total : "
                                DataFields="CostPrice,Units,Quantity" Expression="{0}/{1}*{2}" Aggregate="Sum"
                                DataFormatString="{0:###,##0.00}" FooterAggregateFormatString="{0:£###,##0.00}" />
                            <telerik:GridCalculatedColumn HeaderText="S.P Total " UniqueName="TotalSellingPrice"
                                DataFields="SellingPrice,Units,Quantity" Expression="{0}/{1}*{2}" DataType="System.Double"
                                FooterAggregateFormatString="{0:£###,##0.00}" FooterText="S.P Total : " Aggregate="Sum"
                                DataFormatString="{0:###,##0.00}" />
                        </Columns>
                        <CommandItemSettings AddNewRecordText="Add New Item" AddNewRecordImageUrl="~/Portals/0/Images/AddRecord.gif"
                            ShowRefreshButton="false" />
                        <%--<EditFormSettings EditFormType="Template" InsertCaption="New Item">
                            <FormTemplate>
                                <div>
                                    <asp:Label ID="lblItemName" runat="server" Text="Item Name" CssClass="fieldsetControlStyle"></asp:Label>
                                    &nbsp;&nbsp;
                                    <asp:TextBox ID="txtItemName" runat="server" Text='<%# Eval("ItemName") %>'></asp:TextBox>
                                    <br />
                                    <asp:Label ID="lblRate" runat="server" Text="Rate" CssClass="fieldsetControlStyle"></asp:Label>
                                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                    <asp:TextBox ID="txtRate" runat="server" Text='<%# Eval("Units") %>'></asp:TextBox>
                                    <br />
                                    <asp:Label ID="lblQuantity" runat="server" Text="Quantity"></asp:Label>
                                    <telerik:RadNumericTextBox ID="rntbQuantity" ShowSpinButtons="true" IncrementSettings-InterceptArrowKeys="true"
                                        IncrementSettings-InterceptMouseWheel="true" runat="server" Type="Number" Width="80px"
                                        NumberFormat-DecimalDigits="0" MinValue="0" IncrementSettings-Step="1" CssClass="fieldsetControlWidth fieldsetLine" />
                                    <br />
                                    <asp:Label ID="lblCostPrice" runat="server" Text="Cost Price" CssClass="fieldsetControlStyle"></asp:Label>
                                    &nbsp;&nbsp;&nbsp;
                                    <telerik:RadNumericTextBox ID="rntbCostPrice" MinValue="0" runat="server" MaxLength="6"
                                        DbValue='<%# Bind("CostPrice") %>' Width="160px" Type="Currency" Culture="English (United Kingdom)">
                                    </telerik:RadNumericTextBox>
                                    <br />
                                    <asp:Label ID="lblSellingPrice" runat="server" Text="Selling Price" CssClass="fieldsetControlStyle"></asp:Label>
                                    &nbsp;
                                    <telerik:RadNumericTextBox ID="rntbSellingPrice" MinValue="0" runat="server" MaxLength="6"
                                        DbValue='<%# Bind("SellingPrice") %>' Width="160px" Type="Currency" Culture="English (United Kingdom)">
                                    </telerik:RadNumericTextBox>
                                    <br />
                                    <br />
                                    <asp:LinkButton ID="lnkbtnUpdate" runat="server" CausesValidation="True" CommandName='<%# IIf( DataBinder.Eval(Container, "OwnerTableView.IsItemInserted"), "PerformInsert", "Update") %>'
                                        CssClass="lnkButton" ToolTip="Update">
                                        <asp:Image ID="imgUpdate" runat="server" ImageUrl="~/Portals/0/Images/Update.gif" /></asp:LinkButton>
                                    &nbsp;<asp:LinkButton ID="lnkbtnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
                                        CssClass="lnkButton" ToolTip="Cancel">
                                        <asp:Image ID="imgCancel" runat="server" ImageUrl="~/Portals/0/Images/Cancel.gif" /></asp:LinkButton>
                                </div>
                            </FormTemplate>
                            <%-- <EditColumn ButtonType="ImageButton" InsertText="Insert Item" UpdateText="Update Item"
                                UpdateImageUrl="~/Portals/0/Images/Update.gif" InsertImageUrl="~/Portals/0/Images/Update.gif"
                                UniqueName="EditCommandColumn1" CancelText="Cancel edit">
                            </EditColumn>
                        </EditFormSettings>--%>
                    </telerik:GridTableView>
                </DetailTables>
            </telerik:GridTableView>
        </DetailTables>
        <CommandItemSettings AddNewRecordText="Add new Quotation" AddNewRecordImageUrl="~/Portals/0/Images/AddRecord.gif"
            ShowRefreshButton="false" ShowExportToPdfButton="true" />
    </MasterTableView>
    <ClientSettings AllowKeyboardNavigation="true" EnableRowHoverStyle="true">
        <ClientEvents OnRowContextMenu="RowContextMenu" OnRowDblClick="RowDblClick" />
        <Selecting AllowRowSelect="true" />
        <KeyboardNavigationSettings EnableKeyboardShortcuts="true" AllowSubmitOnEnter="true"
            AllowActiveRowCycle="true" CollapseDetailTableKey="LeftArrow" ExpandDetailTableKey="RightArrow" />
    </ClientSettings>
</telerik:RadGrid>
<telerik:RadContextMenu ID="rcMenu" runat="server" OnItemClick="rcMenu_ItemClick"
    EnableRoundedCorners="true" EnableShadows="true">
    <Items>
        <telerik:RadMenuItem Text="Add" />
        <telerik:RadMenuItem Text="Edit" />
        <telerik:RadMenuItem Text="Delete" />
        <telerik:RadMenuItem Text="Move Up" />
        <telerik:RadMenuItem Text="Move Down" />
    </Items>
</telerik:RadContextMenu>
<%--</telerik:RadAjaxPanel>--%>
<input type="hidden" id="radGridClickedRowIndex" name="radGridClickedRowIndex" />
<input type="hidden" id="radGridClickedTableId" name="radGridClickedTableId" />

[Maria Ilieva]

Hello Muhammad,

Please note that the required functionality could not be achieved in the current implementation you have. When the Grid is hierarchical it is not possible to place additional row for the group total. Currently the required functionality could be achieved with grouped Grid as shown in the following online example.

Kind regards,
Maria Ilieva
the Telerik team

[Muhammad]

Hi Maria,

I have seen that example but I don't understand how I implement that example in my scenario because I want to implement that functionality using nested grid. Any help would be greatly appreciated.

Thanks,

[Maria Ilieva]

Hi Muhammad,

Currently the shown example could not be implemented in your case with nested Grid, because as I mentioned in my previous post you could not manually add new rows for totals ion such hierarchy. The possibility you have is to use grouped Grid as shown in the example.

Greetings,
Maria Ilieva
the Telerik team

[Muhammad]

I solved it ...