Forums Ultimate UI for Windows Forms
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Chris Miller
80
Summary conditional
posted

How do i go about getting the summary sum of a column based on the values of other columns

I have something like this in the code

var totalValue1 = tempBand.Summaries.Add(@"sum(if([Selected]=1, [Price], 0))", SummaryPosition.UseSummaryPositionColumn, tempBand.Columns["Price"]);
totalValue1.DisplayFormat = "{0:C}";
totalValue1.Appearance.TextHAlign = HAlign.Left;
totalValue1.SummaryDisplayArea = SummaryDisplayAreas.RootRowsFootersOnly | SummaryDisplayAreas.BottomFixed;

The code above does not work at all. I have tried to put == instead of =, '1' instead of 1 to no avail. Also tried using .Formula then setting the SummaryType to Formula. Again, Nothing. The code works however if I do not have any formula in it but it will sum everything in the column.

Tried to also look for some documentation on the correct syntax for the formulas in infragistics but found nothing I can use. So can you please tell me how this works and point me to the documentation with the syntax spec.

Thank you

Parents
  • 469350
    Offline posted

    Hi Chris,

    There are two things I notice here.

    First, make sure you have an UltraCalcManager on your form with the grid. Formulas won't work without one.

    Second, your formula won't work as you have it here, because formulas are context sensitive. In the context of a Summary, the reference to the [Selected] column you are using is a reference to the entire column, not a value of a cell within that column. So you are basically comparing a column object to the number 1 and that will never be true because a column is not a number. Likewise for the "[Price]" reference - you can't set the summary value to the entire column object (and you wouldn't want to).

    To make this work, you need to do a 2-step process for your formula where you calculate the value on each row of the grid and then sum them up for the summary. The easiest way to do this is to use an unbound column.

    The unbound column can use the formula you have here because in the context of a formula for a cell, the "[Selected]" and "[Price]" references refer to the cell within the same row. Then your summary just has to sum up the unbound column

     So the code would look something like this:

    


            
            private void ultraGrid1_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
            {
                UltraGridLayout layout = e.Layout;
                UltraGridBand band = layout.Bands[0];

                if (false == band.Columns.Exists("Significant Price"))
                {
                    UltraGridColumn sigPriceColumn = band.Columns.Add("Significant Price");
                    sigPriceColumn.DataType = typeof(decimal);
                    sigPriceColumn.Formula = "if ([Selected] = 1, [Price], 0)";
                    //sigPriceColumn.Hidden = true;
                }

                var totalValue1 = band.Summaries.Add(@"sum([Significant Price])", SummaryPosition.UseSummaryPositionColumn, band.Columns["Price"]);
                totalValue1.DisplayFormat = "{0:C}";
                totalValue1.Appearance.TextHAlign = HAlign.Left;
                totalValue1.SummaryDisplayArea = SummaryDisplayAreas.RootRowsFootersOnly | SummaryDisplayAreas.BottomFixed;
            }

    BTW... if Selected is a boolean column, then you don't need to "= 1". You could just do this:

                    sigPriceColumn.Formula = "if ([Selected], [Price], 0)";

Reply Children