GroupOn Property

  • Article

GroupOn property as it applies to the PivotField object.

PivotFieldGroupOnEnum

PivotFieldGroupOnEnum can be one of these PivotFieldGroupOnEnum constants.
plGroupOnEachValue Specifies no grouping. The default setting.
plGroupOnPrefixChars Group text values by the first N characters of each value, where N is the GroupInterval property value. This setting is valid only for textual fields.
plGroupOnYears Group date or time values by years.
plGroupOnQtrs Group date or time values by quarters.
plGroupOnMonths Group date or time values by month.
plGroupOnWeeks Group date or time values by week.
plGroupOnDays Group date or time values by their respective day number
plGroupOnHours Group date or time values by their respective hour number.
plGroupOnMinutes Group date or time values by their respective hour number.
plGroupOnSeconds Group date or time values by their respective second number.
plGroupOnInterval Group numeric values by the interval specified in the GroupInterval property.

expression.GroupOn

expression Required. An expression that returns a PivotField object.

Remarks

Once you have set the GroupOn property, you can use the GroupInterval, GroupStart, and GroupEnd properties to further define how to group items in a field.

Setting this property affects the way in which values in each field are displayed on the row, column, and filter areas of a PivotTable list. The settings for this property do not affect how values are displayed in the detail area of a PivotTable list. However, the filter dropdown will reflect the GroupOn setting for a field in the detail area of a PivotTable list.

Setting this property of a PivotTable field clears its IncludedMembers and ExcludedMembers properties if they have been set.

GroupOn property as it applies to the GroupLevel and PageField objects.

Returns or sets a DscGroupOnEnum that represents the way GroupLevel objects or PageField objects are grouped. Read/write.

expression.GroupOn

expression Required. An expression that returns on of the objects listed above.

DscGroupOnEnum can be one of these DscGroupOnEnum constants.
dscDay
dscEachValue
dscHour
dscInterval
dscMinute
dscMonth
dscPrefix
dscQuarter
dscWeek
dscYear

Example

As it applies to the PivotField object.

This example groups the Age field of PivotTable1. Starting at age 15, a new group will be created for every five years until age 80. Then, the font, foreground, background, height and alignment of the resulting groups are formatted.

Sub AddGrouping()

    Dim vwView
    Dim ptConstants
    Dim pfGroupedField

    Set ptConstants = PivotTable1.Constants

    ' Set a variable to the active view of the PivotTable.
    Set vwView = PivotTable1.ActiveView
    
    ' Set a variable to the Age field.
    Set pfGroupedField = vwView.FieldSets("Age").Fields("Age")
    
    ' Set the GroupOn property so that the Age field will be
    ' grouped by the GroupInterval setting.
    pfGroupedField.GroupOn = ptConstants.plGroupOnInterval

    ' Create a new grouping for every five years.
    pfGroupedField.GroupInterval = 5

   ' Start the grouping at age 15.
    pfGroupedField.GroupStart = 15

    ' End the grouping at age 80.
    pfGroupedField.GroupEnd = 80
    
    ' Set the font for the field when it is grouped.
    pfGroupedField.GroupedFont.Bold = True

    ' Set the foreground color for the field when it is grouped.
    pfGroupedField.GroupedForeColor = "Black"

    ' Set the Background color for the field when it is grouped.
    pfGroupedField.GroupedBackColor = "Blue"

    ' Set the height for the field when it is grouped.
    pfGroupedField.GroupedHeight = 15

    ' Set the horizontal alignment for the field when it is grouped.
    pfGroupedField.GroupedHAlignment = ptConstants.plHAlignRight
    
End Sub

Applies to | GroupLevel Object | PageField Object | PivotField Object

See Also | GroupEnd Property | GroupInterval Property | GroupStart Property