Gridview summary row use sumif like excel formula

[özer]

Hi. 

I have a grid which have a lot of columns. 

20 of the columns are of decimal data type and I need to show their total. But some rows should not be included in the total.

I tried two different approaches to do this, both approaches had different problems.

This is my data model class

public class SgkThkk : SgkAssistantBase
    {
        public string cn { get; set; } = "";
        public DateTime tya { get; set; } = DateTime.Now;
        public string bm { get; set; } = "";
        public string sgm { get; set; } = "";
        public decimal tp { get; set; } = 0;
        public decimal ip { get; set; }= 0;
        public decimal kn14857 { get; set; }= 0;
        public decimal kn15921 { get; set; }= 0;
        public decimal kn6645 { get; set; }= 0;
        public decimal kn15510 { get; set; }= 0;
        public decimal kn2828 { get; set; }= 0;
        public decimal kn6111 { get; set; }= 0;
        public decimal kn17103 { get; set; }= 0;
        public decimal kn17103i { get; set; }= 0;
        public decimal kn27103 { get; set; }= 0;
        public decimal kn27103i { get; set; }= 0;
        public decimal kn37103 { get; set; }= 0;
        public decimal kn37103i { get; set; }= 0;
        public decimal kn7252 { get; set; }= 0;
        public decimal kn17256 { get; set; }= 0;
        public decimal kn7316 { get; set; }= 0;
        public decimal kn7319 { get; set; }= 0;
        public decimal kn5510 { get; set; }= 0;
        public decimal kn4857 { get; set; }= 0;
        public decimal kn159210 { get; set; }= 0;
        public decimal kn3294 { get; set; }= 0;
        public decimal odenecek { get; set; }= 0;
        public string pdfPath { get; set; } = "";
        public bool onayli { get; set; } = true;
}

Purpose: exclude lines with "İPTAL" in the 'bm' column from the total

1st approach is to use CustomSummaryItem

public class CustomSummaryItem : GridViewSummaryItem
    {
        public CustomSummaryItem()
            : base()
        { }

        public override object Evaluate(IHierarchicalRow row)
        {
            decimal totalPos = 0;
            foreach (GridViewRowInfo childRow in row.ChildRows)
            {
                if ((childRow is GridViewGroupRowInfo) == false)
                {

                    string bm = childRow.Cells["bm"].Value.ToString();

                    if (bm != "İPTAL")
                    {
                        totalPos += Convert.ToDecimal(childRow.Cells[this.Name].Value);
                    }
                }

            }
            return totalPos;
        }
    }

And this is the method that adds a summary row to the grid

