Sum of multiple aggregate columns

[Roger Criebaum]

I'm trying to create a custom summary row that shows "Order Qty: <number>", where <number> is a total of the sum of several columns. I have created the GridTableSummaryRow, as well as five GridSummaryColumns. Each column is an Int32Aggregate. I need to represent the sum of each of these columns with the formula:

col5 - (col1 + col2 + col3 + col4)   --> Order Qty: 350

I get single value (or multiple values) using the GridTableSummaryRow.Title, but that doesn't seem to be able to translate any type of calculation. For example: Title = "Order Qty: {TotalQtyAvail}+{TotalQtyOnPO}" just displays "Order Qty: 75+1". I need it to show "Order Qty: 76".

How would I go about doing this? 

Thanks,

Kevin

[Vijayarasan Sivanandham]

Hi Kevin Swanner,

Thank you for contacting Syncfusion support. 

You can calculate summary value for one column based on the summary values of other columns by changing the display text in the SfDataGrid.DrawCell event with required value. Please refer the below code snippet for your reference, 

//Add the TableSummaryRow 

this.sfDataGrid.TableSummaryRows.Add(new GridTableSummaryRow() 

{ 

                Name = "TableSummaryRow", 

                ShowSummaryInRow = true, 

                Title = "TotalQtyAvail : {Q1} TotalQtyOnPO : {Q2}", 

                SummaryColumns = new System.Collections.ObjectModel.ObservableCollection<Syncfusion.Data.ISummaryColumn>() 

                { 

                   new GridSummaryColumn() 

                    { 

                        Name = "Q1", 

                        SummaryType = Syncfusion.Data.SummaryType.DoubleAggregate, 

                        Format= "{Sum}", 

                        MappingName="Q1", 

                    }, 

                    new GridSummaryColumn() 

                    { 

                        Name = "Q2", 

                        SummaryType = Syncfusion.Data.SummaryType.DoubleAggregate, 

                        Format= "{Sum}", 

                        MappingName="Q2", 

                    } 

                } 

 }); 

 

private void SfDataGrid_DrawCell(object sender, DrawCellEventArgs e) 

{ 

            if ((e.DataRow as DataRowBase).RowType == RowType.TableSummaryCoveredRow) 

            { 

                float summaryColumnValue1 = 0; 

                float summaryColumnValue2 = 0; 

                float.TryParse(SummaryCreator.GetSummaryDisplayText((e.DataRow.RowData as SummaryRecordEntry), "Q1", sfDataGrid.View), out summaryColumnValue1); 

                float.TryParse(SummaryCreator.GetSummaryDisplayText((e.DataRow.RowData as SummaryRecordEntry), "Q2", sfDataGrid.View), out summaryColumnValue2); 

                e.DisplayText = e.DisplayText + "  Sum of Order Qty:   " + (summaryColumnValue1 + summaryColumnValue2).ToString(); 

            } 

} 

Sample Link: https://www.syncfusion.com/downloads/support/forum/163402/ze/Sample1023472216 

Please refer the following KB to calculate the summary value based on other columns summary values. 

KB Link: https://www.syncfusion.com/kb/8810/how-to-calculate-the-summary-value-based-on-other-column-summary-value-in-winforms-datagrid 

Please let us know, if you require further assistance on this.

Regards,
Vijayarasan S 

[Roger Criebaum]

Thank you for your reply.

I used your sample code and came up with this solution:

GridTableSummaryRow tableSummaryRow1 = new GridTableSummaryRow();

tableSummaryRow1.Name = "OrderQtySummary";

tableSummaryRow1.ShowSummaryInRow = true;

tableSummaryRow1.Title = "Order: ";

tableSummaryRow1.Position = VerticalPosition.Bottom;

tableSummaryRow1.SummaryColumns.Add(new GridSummaryColumn { Name = "SumQtyAvail", SummaryType = SummaryType.Int32Aggregate, Format = "{Sum}", MappingName = "QTY_AVAIL" });

tableSummaryRow1.SummaryColumns.Add(new GridSummaryColumn { Name = "SumQtyOnPO", SummaryType = SummaryType.Int32Aggregate, Format = "{Sum}", MappingName = "QTY_ON_PO" });

tableSummaryRow1.SummaryColumns.Add(new GridSummaryColumn { Name = "SumQtyOnXferIn", SummaryType = SummaryType.Int32Aggregate, Format = "{Sum}", MappingName = "QTY_ON_XFER_IN" });

tableSummaryRow1.SummaryColumns.Add(new GridSummaryColumn { Name = "SumQtyOnXferOut", SummaryType = SummaryType.Int32Aggregate, Format = "{Sum}", MappingName = "QTY_ON_XFER_OUT" });

tableSummaryRow1.SummaryColumns.Add(new GridSummaryColumn { Name = "SumUsage", SummaryType = SummaryType.Int32Aggregate, Format = "{Sum}", MappingName = "USAGE" });

mainGrid.TableSummaryRows.Add(tableSummaryRow1);

private void MainGrid_DrawCell(object sender, DrawCellEventArgs e)

{

   if ((e.DataRow as DataRowBase).RowType == RowType.TableSummaryCoveredRow)

   {

      string[] sumItems = new string[] { "QTY_AVAIL", "QTY_ON_PO", "QTY_ON_XFER_IN", "QTY_ON_XFER_OUT", "USAGE" };

      float[] sumValues = new float[] { 0, 0, 0, 0, 0 };

      for (int i = 0; i < 5; i++)

      {

         string item = sumItems[i];

         bool success = float.TryParse(SummaryCreator.GetSummaryDisplayText((SummaryRecordEntry)(e.DataRow.RowData), item, DataGrid.View), out sumValues[i]);

      }

      e.DisplayText = $"Order: {sumValues[4] - (sumValues[0] + sumValues[1] + sumValues[2] + sumValues[3])}";

         }

 }

Thanks for your advice.

[Vijayarasan Sivanandham]

Hi Kevin Swanner, 

Thanks for the update. 

 

We are glad to know that the reported problem has been resolved at your end. Please let us know if you have any further queries on this. We are happy to help you😊. 

 

Regards, 

Vijayarasan S