Master Detail, Show Aggregate Sum in a Master Table Template Column not in Footer.

[gc_0620]

Folks using VS 2010 with RadControls for ASP.NET AJAX Q1 2013.

In Master Table (Paging is enabled, ShowFooter="false").

Assuming I have following columns in Master Table.
Name:                    Monthly Income    Total House Hold Monthly Income (Template Column)
Master Record           1000.00

The Master Table Monthly income column is:

<telerik:GridBoundColumn DataField="MonthlyIncome" HeaderText="Monthly Income"
SortExpression="MonthlyIncome"> </telerik:GridBoundColumn>

Assuming I have following columns in Detail Table (Paging is enabled, ShowFooter="True")
Name:                    Monthly Income:                
Child Record1            200.00
Child Record1            300.00
Child Record1            400.00
Child Record1            500.00

The detail Table Monthly income column is:

<telerik:GridBoundColumn Aggregate="Sum" DataField="MonthlyIncome" HeaderText="Monthly Income"
                    FooterText="All Child records Monthly Income: ">
                </telerik:GridBoundColumn>

             
I would like to show Total House hold monthly income of Master/Detail records in Master Table Template Column. In this example the value of that Template column will be 2400.00. 1000.00 (Master Record Monthly Income)+1400.00 (Sum of Monthly Income of all Child Records).

Thanks for any help

Sincerely

gc_0620
               

[gc_0620]

Any suggestions or is not possible?

Sincerely,

gc_0620

[Angel Petrov]

Hi,

In order to achieve the functionality mentioned you will have to place a control in the template column and execute a query which should return the calculated result. Later display this result in the control. However this might not be fastest thing to do since every time a grid row is created you will have to execute a query and if you have 50 rows this means that you will have to execute 50 queries. Please revise the requirements so we could give you the best possible solution.

All the best,
Angel Petrov
the Telerik team

[gc_0620]

HI Angel,

Sorry I was away for some weeks.

Yesterday I spoke with user's; they can live with Showing footer Item in Master Table. (Paging is enabled, ShowFooter="True").

So how I get Sum of  Monthly Income of Master Table + Detail Table Monthly Income in Master Table Footer Template?

Declaration of Master Table Column,  Monthly Income: 1000.00:

<telerik:GridBoundColumn Aggregate="Sum" DataField="MonthlyIncome" HeaderText="Monthly Income"
                    FooterText="All Master & Child records Monthly Income: ">
                </telerik:GridBoundColumn>

Footer Value  will be 2400.00. 1000.00 (Master Record Monthly Income)+1400.00 (Sum of Monthly Income of all Child Records).

Declaration of Child Table Column:

<telerik:GridBoundColumn Aggregate="Sum" DataField="MonthlyIncome" HeaderText="Monthly Income"
                    FooterText="All Child records Monthly Income: ">
                </telerik:GridBoundColumn>

Values:

Name:                    Monthly Income:                
Child Record1            200.00
Child Record2            300.00
Child Record3            400.00
Child Record4           500.00

Footer Value will be 1400.00 (Sum of Monthly Income of all Child Records).

Thanks

gc_0620

[Angel Petrov]

Hello,

The functionality you mentioned can be implemented by intercepting the OnItemDataBound event and by following the below listed steps:

1. Intercept the OnItemDataBound event
2. When a record from the MasterTableView is being bound store its monthly income.
3. When the footer of the child table is being populated store the aggregate value.
4. When the footer of the parent table is being populated sum up the previously stored values and change it's text.

Note that you will have to make additional checks whether the footer which is being currently populated is  a parent or a child table footer. This is achievable by obtaining a reference to the OwnerTableView(you can set Names for the GridTableViews to distinguish them more easily). On how to obtain such a reference I suggest that you examine the code provided below:

protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
    {
        GridFooterItem footer = e.Item as GridFooterItem;
        if (footer!=null)
        {
            //obtaining the value of the footer
            string footerValue = footer["UnitPrice"].Text;
            //obtaining a reference to the owner GridTableView
            GridTableView currentTableView = footer.OwnerTableView;
        }
    }