public void AddSummariesToTHKK(RadGridView rgv)
        {
            rgv.MasterTemplate.AutoExpandGroups = true;
            rgv.GroupDescriptors.Clear();
            rgv.GroupDescriptors.Add(new GridGroupByExpression("cn Group By cn"));
            rgv.SummaryRowsBottom.Clear();

            rgv.MasterTemplate.ShowTotals = true;

            List<GridViewSummaryItem> lstSum = new List<GridViewSummaryItem>();
            CustomSummaryItem tptSum = new CustomSummaryItem(); tptSum.Name = "tp"; tptSum.Aggregate = GridAggregateFunction.Sum;  tptSum.FormatString = "Toplam: {0:C}"; lstSum.Add(tptSum);

            CustomSummaryItem ipSum = new CustomSummaryItem(); ipSum.Name = "ip"; ipSum.Aggregate = GridAggregateFunction.Sum; ipSum.FormatString = "Toplam: {0:C}"; lstSum.Add(ipSum);
            CustomSummaryItem kn14857Sum = new CustomSummaryItem(); kn14857Sum.Name = "kn14857"; kn14857Sum.Aggregate = GridAggregateFunction.Sum; kn14857Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn14857Sum);
            CustomSummaryItem kn15921Sum = new CustomSummaryItem(); kn15921Sum.Name = "kn15921"; kn15921Sum.Aggregate = GridAggregateFunction.Sum; kn15921Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn15921Sum);
            CustomSummaryItem kn6645Sum = new CustomSummaryItem(); kn6645Sum.Name = "kn6645"; kn6645Sum.Aggregate = GridAggregateFunction.Sum; kn6645Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn6645Sum);
            CustomSummaryItem kn15510Sum = new CustomSummaryItem(); kn15510Sum.Name = "kn15510"; kn15510Sum.Aggregate = GridAggregateFunction.Sum; kn15510Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn15510Sum);
            CustomSummaryItem kn2828Sum = new CustomSummaryItem(); kn2828Sum.Name = "kn2828"; kn2828Sum.Aggregate = GridAggregateFunction.Sum; kn2828Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn2828Sum);
            CustomSummaryItem kn6111Sum = new CustomSummaryItem(); kn6111Sum.Name = "kn6111"; kn6111Sum.Aggregate = GridAggregateFunction.Sum; kn6111Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn6111Sum);
            CustomSummaryItem kn17103Sum = new CustomSummaryItem(); kn17103Sum.Name = "kn17103"; kn17103Sum.Aggregate = GridAggregateFunction.Sum; kn17103Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn17103Sum);
            CustomSummaryItem kn17103iSum = new CustomSummaryItem(); kn17103iSum.Name = "kn17103i"; kn17103iSum.Aggregate = GridAggregateFunction.Sum; kn17103iSum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn17103iSum);
            CustomSummaryItem kn27103Sum = new CustomSummaryItem(); kn27103Sum.Name = "kn27103"; kn27103Sum.Aggregate = GridAggregateFunction.Sum; kn27103Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn27103Sum);
            CustomSummaryItem kn7252Sum = new CustomSummaryItem(); kn7252Sum.Name = "kn7252"; kn7252Sum.Aggregate = GridAggregateFunction.Sum; kn7252Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn7252Sum);
            CustomSummaryItem kn17256Sum = new CustomSummaryItem(); kn17256Sum.Name = "kn17256"; kn17256Sum.Aggregate = GridAggregateFunction.Sum; kn17256Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn17256Sum);
            CustomSummaryItem kn7316Sum = new CustomSummaryItem(); kn7316Sum.Name = "kn7316"; kn7316Sum.Aggregate = GridAggregateFunction.Sum; kn7316Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn7316Sum);
            CustomSummaryItem kn7319Sum = new CustomSummaryItem(); kn7319Sum.Name = "kn7319"; kn7319Sum.Aggregate = GridAggregateFunction.Sum; kn7319Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn7319Sum);
            CustomSummaryItem kn5510Sum = new CustomSummaryItem(); kn5510Sum.Name = "kn5510"; kn5510Sum.Aggregate = GridAggregateFunction.Sum; kn5510Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn5510Sum);
            CustomSummaryItem kn4857Sum = new CustomSummaryItem(); kn4857Sum.Name = "kn4857"; kn4857Sum.Aggregate = GridAggregateFunction.Sum; kn4857Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn4857Sum);
            CustomSummaryItem kn159210Sum = new CustomSummaryItem(); kn159210Sum.Name = "kn159210"; kn159210Sum.Aggregate = GridAggregateFunction.Sum; kn159210Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn159210Sum);
            CustomSummaryItem kn3294Sum = new CustomSummaryItem(); kn3294Sum.Name = "kn3294"; kn3294Sum.Aggregate = GridAggregateFunction.Sum; kn3294Sum.FormatString = "Toplam: {0:C}"; lstSum.Add(kn3294Sum);
            CustomSummaryItem odeSum = new CustomSummaryItem(); odeSum.Name = "odenecek"; odeSum.Aggregate = GridAggregateFunction.Sum; odeSum.FormatString = "Toplam: {0:C}"; lstSum.Add(odeSum);

            GridViewSummaryRowItem sumTopRow = new GridViewSummaryRowItem();
            sumTopRow.AddRange(lstSum);
            rgv.SummaryRowsBottom.Add(sumTopRow);

            rgv.BottomPinnedRowsMode = GridViewBottomPinnedRowsMode.Fixed;
            rgv.MasterView.SummaryRows[0].IsPinned = true;
            rgv.MasterView.SummaryRows[0].PinPosition = PinnedRowPosition.Bottom;
        }

