I want a column in the grid summary to be a calculation from three other columns in the summary. How can I enter a formula in the grid summary?
Hi,
Can you explain in more detail exactly what you mean? Are you trying to create a summary that is calculated based on other summary values? Or do you want the summary based on column values? You can do either one with a formula.
Referring to a column in the formula for a summary is very easy, you just use the column name. So you could do something like "Sum([Column 1]) + Sum([Column 2])"
If you want one summary to refer to another summary, then this is also very easy. You just have to make sure that the summary has a Key. Then you can refer to that summary by key, just like a column.
Below is my InitializeLayout code, in my summary the cells 'summary_avgfrgross_n' and 'summary_avgfrgross_u' are always blank. My summary cells 'summary_totfrgross_n', 'summary_cnt_n', 'summary_totfrgross_u' and 'summary_cnt_u' do have proper values in them. Why?
Private Sub grdSummarySM_InitializeLayout(ByVal sender As System.Object, ByVal e As Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs) Handles grdSummarySM.InitializeLayout Dim band As UltraGridBand = e.Layout.Bands(0) Dim cols As ColumnsCollection = band.Columns Dim col As UltraGridColumn ' Configure grid band.Override.AllowRowFiltering = DefaultableBoolean.False band.Override.RowFilterMode = RowFilterMode.AllRowsInBand band.UseRowLayout = True e.Layout.AutoFitStyle = AutoFitStyle.ResizeAllColumns e.Layout.BorderStyle = UIElementBorderStyle.Solid 'e.Layout.CaptionVisible = False e.Layout.GroupByBox.Hidden = True e.Layout.MaxColScrollRegions = 1 e.Layout.MaxRowScrollRegions = 1 e.Layout.Appearance.FontData.Name = "Arial" e.Layout.Appearance.FontData.SizeInPoints = 9 e.Layout.Scrollbars = Scrollbars.Vertical e.Layout.ScrollBounds = ScrollBounds.ScrollToFill e.Layout.ScrollStyle = ScrollStyle.Immediate e.Layout.Override.AllowColMoving = AllowColMoving.NotAllowed e.Layout.Override.AllowColSizing = AllowColSizing.None e.Layout.Override.AllowColSwapping = AllowColSwapping.NotAllowed e.Layout.Override.CellClickAction = CellClickAction.EditAndSelectText e.Layout.Override.HeaderClickAction = HeaderClickAction.SortMulti e.Layout.Override.RowSelectors = DefaultableBoolean.False e.Layout.Override.RowSizing = RowSizing.AutoFixed e.Layout.Override.SelectTypeCell = SelectType.None e.Layout.Override.SelectTypeCol = SelectType.None e.Layout.Override.SelectTypeRow = SelectType.None e.Layout.Override.ActiveRowAppearance.BackColor = Color.FromArgb(255, 255, 192) e.Layout.Override.CellPadding = 3 e.Layout.Override.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed ' hide columns that should not be used For Each col In cols col.CellActivation = Activation.NoEdit col.AllowRowFiltering = DefaultableBoolean.False col.Header.Caption = "" col.Header.Appearance.TextHAlign = HAlign.Left Select Case col.Key.ToLower Case "smnm" col.Header.Caption = "SP" Case "cnt_n" col.Header.Caption = "N" col.Width = 16 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right Case "totfrgross_n" col.Header.Caption = "Gross New" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case "avgfrgross_n" col.Header.Caption = "Avg Gross New" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case "cnt_u" col.Header.Caption = "U" col.Width = 16 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right Case "totfrgross_u" col.Header.Caption = "Gross Used" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case "avgfrgross_u" col.Header.Caption = "Avg Gross Used" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case Else col.Hidden = True End Select Next ' ' sub-total ' If band.Summaries.Exists("summary_totfrgross_n") = False Then band.Summaries.Add("summary_smnm", SummaryType.Formula, band.Columns("smnm"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_cnt_n", SummaryType.Sum, band.Columns("cnt_n"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_totfrgross_n", SummaryType.Sum, band.Columns("totfrgross_n"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_avgfrgross_n", SummaryType.Formula, band.Columns("avgfrgross_n"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_cnt_u", SummaryType.Sum, band.Columns("cnt_u"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_totfrgross_u", SummaryType.Sum, band.Columns("totfrgross_u"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_avgfrgross_u", SummaryType.Formula, band.Columns("avgfrgross_u"), SummaryPosition.UseSummaryPositionColumn) With e.Layout.Override .SummaryFooterCaptionVisible = DefaultableBoolean.False .SummaryFooterAppearance.BackColor = Color.White .SummaryValueAppearance.BackColor = Color.White End With For i As Integer = 0 To band.Summaries.Count - 1 With band.Summaries(i) .Appearance.TextHAlign = HAlign.Right .Appearance.FontData.Bold = DefaultableBoolean.True Select Case .Key.ToLower Case "summary_cnt_n", "summary_cnt_u" .DisplayFormat = "{0}" Case Else .DisplayFormat = "{0:#,##0.00}" End Select End With Next band.Summaries("summary_smnm").DisplayFormat = "Total:" band.Summaries("summary_avgfrgross_n").Formula = "[summary_totfrgross_n] / [summary_cnt_n]" band.Summaries("summary_avgfrgross_u").Formula = "[summary_totfrgross_u] / [summary_cnt_u]" End If End Sub
korazy said:in my summary the cells 'summary_avgfrgross_n' and 'summary_avgfrgross_u' are always blank.
If formula summaries are not working, then my guess is that you do not have an UltraWinCalcManager component on the form. You need this component to handle formula calculations.
If you do have one on the form and it still doesn't work, then my best guess is that the grid has somehow lots it's reference to the CalcManager, or perhaps never had one because you are creating the grid at run-time. If that's the case, then you need to set the CalcManager property on the grid to the CalcManager component on the form.
korazy said:My summary cells 'summary_totfrgross_n', 'summary_cnt_n', 'summary_totfrgross_u' and 'summary_cnt_u' do have proper values in them.
I'm not sure what this means. What exactly is wrong with the values in these summaries? What values do that have and what exactly is not proper about them?
I added the UltraWinCalcManager control to my grid, but the two summary cells in my grid with a formula show '#REF!'. What is wrong?
My two formulas in the grid summary:band.Summaries("summary_avgfrgross_n").Formula = "[summary_totfrgross_n] / [summary_cnt_n]"band.Summaries("summary_avgfrgross_u").Formula = "[summary_totfrgross_u] / [summary_cnt_u]"
Private Sub grdSummarySM_InitializeLayout(ByVal sender As System.Object, ByVal e As Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs) Handles grdSummarySM.InitializeLayout Dim band As UltraGridBand = e.Layout.Bands(0) Dim cols As ColumnsCollection = band.Columns Dim col As UltraGridColumn Dim calcManager As Infragistics.Win.UltraWinCalcManager.UltraCalcManager calcManager = New Infragistics.Win.UltraWinCalcManager.UltraCalcManager(Me.Container) e.Layout.Grid.CalcManager = calcManager ' Configure grid band.Override.AllowRowFiltering = DefaultableBoolean.False band.Override.RowFilterMode = RowFilterMode.AllRowsInBand band.UseRowLayout = True e.Layout.AutoFitStyle = AutoFitStyle.ResizeAllColumns e.Layout.BorderStyle = UIElementBorderStyle.Solid 'e.Layout.CaptionVisible = False e.Layout.GroupByBox.Hidden = True e.Layout.MaxColScrollRegions = 1 e.Layout.MaxRowScrollRegions = 1 e.Layout.Appearance.FontData.Name = "Arial" e.Layout.Appearance.FontData.SizeInPoints = 9 e.Layout.Scrollbars = Scrollbars.Vertical e.Layout.ScrollBounds = ScrollBounds.ScrollToFill e.Layout.ScrollStyle = ScrollStyle.Immediate e.Layout.Override.AllowColMoving = AllowColMoving.NotAllowed e.Layout.Override.AllowColSizing = AllowColSizing.None e.Layout.Override.AllowColSwapping = AllowColSwapping.NotAllowed e.Layout.Override.CellClickAction = CellClickAction.EditAndSelectText e.Layout.Override.HeaderClickAction = HeaderClickAction.SortMulti e.Layout.Override.RowSelectors = DefaultableBoolean.False e.Layout.Override.RowSizing = RowSizing.AutoFixed e.Layout.Override.SelectTypeCell = SelectType.None e.Layout.Override.SelectTypeCol = SelectType.None e.Layout.Override.SelectTypeRow = SelectType.None e.Layout.Override.ActiveRowAppearance.BackColor = Color.FromArgb(255, 255, 192) e.Layout.Override.CellPadding = 3 e.Layout.Override.SummaryDisplayArea = SummaryDisplayAreas.BottomFixed ' hide columns that should not be used For Each col In cols col.CellActivation = Activation.NoEdit col.AllowRowFiltering = DefaultableBoolean.False col.Header.Caption = "" col.Header.Appearance.TextHAlign = HAlign.Left Select Case col.Key.ToLower Case "smnm" col.Header.Caption = "SP" Case "cnt_n" col.Header.Caption = "N" col.Width = 16 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right Case "totfrgross_n" col.Header.Caption = "Gross New" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case "avgfrgross_n" col.Header.Caption = "Avg Gross New" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case "cnt_u" col.Header.Caption = "U" col.Width = 16 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right Case "totfrgross_u" col.Header.Caption = "Gross Used" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case "avgfrgross_u" col.Header.Caption = "Avg Gross Used" col.Width = 50 col.MinWidth = col.Width col.MaxWidth = col.MaxWidth col.CellAppearance.TextHAlign = HAlign.Right col.Format = "#,##0.00" Case Else col.Hidden = True End Select Next ' ' sub-total ' If band.Summaries.Exists("summary_totfrgross_n") = False Then band.Summaries.Add("summary_smnm", SummaryType.Formula, band.Columns("smnm"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_cnt_n", SummaryType.Sum, band.Columns("cnt_n"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_totfrgross_n", SummaryType.Sum, band.Columns("totfrgross_n"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_avgfrgross_n", SummaryType.Formula, band.Columns("avgfrgross_n"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_cnt_u", SummaryType.Sum, band.Columns("cnt_u"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_totfrgross_u", SummaryType.Sum, band.Columns("totfrgross_u"), SummaryPosition.UseSummaryPositionColumn) band.Summaries.Add("summary_avgfrgross_u", SummaryType.Formula, band.Columns("avgfrgross_u"), SummaryPosition.UseSummaryPositionColumn) band.Summaries("summary_avgfrgross_n").Formula = "[summary_totfrgross_n] / [summary_cnt_n]" band.Summaries("summary_avgfrgross_u").Formula = "[summary_totfrgross_u] / [summary_cnt_u]" With e.Layout.Override .SummaryFooterCaptionVisible = DefaultableBoolean.False .SummaryFooterAppearance.BackColor = Color.White .SummaryValueAppearance.BackColor = Color.White End With For i As Integer = 0 To band.Summaries.Count - 1 With band.Summaries(i) .Appearance.TextHAlign = HAlign.Right .Appearance.FontData.Bold = DefaultableBoolean.True Select Case .Key.ToLower Case "summary_cnt_n", "summary_cnt_u" .DisplayFormat = "{0}" Case Else .DisplayFormat = "{0:#,##0.00}" End Select End With Next band.Summaries("summary_smnm").DisplayFormat = "Total:" End If End Sub
A #REF error indicates that a reference in the formula could not be found. So either [summary_totfrgross_n] or [summary_cnt_n] could not be found. You might try changing the formula to reference only one of these at a time and see which one (or both) are failing.
If that does not help, then I recommend that you try to reproduce the issue in a small sample project that I can run and post it here so I can take a look. It's really hard to tell what's wrong from a code snippet.