I have a project estimation application that is currently in use where I have a grid displaying summary data that is using two group by columns. The final screen in the application is similar to a final estimated cost of a project grouped by a major category and sub categories. For example, materials, engineering and labor are the major categories and then each one has subcategories. The user can then adjust factors such as percentage mark up, percentage risk and sales commission to add to the estimate cost and arrive at a final sales price that is submitted to a potential customer. When the user makes a change to one of these factors I recalculate the results in the underlying data table and the data is updated in the wingrid. I am displaying a summary dollar total for each category and sub category and then a final grand total at the bottom of the gird.
I have been receiving complaints from the users that sometimes the sum of the sub totals and the grand total do not add up to the same value. Of course when one of the users tried to show me it appeared to work just fine and just wrote it off as user error. As if it meant anything I tried to explain to the user that the grid control automatically calculates the sub total and total values so I doubted very much that they would not match. But today I just saw it happen with my own eyes where a user updated the mark up percentage on an estimate and the subtotal and grand total did not add up. If I'm not mistaken the group by totals did not update, but the grand total updated and was correct.
In my last post I had a situation where the group by column was not displaying correctly using an ADO column with an expression as the source. I had to use the .Bands(0).SortedColumns.RefreshSort(True) method on each grid every time I added a new record for the grid to display correct. Using the same technique to get around that problem I am able to get the grid to correctly update and the subtotals and grand totals add up correctly.
As you might imagine this whole thing was a big surprise to me. I no longer feel comfortable assuming the wingrid is going to display the correct group by summary values unless I manually kick the grid every time something changes. I don't know if this has anything do with the problem I discussed in my last post, but it seems very coincidental. The groups in a wingrid don't appear to always update when the underlying data changes.
I'm running release v9.1.2009.2029.
Hi Steve,
How are you doing the summaries in your grid? Are you using the regular summary enums like sum? Or are you using formulas?
I can't explain how the grand total could be correct when the sub-totals are not. It would almost make sense if it was the other way around, and it's possible for something like that to happen temporarily when using formulas, since the formulas are calculated asynchronously. But it would only be temporary in that case and you would almost never see a delay unless you have a pretty large number of rows in the grid.
I've certainly never seen this happen before. I suspect you must be right and that it is somehow related to the other issue you mentioned with the calculated values in the data source.
In the WinGrid designer feature picker I set allow row summaries to true and then selected the sum option for my totals column. I then checked off Bottom Fixed and GroupBy Rows Footer in the summary display area. I wasn't doing anything fancy which was why I was surprised when I saw it happen.
I'm going to try and recreate the problem to make sure I was saying correctly that the subtotals were wrong and the total was right. I could have it backwards.
There seems to be bug in Group By summary calculation. And it's happening in 10.3 version. I have replied to other post to find an answer.
For more information, I am implementing custom summary and binding to the grid using BindingList<T>
I am now experiencing the what appears to be the same issue in 11.1 in VB and VS 2010. Was this ever recognized as a bug and fixed?
Sorry i have resolved the issue. I had a typo in the AfterCellUpdate sub, the summary name was wrong there so after fixing it the sub and grand totals work fine. I just wish now that I could find a good way to label each summary area separately and align the summary labels nicely ( e.g Sub Total and Grand Total) Any Suggestions?
Hi,
skalyniuk said:I just wish now that I could find a good way to label each summary area separately and align the summary labels nicely ( e.g Sub Total and Grand Total) Any Suggestions?
I'm not sure I understand what you are asking.
Hi Mike
Fair enough to. Please see the attached file and I think I have part of this worked out in that I now have the Sub totals and Grand Totals working and being formatted as I need them to be. However, the final hurdle is a problem with the auto sizing. I have the following code
e.Layout.Override.AllowRowSummaries = AllowRowSummaries.False ' Here is how you can add summary of a column in code. The retruned ' SummarySettings object has properties for specifying the appearance, ' visibility and where it's displayed among other things. Dim columnToSummarize As UltraGridColumn = e.Layout.Bands(0).Columns("Value") Dim summary As SummarySettings = e.Layout.Bands(0).Summaries.Add("ValueTotal", _ SummaryType.Custom, _ New DocumentTabSummary, _ columnToSummarize, _ SummaryPosition.UseSummaryPositionColumn, _ columnToSummarize) With summary .ShowCalculatingText = DefaultableBoolean.True .DisplayFormat = "Sub Total = {0:######.00}" .Appearance.TextHAlign = HAlign.Right ' SummaryDisplayArea property is expossed on the SummarySettings object as ' well allowing to control if and where the summary gets displayed on a ' per summary basis. .SummaryDisplayArea = SummaryDisplayAreas.Bottom End With ' Here is how you can add summary of a column in code. The retruned ' SummarySettings object has properties for specifying the appearance, ' visibility and where it's displayed among other things. Dim columnToTotal As UltraGridColumn = e.Layout.Bands(0).Columns("Value") Dim gsummary As SummarySettings = e.Layout.Bands(0).Summaries.Add("GrandTotal", _ SummaryType.Custom, _ New DocumentTabSummary, _ columnToTotal, _ SummaryPosition.UseSummaryPositionColumn, _ columnToTotal) With gsummary .ShowCalculatingText = DefaultableBoolean.True .DisplayFormat = "Grand Total = {0:######.00}" .Appearance.TextHAlign = HAlign.Right .SummaryDisplayArea = SummaryDisplayAreas.BottomFixed _ Or SummaryDisplayAreas.RootRowsFootersOnly End With ' By default UltraGrid does not display summary footers or headers of ' group-by row islands. To display summary footers or headers of group-by row ' islands set the SummaryDisplayArea to a value that has GroupByRowsFooter ' flag set.\ With e.Layout.Override .SummaryDisplayArea = e.Layout.Override.SummaryDisplayArea Or SummaryDisplayAreas.GroupByRowsFooter ' By default any summaries to be displayed in the group-by rows are displayed ' as text appended to the group-by row's description. You can set the ' GroupBySummaryDisplayStyle property to SummaryCells or ' SummaryCellsAlwaysBelowDescription to display summary values as a separate ' ui element (cell like element with border, to which the summary value related ' appearances are applied). Default value of GroupBySummaryDisplayStyle is resolved ' to Text. .GroupBySummaryDisplayStyle = GroupBySummaryDisplayStyle.SummaryCells ' Appearance of the summary area can be controlled using the ' SummaryFooterAppearance. Even though the property's name contains the ' word 'footer', this appearance applies to summary area that is displayed ' on top as well (summary headers). .SummaryFooterAppearance.BackColor = SystemColors.Info ' Appearance of summary values can be controlled using the ' SummaryValueAppearance property. .SummaryValueAppearance.BackColor = SystemColors.Window .SummaryValueAppearance.FontData.Bold = DefaultableBoolean.True ' Appearance of summary values that are displayed inside of group-by rows can ' be controlled using the GroupBySummaryValueAppearance property. Note that ' this has effect only when the GroupBySummaryDisplayStyle is set to SummaryCells ' or SummaryCellsAlwaysBelowDescription. .GroupBySummaryValueAppearance.BackColor = SystemColors.Window .GroupBySummaryValueAppearance.TextHAlign = HAlign.Right ' Caption's appearance can be controlled using the SummaryFooterCaptionAppearance ' property. .SummaryFooterCaptionAppearance.FontData.Bold = DefaultableBoolean.True ' By default summary footer caption is visible. You can hide it using the ' SummaryFooterCaptionVisible property. .SummaryFooterCaptionVisible = DefaultableBoolean.False ' SummaryFooterSpacingAfter and SummaryFooterSpacingBefore properties can be used ' to leave spacing before and after the summary footer. .SummaryFooterSpacingAfter = 1 .SummaryFooterSpacingBefore = 1 End With With e.Layout .Bands(0).SummaryFooterCaption = "Grand Totals:" .Override.RowSizing = RowSizing.AutoFree .Override.CellMultiLine = DefaultableBoolean.True .PerformAutoResizeColumns(False, PerformAutoSizeType.VisibleRows) End WithI had .PerformAutoResizeColumns(False, PerformAutoSizeType.AllRowsInBand)which I changed to .PerformAutoResizeColumns(False, PerformAutoSizeType.VisibleRows)and the sub totals 'row' now sizes as required.The problem is that the 'Grand Total' summary keeps getting re-sized and I have to widen the column to see the last few digitson the right after every cell update. Your help with this would be greatly appreciated.
Solved It
I have added two extra Summaries to the column to the left of where I am adding up the values. These 2 new summary in the column to the left do not show the 'value' just the heading. I have also added
.Appearance.TextTrimming = TextTrimming.None to the summary appearance because the default seems to be to TextTrimming.ellipsisThis took me ages to all figure out so here is all the code for someone elseHere is how you can add summary of a column in code. The retruned ' SummarySettings object has properties for specifying the appearance, ' visibility and where it's displayed among other things. Dim columnToSummarize As UltraGridColumn = oultraGrid.DisplayLayout.Bands(0).Columns("Value") Dim summary As SummarySettings = oultraGrid.DisplayLayout.Bands(0).Summaries.Add("ValueTotal", _ SummaryType.Custom, _ New DocumentTabSummary, _ columnToSummarize, _ SummaryPosition.UseSummaryPositionColumn, _ columnToSummarize) With summary .ShowCalculatingText = DefaultableBoolean.True .DisplayFormat = "{0:#####.00}" .Appearance.TextHAlign = HAlign.Right .Appearance.TextTrimming = TextTrimming.None .Appearance.TextVAlign = VAlign.Top ' SummaryDisplayArea property is expossed on the SummarySettings object as ' well allowing to control if and where the summary gets displayed on a ' per summary basis. .SummaryDisplayArea = SummaryDisplayAreas.Bottom End With Dim SubTotalcolumn As UltraGridColumn = oultraGrid.DisplayLayout.Bands(0).Columns("Finding") Dim subsumm As SummarySettings = oultraGrid.DisplayLayout.Bands(0).Summaries.Add("FindingTotal", SummaryPosition.UseSummaryPositionColumn, _ SubTotalcolumn) With subsumm .Lines = 1 .ShowCalculatingText = DefaultableBoolean.True .DisplayFormat = "Sub Total" .Appearance.TextHAlign = HAlign.Right .Appearance.TextTrimming = TextTrimming.None .Appearance.TextVAlign = VAlign.Top .SummaryDisplayArea = SummaryDisplayAreas.Bottom End With ' Here is how you can add summary of a column in code. The retruned ' SummarySettings object has properties for specifying the appearance, ' visibility and where it's displayed among other things. Dim columnToTotal As UltraGridColumn = oultraGrid.DisplayLayout.Bands(0).Columns("Value") Dim gsummary As SummarySettings = oultraGrid.DisplayLayout.Bands(0).Summaries.Add("GrandTotal", _ SummaryType.Custom, _ New DocumentTabSummary, _ columnToTotal, _ SummaryPosition.UseSummaryPositionColumn, _ columnToTotal) With gsummary .Lines = 1 .ShowCalculatingText = DefaultableBoolean.True .DisplayFormat = "{0:#####.00}" .Appearance.TextHAlign = HAlign.Right .Appearance.TextTrimming = TextTrimming.None .Appearance.TextVAlign = VAlign.Top .SummaryDisplayArea = SummaryDisplayAreas.BottomFixed _ Or SummaryDisplayAreas.RootRowsFootersOnly End With Dim Totalcolumn As UltraGridColumn = oultraGrid.DisplayLayout.Bands(0).Columns("Finding") Dim summ As SummarySettings = oultraGrid.DisplayLayout.Bands(0).Summaries.Add("FindingTotal", SummaryPosition.UseSummaryPositionColumn, _ Totalcolumn) With summ .Lines = 1 .ShowCalculatingText = DefaultableBoolean.True .DisplayFormat = "Grand Total" .Appearance.TextHAlign = HAlign.Right .Appearance.TextTrimming = TextTrimming.None .Appearance.TextVAlign = VAlign.Top .SummaryDisplayArea = SummaryDisplayAreas.BottomFixed _ Or SummaryDisplayAreas.RootRowsFootersOnly End With ' By default UltraGrid does not display summary footers or headers of ' group-by row islands. To display summary footers or headers of group-by row ' islands set the SummaryDisplayArea to a value that has GroupByRowsFooter ' flag set. With oultraGrid.DisplayLayout.Override .SummaryDisplayArea = oultraGrid.DisplayLayout.Override.SummaryDisplayArea Or SummaryDisplayAreas.GroupByRowsFooter ' By default any summaries to be displayed in the group-by rows are displayed ' as text appended to the group-by row's description. You can set the ' GroupBySummaryDisplayStyle property to SummaryCells or ' SummaryCellsAlwaysBelowDescription to display summary values as a separate ' ui element (cell like element with border, to which the summary value related ' appearances are applied). Default value of GroupBySummaryDisplayStyle is resolved ' to Text. .GroupBySummaryDisplayStyle = GroupBySummaryDisplayStyle.SummaryCells ' Appearance of the summary area can be controlled using the ' SummaryFooterAppearance. Even though the property's name contains the ' word 'footer', this appearance applies to summary area that is displayed ' on top as well (summary headers). .SummaryFooterAppearance.BackColor = SystemColors.Info ' Appearance of summary values can be controlled using the ' SummaryValueAppearance property. .SummaryValueAppearance.BackColor = SystemColors.Window .SummaryValueAppearance.FontData.Bold = DefaultableBoolean.True ' Appearance of summary values that are displayed inside of group-by rows can ' be controlled using the GroupBySummaryValueAppearance property. Note that ' this has effect only when the GroupBySummaryDisplayStyle is set to SummaryCells ' or SummaryCellsAlwaysBelowDescription. .GroupBySummaryValueAppearance.BackColor = SystemColors.Window .GroupBySummaryValueAppearance.TextHAlign = HAlign.Right ' Caption's appearance can be controlled using the SummaryFooterCaptionAppearance ' property. .SummaryFooterCaptionAppearance.FontData.Bold = DefaultableBoolean.True ' By default summary footer caption is visible. You can hide it using the ' SummaryFooterCaptionVisible property. .SummaryFooterCaptionVisible = DefaultableBoolean.False ' SummaryFooterSpacingAfter and SummaryFooterSpacingBefore properties can be used ' to leave spacing before and after the summary footer. .SummaryFooterSpacingAfter = 1 .SummaryFooterSpacingBefore = 1 End With
The issue is that the Grand Total is lined up with the other summaries only by the user with the mouse lining it up (expanding column width). Please refer to the attached screen.
The specific issue is that the Sub Totals are expanding as expected but the Grand Total row does not resize appropriately.
I am asking for your help to get the Grand Total row / cell to re-size to show the full data in the cell automatically.
Thank you
I'm still not sure what you are asking. The screen shot you posted here looks just fine to me. The Grand Total is lined up with the other summaries, which seems correct.
What are you trying to do?