Subtotal Method

Inserts rows with summary data.

Syntax

[form!]vsFlexGrid.Subtotal Function As SubtotalSettings, [ GroupOn As Long ], [ TotalOn As Long ], [ Format As String ], [ BackColor As Color ], [ ForeColor As Color ], [ FontBold As Boolean ], [ Caption As String ], [ MatchFrom As Long ], [ TotalOnly As Boolean ]

Remarks

The Subtotal method adds subtotal rows which summarize the data in the control.

Subtotal rows are used for summarizing data and for displaying outlines. You may use the Subtotal method to create subtotal rows automatically, or the IsSubtotal property to create them manually.

Each subtotal row has a level that is used to indicate which column is being grouped. The subtotal level is also used for outlining. When you created subtotals using the Subtotal method, the level is set automatically based on the GroupOn parameter. When you create an outline manually, use the RowOutlineLevel property to set the outline level for each subtotal row.

Subtotal rows may be added at the top or at the bottom of the values being summarized. This is determined by the SubtotalPosition property. When creating outlines, you will typically use the SubtotalPosition property is used to place the subtotals above the data. When creating reports, you will typically use the SubtotalPosition property to place the subtotals below the data.

The parameters for the Subtotal method are described below:

Function As SubtotalSettings

This parameter specifies the type of aggregate function to be used for the subtotals. Valid settings are:

 

Constant

Value

Description

flexSTNone

0

Outline only, no aggregate values

flexSTClear

1

Clear all subtotals

flexSTSum

2

Sum

flexSTPercent

3

Percent of total sum

flexSTCount

4

Row count

flexSTAverage

5

Average

flexSTMax

6

Maximum

flexSTMin

7

Minimum

flexSTStd

8

Standard deviation

flexSTVar

9

Variance

flexSTStdPop

10

Standard Deviation Population

flexSTVarPop

11

Variance Population

 

GroupOn As Long  (optional)

This parameter specifies the column that contains the categories for calculation of a subtotal. By default, the control assumes that all data is sorted from the leftmost column to the column specified as GroupOn. Consequently, a subtotaling break occurs whenever there is a change in any column from the leftmost one up to and including the column specified as GroupOn.

To create subtotals based on a column or range of columns that does not start with the leftmost column, use the MatchFrom parameter. If MatchFrom is specified, the control generates subtotal line only on a change of data in any column between and including column MatchFrom and GroupOn.

For example, to subtotal values in column 3 of the control whenever there are changes in column 2 only, use

    fg.Subtotal flexSTSum, 2, 3, , , , , 2

TotalOn As Long  (optional)

This parameter specifies the column that contains the values to use when calculating the total.

Format As String (optional)

This parameter specifies the format to be used for displaying the results. The syntax for the format string is similar but not identical to the syntax used with Visual Basic's Format command. For a detailed description of the syntax used to specify formats, see the ColFormat property. If this parameter is omitted, the column's default format (defined by the ColFormat property) is used.

BackColor, ForeColor As Color  (optional)

These parameters specify the colors to be used for the cells in the subtotal rows.

FontBold As Boolean  (optional)

This parameter specifies whether text in the subtotal rows should be boldfaced.

Caption As Variant  (optional)

This parameter specifies the text that should be put in the subtotal rows. If omitted, the text used is the function name plus the category name (for instance, "Total Widgets"). If supplied, you may add a "%s" marker to indicate a place where the category name should be inserted (for example, "The %s Count").

MatchFrom As Variant  (optional)

When deciding whether to insert a subtotal row between two adjacent rows, the control compares the values in columns between MatchFrom and GroupOn. If any of these cells are different, a subtotal row is inserted. The default value for MatchFrom is FixedCols, which means all columns to the left of and including GroupOn must match, or a subtotal row will be inserted. If you set MatchFrom to the same value as GroupOn, then subtotal rows will be inserted whenever the contents of the GroupOn column change.

TotalOnly As Boolean (optional)

By default, the control will copy the contents of all columns between MatchFrom and GroupOn to the new subtotal row, and will place the calculated value on column TotalOn. If you set the TotalOnly parameter to True, the control will not copy the contents of the rows. The subtotal row will contain only the title and the calculated value.

The example below shows how to use the Subtotal method.

    ' this assumes we have a populated grid fa with

    ' 4 columns: product, employee, region, and sales

    fg.ColFormat(3) = "$(#,###.00)" ' set format for calculated totals

    fg.Subtotal flexSTClear         ' remove old values

    ' calculate subtotals (the order doesn't matter)

    ' (sales values to be added are in column 3)

    ' col 0: product

    fg.Subtotal flexSTSum, 0, 3, , vbRed  

    ' col 1: employee

    fg.Subtotal flexSTSum, 1, 3, , vbGreen

    ' col 2: region

    fg.Subtotal flexSTSum, 2, 3, , vbBlue 

    ' total on a negative column to get a grand total

    fg.Subtotal flexSTSum, -1, 3, , vbblue, vbwhite, True

The parameters in the Subtotal method allow a great deal of customization. The example below shows how the Caption and TotalOnly parameters can be used to generate report-type subtotals:

    fg.ColFormat(3) = "$(#,###.00)" ' set format for calculated totals

    fg.Subtotal flexSTClear         ' remove old values

    ' calculate subtotals (the order doesn't matter)

    ' (sales values to be added are in column 3)

    ' col 0: product

    fg.Subtotal flexSTSum, 0, 3, , vbRed  ,,," TotPrd %s",,True  

    ' col 1: employee

    fg.Subtotal flexSTSum, 1, 3, , vbGreen,,,"  TotEmp %s",,True 

    ' col 2: region

    fg.Subtotal flexSTSum, 2, 3, , vbBlue ,,,"   TotRgn %s",,True

    ' total on a negative column to get a grand total

    fg.Subtotal flexSTSum, -1, 3, , vbblue, vbwhite, True

See Also

VSFlexGrid Control