Sum UltraGrid Column Values & Show in a TextBox

Hi!

I’m using the UltraGrid (in namespace Infragistics.Win.UltraWinGrid) and I want to sum all the values (integers) in a certain column in the grid and put the result in a textbox (not in an extra row in the grid!). How do I do that?

I’m using version 6.2.

Regards, Emelia

You can always enumerate through your rows collection and calculate the sum yourself, but maybe you want the grid to calculate it?

 

Have you tried using Summaries? This allows the grid to calculate certain summary columns automatically, and using the SummaryValue class or SummaryValueChanged event you can get the results (to put in your textbox)… 

I do not want to loop thru the rows and sum the values, I want the grid to do it for me!

Can you maybe give me an example on how to use the summaries? What is it? A class or method on the UltraGridColumn?

 Regards, Emelia

Hi,

I had the same issue last week and SummaryRows weren't the solution as they have to be visible in order for them to recalculate. I'd suggest using the UltraCalcManager and supplying a named reference and a formula to it. This should give you the column sum you need.

Regards, Mark

Which methodes/properties shall I use to supply the UltraCalcManager with a named reference and a formula?

Add the UltraCalcManager to your form

Add a NamedReference to the UltraCalcManager NamedReferences collection. The formula will be something like sum( [//_grid/DataRecord/Qty] ), where "_grid" is the Id of the Grid, DataRecord is the Band name and Qty is the Column name.

In the form code add an event handler to the NameDReferenceResultChanged event of the UltraCalcManager 

_calcManager.NamedReferenceResultChanged +=

new Infragistics.Win.UltraWinCalcManager.NamedReferenceResultChangedEventHandler(OnSumQty_NamedReferenceResultChanged);

In the event handler, update your control

void OnSumQty_NamedReferenceResultChanged(object sender, Infragistics.Win.UltraWinCalcManager.NamedReferenceResultChangedEventArgs e)

{

_sumQtyLabel.Text = e.NamedReference.FormulaResult.ToString();

}

Actually, you don't need the NamedReference – you can apply a formula directly to the TextBox control using the CalcSettings property. This is an extender property that will show up in the property grid at design-time as long as there is an UltraCalcManager component on the form with the TextBox. 

Also… I recommend using a Label, rather than a TextBox. Or making the TextBox read-only. Since any edits the user makes will be overwritten by the formula. 

Oooooo!!! I didn't know that: that's cool! 🙂

 Yes, it is. 🙂

You can use CalcManager on any control. The CalcSettings allows you to apply a formula and you can even choose which property of the control the formula applies to. 

 Hi,

I've tried doing what you suggested, but I get an "Invalid or unavailable reference" error.

I have an UltraGrid called dgvWOVals where I manually define column MV of type Double. I also have a TextBox where I want to show the sum of MV. I can see the grid in the Formula Builder and I can reference the relevant column, however if I add the formula "sum([//dgvWOVals/Band 0/MV] )" to the TextBox I get the error.

I've tried the same thing using DataGridView and it worked, but I need to use UltraGrid because I need to create calculated columns and drive some of the other calculations off them.

Also, because UltraGrid loses all settings when the Data Schema is changed, I tend to do all formatting, binding and all calculated columns at run time. Will I still be able to reference columns created at run time through UltraCalcManager.

As a general statement I find that UltraCalcManager only works under some strict conditions and fails if any of the conditions are not met. Is there a good tutorial outlining when it will and will not work?

Thank you

Hi,

I tried this out using an unbound column in the grid and basically the same formula you are using and it works fine for me.

My only guess here is that something on your form has gotten corrupted and the grid is not properly hooked up to the calculation network.

If you look at your form's Designer code in the InitializeComponent method, make sure there is a line of code in there that sets the CalcManager property of the grid. Something like this:

this.ultraGrid1.CalcManager = this.ultraCalcManager1;

Found it,

I was binding the datatable to the grid by doing dgvWOVals.DataSource = <DataTable>, which overrides the schema. Therefore in the formula the Band key has to be replaced with the table name, so in my case that would be 

"sum([//dgvWOVals/Valuations/MV] )"

instead of

 "sum([//dgvWOVals/Band 0/MV] )"

 

Thanks for the reply, though.

What if I only want to sum certain rows in the column? 

Say I have 10 rows in the column but only want to sum 5 of them and display it in a textbox?   They do have a u_id, so how do I say "only sum these u_ids together"?

 

What you would do is create an unbound column in the grid and give it a formula using the "IF" function so that you give the unbound cell a value or 0 based on the u_id. Then you sum the unbound column.

Can you give me some sample code?  I don't understand the formula of the grid and don't know how I can say:

Sum(if(u_id in (10.2, 10.3, 10.5, 10.6, 10.8, 10.9), 0, annual_spent))

Thanks….

 

You don't do Sum(If(… UltraGrid does not have a SUMIF or SUMPRODUCT the way Excel does.

 

You create an unbound calculated column with the formula if(u_id = X, annual_spent, 0)… Name it, say, Selected_annual_spent. Then in the textbox you say sum([//UltraGrid/BandName/Selected_annual_spent]).

Make sure your BandName is correct – if you're binding the grid to a datatable at run time, the band name is the table name.

 

Hope this helps

ok, I figured out the syntax some but my formula still isn't working.  Any ideas what is wrong?

This is my formula for the unbound column.  It still includes all of the category_ids in the unbound column.  I want it to not include the ones below (10.2, 10.3, 10.5, 10.6, 10.8, 10.9)….

if(and( [Category_id] <> "10.2" , [Category_id] <> "10.3" , [Category_id] <> "10.5", [Category_id] <> "10.6", [Category_id] <> "10.8", [Category_id] <> "10.9"  ), [Annual_Spent_After], 0 )

What data type is your Category_id ? If it is numeric, you shouldn't have quotes around the values.

All you do is create an unbound column in the grid and apply a formula to it. Here's some sample code. In this sample, the unbound column formula checks the Value of the column names "Int32 1" and if it's a 1, assigns the value of the "Int32 2" column to the unbound column, otherwise, it assigns a 0.

Then the TextBox or label or whatever can simply sum the unbound column.

        private void ultraGrid1_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
        {
            UltraGridColumn myUnboundColumn = e.Layout.Bands[0].Columns.Add("My Unbound Column");
            myUnboundColumn.DataType = typeof(int);
            myUnboundColumn.Formula = "IF ( [Int32 1] = 1, [Int32 2], 0 )";
            //myUnboundColumn.Hidden = true;
        }

Mike,

I hear what you are saying about adding the calculated field and summing it, but this is surely a long winded way…I basically have the same issue as tammy in that i am trying to carry out a conditional sum based on a simple expression. For instance on my text box the following will work

SUM([//gdBillCallsGrid/ConnectionBillCalls/CallCost])

but I cannot get the following to work

SUM(if([//gdBillCallsGrid/ConnectionBillCalls/CallCost] >10,[//gdBillCallsGrid/ConnectionBillCalls/CallCost],0))

I get "Incorrect type of argument or operand"

 

As I stated above, there is no way to do this with a single formula. You must use 2 formulas.

References in any formula are relative to the object to which the formula is being applied.If you are applying a formula to a grid column, then referencing another column really references the cell in the column in the same row as the cell whose formula is being calculated.

For example, if I have two columns in the grid called "Column 0" and "Column 1", then when I apply a formula to "Column 1" that references "[Column 0]", each cell in column 1 has a context (the row) by which it can get a corresponding cell in Column 0.

For a textbox, there is no such context. So if a TextBox refers to "Column 0", the reference represents the entire column. Therefore, in a TextBox, doing something like "if ([Column 0] > 10, [Column 0], 0)" is meaningless. Asking if a column is greater than 0 is a meaningless condition, as Column 0 is not a value, it's a list of cells.

I was assuming that the actual calcmanager iterated through the individual rows as part of the SUM function.  If it did, it would have a context for referencing an individual row and be able to evaluate my IF statement base on another column value in the same row,  rather than the whole column as you mentioned above.

I had better add my calculated column…….

I have implemented a calculated column and it works fine but it has rasied another issue. Is there any way to get the calcmanager to run it calculations against hidden records (hidden=true;)

I figured it out. When you want hidden rows included in the calculation you need to set the Override.FormulaRowIndexSource to FormulaRowIndexSource.Lstindex on the actual ultrawingrid.