With this approach, grand totals always show 0

The result is like below

 

CustomSummaryRow result

2nd approach is to subscribe to the GroupSummaryEvaluate event of the grid

private void rgvSgkOtomasyon_GroupSummaryEvaluate(object sender, GroupSummaryEvaluationEventArgs e)
        {
            decimal value = 0;
            foreach (GridViewRowInfo row in this.rgvSgkOtomasyon.Rows)
            {
                if (row.Cells["bm"].Value.ToString() == "İPTAL" || !e.SummaryItem.FormatString.Contains("0:C")) continue;
                value += (decimal)row.Cells[e.SummaryItem.Name].Value;
            }

            e.Value = value;
        }

There are two major problems with this approach when we group by any column.
1- The same number appears under the groups in the summary row and the grand total row. (all show the grand total of the column)
2- "0" replaces the name of the grouped column

There is no problem when we do not group by any column.

Here is the result

GroupSummaryEvaluate result

Thank you in advance for your help

 

 

[özer]

And i don't know why i can't add picture here :)

[Dinko | Tech Support Engineer]

Hi özer,

Thank you for the provided details.

The total return is 0 because the same CustomSummaryItem class is also applied for the total rows. When the Evaluate method is called for the total rows, the row parameter will be of type MasterGridViewTemplate and it will never pass the first IF clause. What you can do is to check if the type of the row parameter is MasterGridViewTemplate and if yes call the base logic of the method.

public class CustomSummaryItem : GridViewSummaryItem
{
    public CustomSummaryItem()
        : base()
    { }

    public override object Evaluate(IHierarchicalRow row)
    {
        decimal totalPos = 0;
        foreach (GridViewRowInfo childRow in row.ChildRows)
        {
            if ((childRow is GridViewGroupRowInfo) == false)
            {

                string bm = childRow.Cells["bm"].Value.ToString();

                if (bm != "IPTAL")
                {
                    totalPos += Convert.ToDecimal(childRow.Cells[this.Name].Value);
                }
            }

        }

        if (row is MasterGridViewTemplate)
        {
            return base.Evaluate(row);
        }
        return totalPos;
    }
}

Give this approach a try and let me know how it goes.

Regards,
Dinko | Tech Support Engineer
Progress Telerik

[özer]

Hi Dinko.

In the approach you suggested, lines that I didn't want to be included in the overall total were also being added.

I achieved my goal by making a small change on the solution you sent.

Thank you very much for your help.

public class CustomSummaryItem : GridViewSummaryItem
    {
        public CustomSummaryItem()
            : base()
        { }
        public decimal subtruct { get; set; } = 0;
        public override object Evaluate(IHierarchicalRow row)
        {
           
            decimal totalPos = 0;
            foreach (GridViewRowInfo childRow in row.ChildRows)
            {
                if ((childRow is GridViewGroupRowInfo) == false)
                {

                    string bm = childRow.Cells["bm"].Value.ToString();

                    if (bm != "İPTAL")
                    {
                        totalPos += Convert.ToDecimal(childRow.Cells[this.Name].Value);
                    }
                    else
                    {
                        subtruct += Convert.ToDecimal(childRow.Cells[this.Name].Value);
                    }
                }

            }
            if (row is MasterGridViewTemplate)
            {
                return Convert.ToDecimal(base.Evaluate(row)) - subtruct;
            }
            return totalPos;
        }