Regards,
Angel Petrov
the Telerik team

[gc_0620]

Hi Angel
Still can't populate the parent table Footer from sum of  parent & chid table monthly income column. Parent table Footer item only shows  its Dataitem value not parent+child table value.  Per your guidance...

1. Intercept the OnItemDataBound event               ---> i did this.
2. When a record from the MasterTableView is being bound store its monthly income. --> Works
3. When the footer of the child table is being populated store the aggregate value.  --->Works
4. When the footer of the parent table is being populated sum up the previously stored values and change it's text. ----> Does not work, shows only parent table column value..

Link to any previous forum thread will be helpful. Also any suggestions.

Thanks

gc_0620

[gc_0620]

Hi Angel,

Please close the thread.  The problem is resolved without using Item Databound event; all are done in Prerender event.  This page is called from another page; thus Parent Table of this page will always have 1 row. Below is declarations.

Parent Table Column

<telerik:GridBoundColumn DataField="CurrentAmount" HeaderText="Current Amount" Aggregate="Sum"
                        UniqueName="CurrentAmountMain">
                    </telerik:GridBoundColumn>

Child Table Column

 <telerik:GridBoundColumn DataField="CurrentAmount" HeaderText="Current Amount" Aggregate="Sum"
                        UniqueName="CurrentAmountDep">
                    </telerik:GridBoundColumn>

Codes:
 

decimal totalchildamount = 0;
  decimal totalparentamount = 0;

  protected void RadGrid1_PreRender(object sender, EventArgs e)
  {
      RadGrid1.MasterTableView.Items[0].Selected = true; //making the first row selected

      foreach (GridDataItem item in RadGrid1.Items)
      {
          if (item.Selected == true)
          {
              foreach (GridColumn col in RadGrid1.Columns)
              {
                  if (col.ColumnType == "GridBoundColumn")
                  {
                      if (col.UniqueName == "CurrentAmountMain" && (item["CurrentAmountMain"].Text.ToString() != " "))
                      {
                          totalparentamount = System.Convert.ToDecimal(item["CurrentAmountMain"].Text); //getting the value of the master table BoundColumn
                      }
                  }
              }
          }

          if (item.ChildItem != null)
          // if (item.Expanded)                                // Traverse the Child Grid without Expanding
          {
              GridTableView tableView = (GridTableView)item.ChildItem.NestedTableViews[0];

              foreach (GridDataItem childitem in tableView.Items)
              {
                  if (childitem["CurrentAmountDep"].Text.ToString() != " ")
                  {
                      totalchildamount += System.Convert.ToDecimal(childitem["CurrentAmountDep"].Text); //getting the value of the Child table BoundColumn
                  }
              }
          }
      }

      GridFooterItem footeritem = RadGrid1.MasterTableView.GetItems(GridItemType.Footer)[0] as GridFooterItem;
      footeritem["CurrentAmountMain"].Text = "Total House hold income: " + "$" + (totalchildamount + totalparentamount).ToString("#,0.00");

  }

Best, Thanks

gc_0620

[Joselina]

Hi ,
 
 I also have to concatnate the Aggregate value of the Bound column of the detail table to the bound column text value of the master table. I tried with Pre render event but I am not bale to succeed can you pllease help me out.

I am using Radgrid to display the number of attachements under each category. currently the boundcolumn has the datafield = "Name" (Name of the category) alone. If each category row from the master table view is clicked then it will be expanded to show the list of attachments under it, but now i have the requirement to show the count of the attachments also along with the Name of the Category in the Bouncolumn of the MasterTableview. 

BoundColCategory.DataField = "NAME"  + (deatailtable Items Count)
I want the output in the below format

Output Example: Attachment1(s) (20)
                         Attachment2(s) (10)
                         Attachment3(s) 
                        Attachment4(s) (5)
                        Attachment4(s) (11)
                        .........
                        .............
                        .............

I tried to set the datafield/text of the Boundcolumn in events like Item cretaed or Pre- render and also in page load but not able to succeed Please help me how to achieve this programmmatically.
Below is the Grid in my .aspx page.

