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