How to: Group records and show totals

Group customers by country and city, with the record count and the turnover total on every group row and in the footer, and pass the result to Excel exactly as it's shown.

Requires WKL Forms Object Library 1.23 or later.

GroupFields lists the fields to group by, starting with the outermost; DESC after a field sorts its groups in descending order. A column's Summary determines what its footer and group rows show (count, sum, minimum, maximum, or average), and ShowFooter shows the footer. Users group records by dragging column headers onto the group panel (ShowGroupPanel).

With Me!Grid1.Object
    .Columns("Turnover").Summary = wsuSum
    .ShowFooter = True
    .GroupFields = "Country;City DESC"
    .ExpandAll
    Debug.Print .SummaryValue("Turnover")          ' the footer value
    .ExportToXlsx "Customers.xlsx"                 ' next to the database
End With

• Grouping is only a view: the records aren't read again, and the selection is kept. A group field that isn't a column is read once along with the records.
• A group row shows the field, the value, and the record count (“City: Boston (3)”); Null and empty text form a single group, shown as (empty). Group rows can't be edited, and their summaries count only the group's records.
• On the group panel, clicking a level reverses the order of its groups, dragging a level changes the order of the levels, and dragging it off the panel removes it. The panel's shortcut menu offers the sort order and expanding or collapsing all groups; a column header's shortcut menu offers grouping by that column and removing the grouping.
• The workbook has one outline level per group level; the records of a collapsed group are hidden rows, and every group summary is a SUBTOTAL formula over the group's rows. The footer's SUBTOTAL skips them, so nothing is counted twice, even after filtering in Excel. With ExportRows = visible, the records of collapsed groups are omitted.
• Events: BeforeGroupExpand, GroupExpand, BeforeGroupCollapse, and GroupCollapse report the user expanding and collapsing groups.

• API reference: GroupFields
• How to: Export the list to Excel
• WklGrid overview