The BeeGrid control can group successive rows that have the same value in the grouping column. Grouping column is a column that is specified, with some other group settings, by the SGGroup object. Each group has a group heading row and each group can have a group footer row. Group header and footer rows display values defined by the SGGroup properties. Among other things, group header and footer can show results from various calculation and formulas. 

Groups can be created from code and/or you can allow users to drop column headers into the group-by box.

Creating group definitions in code

Use the Add method of the SGGroups collection to create new group definition. With Add method parameters, you can control whether grouping column is going to be sorted (SortOrder parameter) and whether grouping should occur before Add method finishes (DoRefresh parameter). Typically, if recordset is already sorted, you do not want to sort it again. Also, if you are adding more than one group, it's more efficient to regroup only when last group is added. Note that you can use the RefreshGroups method to regroup rows any time later.

Group definitions can be persisted with the SGGroups collection's GetDefinition and SetDefinition methods. Use the GetDefinition method to retrieve XML presentation of the current group settings. Returned string can be saved to a file, database or some other persistent storage. Later, you can use the SetDefinition method to recreate group settings.

Group-by box

To allow users to group data, you should enable the group-by box. The group-by box is a rectangle area at the top of the grid into which a user can drop a column header. To enable group-by box, set GroupByBoxVisible property to True. To change text displayed in the group-by box, use the GroupByBoxText property. Built-in style "GroupByBox", can be used to change box's appearance. For example, the code below changes the box's background color:

SGGrid1.Styles("GroupByBox").BackColor = RGB(240, 255, 255)

When a column header is dropped into the group-by box, two events gives you an opportunity to control user actions. Before a group is added, deleted or moved, the BeforeGroupChange event is fired. In this event's handler, you can enable or disable sorting (if recordset is already sorted), change sorting parameters, show or hide group footer and eventually, cancel user actions. If an action was not canceled, the AfterGroupChange event occurs. In this event you can further customize attributes of the newly created group, or change attributes of the un-grouped column (e.g. hide it).

Group header and footer text

Which data is displayed in the group header and footer is controlled by the SGGroup object's HeaderTextSource and FooterTextSource properties. Following tables shows possible combinations.

HeaderTextSource value	What is displayed in the group header?
sgGrpHdrEmpty	Nothing.
sgGrpHdrColCaptionAndValue	Value of the Caption property and grouping value are separated by a semicolon (:).
sgGrpHdrCaptionAndValue	Value of the HeaderCaption property and grouping value are separated by a semicolon (:).
sgGrpHdrCaption	Value of the HeaderCaption property.
sgGrpHdrFormula	Result of the formula contained in the HeaderFormula property.
sgGrpHdrFireFetchText	Data returned from the FetchGroupHeaderData event.

FooterTextSource value	What is displayed in the group footer?
sgGrpFooterEmpty	Nothing.
sgGrpFooterCaption	Value of the FooterCaption property.
sgGrpFooterFormula	Result of the formula contained in the FooterFormula property.
sgGrpFooterFireFetchText	Data returned from the FetchGroupFooterText event

For more info on grid formulas, please click here. 

Group header pictures

You can show a picture inside the group header row. To control which picture is displayed when group is collapsed, use the PictureCollapsed property. For picture on expanded group, use the PictureExpanded property. Following table shows possible values for these properties. 

PictureCollapsed or PictureExpanded value	Which picture is displayed?
Empty variant	No picture is displayed.
Picture object	Picture represented by this picture object
Integer value smaller then sgbipFirst	Picture from the image list assigned to the SGGrid.ImageList.
sgbipFirst + X	Picture from the built-in image list.
sgGroupHeaderColumnPicture	Picture from the grouped column's HeadingPicture property.
sgGroupHeaderFetchPicture	Picture returned from the FetchGroupHeaderData event.

 

Customizing group header and footer appearance

To customize group header and footer appearance, change attributes of the "GroupHeader" and "GroupFooter" built-in styles. If you want to apply different styles to different group headers and footers, you can set the FetchHeaderStyle and/or FetchFooterStyle properties to True and handle the FetchGroupHeaderStyle and FetchGroupFooterStyle events.

Enumerating groups

To enumerate groups, you can use SGRows or SGGroupHeadings collections. When you use SGRows collection, check the row object's Type property. For a group header it will have sgGroupHeader value.

Grid's GroupHeadings property is a collection of all root level group headings. Each heading in this collection is represented with one SGGroupHeading object. Since the SGGroupHeading object has collections of child groups and rows, you can look at the grid's GroupHeadings property as o root of the group hierarchy. The code below recursively enumerates all groups in the grid:

Private Sub EnumGroups(ghs As SGGroupHeadings)
   Dim gh As SGGroupHeading
   
   For Each gh In ghs
      ' Print group header text
      Debug.Print gh.Row.Cells(0).Value & " (" & gh.ChildRows.Count & ")"
      EnumGroups gh.Headings
   Next
End Sub