<am:aMGridID="amGrdNotesMaster"runat="server"TabIndex="212"AutoGenerateColumns="false"AllowSorting="false"AllowPaging="false"ClientSettings-AllowColumnsReorder="false"ClientSettings-AllowDragToGroup="false"ShowGroupPanel="false">

      <MasterTableViewDataKeyNames="ID"HierarchyDefaultExpanded="false"NoMasterRecordsText=""NoDetailRecordsText=""GroupLoadMode="Client">

                                                <Columns>

                                                   <telerik:GridBoundColumnHeaderText="Category"DataField= "Name"UniqueName="Category"/>

                                                    <telerik:GridTemplateColumnUniqueName="Reorder">

                                                        <ItemTemplate><tm:TMButtonID="btnReorder"runat="server"CommandName="reorder"Text="Reorder based on sort"/></ItemTemplate>

                                                    </telerik:GridTemplateColumn>

                                                </Columns>

                                                <DetailTables>

                                                    <telerik:GridTableViewName="Detail"AllowSorting="true"AllowPaging="false"DataKeyNames="ID"NoMasterRecordsText=""NoDetailRecordsText=""EditMode="InPlace">

                                                        <Columns>
                                                                      <telerik:GridBoundColumn Uniquename="ID" DataField ="ID" Aggregate ="Count" Visible ="true" ></telerik:GridBoundColumn>

                                                            <telerik:GridHyperLinkColumnUniqueName="Subject"HeaderText="Subject"DataNavigateUrlFormatString="~/notedisplay.aspx?id={0}"DataNavigateUrlFields="ID"DataTextField="Subject"SortExpression="Subject"ItemStyle-CssClass="gridhyperlink"HeaderStyle-Width="500px"/>

                                                            <telerik:GridHyperLinkColumnUniqueName="Attachment"HeaderText="Attachment"DataNavigateUrlFormatString="javascript:void(window.open('imageviewer.aspx?a=3&b=Attachment&c={0}', '_blank', 'left=0, top=0, width=785, height=585, titlebar=yes, location=no, status=no, toolbar=no, menubar=no, scrollbars=no, resizable=yes'));"DataNavigateUrlFields="ID"DataTextField="AttachmentName"SortExpression="AttachmentName"ItemStyle-CssClass="gridhyperlink"/>

                                                            <telerik:GridBoundColumnUniqueName="AddDate"HeaderText="Created"DataField="AddDate"SortExpression="AddDate"ReadOnly="true"/>

                                                            <telerik:GridBoundColumnUniqueName="ModDate"HeaderText="Last Modified"DataField="ModDate"SortExpression="ModDate"ReadOnly="true"/>

                                                            <telerik:GridBoundColumnUniqueName="ModUser"HeaderText="Modified By"DataField="ModUser"SortExpression="ModUser"ReadOnly="true"/>

                                                            <telerik:GridNumericColumn  HeaderText="Position"DataField="SortOrder"SortExpression="SortOrder"UniqueName="SortOrder"/>

                                                            <telerik:GridEditCommandColumnUniqueName="EditColumn">

                                                                <ItemStyleCssClass="gridhyperlink"/>

                                                            </telerik:GridEditCommandColumn>

                                                        </Columns>

                                                    </telerik:GridTableView>

                                                </DetailTables>

                                            </MasterTableView>

                                            <ClientSettingsAllowExpandCollapse="true">

                                            </ClientSettings>

                                        </am:aMGrid>

thanks
Jose

[Princy]

Hi Joselina,

Please try the following code snippet.

C#:

protected void RadGrid1_PreRender(object sender, EventArgs e)
{
    foreach (GridDataItem item in RadGrid1.MasterTableView.Items)
    {
        int count = item.ChildItem.NestedTableViews[0].Items.Count;
        item["Name"].Text = item["Name"].Text + "(s) (" + count + ")";
    }
}

Thanks,
Princy

[Joselina]

Thanks Princy. Its working fine now.

Thanks
Joselina