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
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)";
This is what i tried in the beginning. I keep getting the following exceptions. Now, the data behind the grid has no null values. I have checked this.
See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************System.NullReferenceException: Object reference not set to an instance of an object. at Infragistics.Win.UltraWinGrid.RefCellCollection.RefCellCollectionEnumerator.MoveNext() at Infragistics.Win.CalcEngine.RangeCalcInfo.EvaluateNextRow() at Infragistics.Win.CalcEngine.UltraCalcEngine.Evaluate(Int64 ticks) at Infragistics.Win.CalcEngine.UltraCalcEngine.Recalc(Int64 ticks, Boolean isStartOfRecalcOperation) at Infragistics.Win.CalcEngine.UltraCalcEngine.Recalc(Int64 ticks) at Infragistics.Win.UltraWinCalcManager.UltraCalcManager.ReCalcInternal(Int64 ticks) at Infragistics.Win.UltraWinCalcManager.UltraCalcManager.ReCalc(Int64 millis) at Infragistics.Win.UltraWinCalcManager.UltraCalcManager.ReCalc() at Infragistics.Win.UltraWinCalcManager.UltraCalcManager.ReCalcSynchronous() at Infragistics.Win.UltraWinCalcManager.UltraCalcManager.VerifyReCalc() at Infragistics.Win.UltraWinCalcManager.UltraCalcManager.DirtyCalc(IUltraCalcReference reference, ValueDirtiedAction action) at Infragistics.Win.UltraWinCalcManager.UltraCalcManager.Infragistics.Win.CalcEngine.IUltraCalcManager.AddReference(IUltraCalcReference reference) at Infragistics.Win.UltraWinGrid.BandReference.VerifyGroupLevelSummaryFormulas() at Infragistics.Win.UltraWinGrid.BandReference.ResumeVerifyGroupLevelSummaryFormulas(Boolean verifyOnResume) at Infragistics.Win.UltraWinGrid.RowsCollection.VerifyGroupByVersion() at Infragistics.Win.UltraWinGrid.RowsCollection.EnsureNotDirty() at Infragistics.Win.UltraWinGrid.RowsCollection.EnsureFiltersEvaluated() at Infragistics.Win.UltraWinGrid.UltraGridRow.get_IsFilteredOut() at Infragistics.Win.UltraWinGrid.UltraGridRow.get_HiddenInternal() at Infragistics.Win.UltraWinGrid.UltraGridRow.get_HiddenResolved() at Infragistics.Win.UltraWinGrid.RowScrollRegion.get_FirstRow() at Infragistics.Win.UltraWinGrid.RowScrollRegion.GetMaxScrollPosition(Boolean scrollToFill, Boolean ignoreScrollBoundsResolved) at Infragistics.Win.UltraWinGrid.RowScrollRegion.GetMaxScrollPosition(Boolean scrollToFill) at Infragistics.Win.UltraWinGrid.RowScrollRegion.EnsureScrollRegionFilled(Boolean calledFromRegenerateVisibleRows) at Infragistics.Win.UltraWinGrid.RowScrollRegion.RegenerateVisibleRows(Boolean resetScrollInfo) at Infragistics.Win.UltraWinGrid.RowScrollRegion.RegenerateVisibleRows() at Infragistics.Win.UltraWinGrid.RowScrollRegion.WillScrollbarBeShown(ScrollbarVisibility assumeColScrollbarsVisible) at Infragistics.Win.UltraWinGrid.ViewStyleBase.FitColumnsToWidth() at Infragistics.Win.UltraWinGrid.BandsCollection.CalculateBandMetrics(Int32 pass) at Infragistics.Win.UltraWinGrid.BandsCollection.CalculateBandMetrics() at Infragistics.Win.UltraWinGrid.ColScrollRegionsCollection.InitializeMetricsHelper() at Infragistics.Win.UltraWinGrid.ColScrollRegionsCollection.InitializeMetrics() at Infragistics.Win.UltraWinGrid.RowScrollRegion.RegenerateVisibleRows(Boolean resetScrollInfo) at Infragistics.Win.UltraWinGrid.RowScrollRegion.RegenerateVisibleRows() at Infragistics.Win.UltraWinGrid.RowScrollRegion.WillScrollbarBeShown(ScrollbarVisibility assumeColScrollbarsVisible) at Infragistics.Win.UltraWinGrid.ScrollRegionBase.WillScrollbarBeShown() at Infragistics.Win.UltraWinGrid.RowScrollRegion.PositionScrollbar(Boolean resetScrollInfo) at Infragistics.Win.UltraWinGrid.ScrollRegionBase.SetOriginAndExtent(Int32 origin, Int32 extent) at Infragistics.Win.UltraWinGrid.RowScrollRegion.SetOriginAndExtent(Int32 origin, Int32 extent) at Infragistics.Win.UltraWinGrid.DataAreaUIElement.ResizeRowScrollRegions() at Infragistics.Win.UltraWinGrid.DataAreaUIElement.PositionChildElements() at Infragistics.Win.UIElement.VerifyChildElements(ControlUIElementBase controlElement, Boolean recursive) at Infragistics.Win.UltraWinGrid.DataAreaUIElement.VerifyChildElements(ControlUIElementBase controlElement, Boolean recursive) at Infragistics.Win.UltraWinGrid.DataAreaUIElement.set_Rect(Rectangle value) at Infragistics.Win.UltraWinGrid.UltraGridUIElement.PositionChildElements() at Infragistics.Win.UIElement.VerifyChildElements(ControlUIElementBase controlElement, Boolean recursive) at Infragistics.Win.UltraWinGrid.UltraGridUIElement.VerifyChildElements(ControlUIElementBase controlElement, Boolean recursive) at Infragistics.Win.UIElement.DrawHelper(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Boolean clipText, Boolean forceDrawAsFocused, Boolean preventAlphaBlendGraphics) at Infragistics.Win.UIElement.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Boolean forceDrawAsFocused, Boolean preventAlphaBlendGraphics) at Infragistics.Win.ControlUIElementBase.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Size elementSize, Boolean preventAlphaBlendGraphics) at Infragistics.Win.ControlUIElementBase.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Size elementSize) at Infragistics.Win.ControlUIElementBase.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode) at Infragistics.Win.UltraWinGrid.UltraGridUIElement.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode) at Infragistics.Win.UltraControlBase.OnPaint(PaintEventArgs pe) at Infragistics.Win.UltraWinGrid.UltraGrid.OnPaint(PaintEventArgs pe) at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer) at System.Windows.Forms.Control.WmPaint(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Any ideas. I should mention that I have no filters in my grid. I do however have a group by.
Hi,
This is not related to null values in the grid.
It's either a bug in the grid - in which case the solution is to update to the latest Service Release or possibly update to a newer version, depending on what version you are using.
How to get the latest service release - Windows Forms - Service Releases
Or, it might be that you are using a set of incompatible settings that create a sort've catch-22 where the formula is dependent on the visibility of the row, but the visibility of the row is dependent on the formula. If that's the case, you may be able to resolve it by changing the FormulaRowSourceIndex to something other than the default, which is VisibleIndex.
layout.Override.FormulaRowIndexSource = FormulaRowIndexSource.RowIndex;
I'm just following up on this. Did my suggestions solve the issue? Do you need any further assistance with this?
I will keep the case open for a few more days and then close it if I don't hear from you.
I have now gone a different way and made the calculations myself. Doing an update isn't a viable option at this point in our cycle.
Thank you for the help though.