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