2.2.5.2.4 Grouping

Grouping is used to combine a set of cache items (section 2.2.5.2.3) into a group. There are three different types of grouping: numeric grouping, date grouping, and discrete grouping. Numeric grouping combines numeric cache items into ranges of values. Date grouping combines date cache items into date ranges. Discrete grouping combines specifically selected cache items into groups.

The cache field (section 2.2.5.2.2) that contains the cache items (section 2.2.5.2.3) that are to be grouped is called the base cache field. The resultant cache field that contains the groups of cache items is called the parent grouping cache field. Each group of cache items in the base cache field is associated with a single cache item in the parent grouping cache field. Often cache items in parent grouping cache fields can be further grouped, creating a hierarchy of parent grouping cache fields. The base cache field is at the lowest level of the hierarchy.

Grouping is specified by a sequence of records that conform to the PCDFGROUP rule (defined in section 2.1.7.38).

Numeric grouping and date grouping are specified by records in the PivotCache Definition part (section 2.1.7.38) that conform to the PCDFGRANGE rule (defined in section 2.1.7.38).

Discrete grouping is specified by records in the PivotCache Definition part that conform to the PCDFGDISCRETE rule (defined in section 2.1.7.38).

A numeric grouping or date grouping cache field (section 2.2.5.2.2) is specified by the presence of a BrtBeginPCDFGRange (section 2.4.134) record following the BrtBeginPCDField (section 2.4.136) record associated with that cache field. For a numeric grouping cache field, the iByType field of the associated BrtBeginPCDFGRange record is equal to 0. For a date grouping cache field, the iByType field of the BrtBeginPCDFGRange record is greater than or equal to 1 and less than or equal to 7.

A discrete grouping cache field (section 2.2.5.2.2) is specified by the presence of a BrtBeginPCDFGDiscrete (section 2.4.132) record following the BrtBeginPCDField record associated with the cache field.

The ifdbBase field of the BrtBeginPCDFGroup (section 2.4.135) record that follows the BrtBeginPCDField record specifies a cache field index to the base cache field for a grouping cache field.

The ifdbParent field of the BrtBeginPCDFGroup record that follows the BrtBeginPCDField record specifies a cache field index to the parent grouping cache field of a grouping cache field or of a base cache field.

For numeric grouping, there is only one cache field (section 2.2.5.2.2) associated with the grouping and it serves as both the grouping cache field and the base cache field. Therefore, for a numeric grouping cache field, the ifdbBase field of the BrtBeginPCDFGroup record that follows the BrtBeginPCDField (section 2.4.136) record MUST specify a cache field index to that same BrtBeginPCDField record. For numeric grouping, the ifdbParent field of the BrtBeginPCDFGroup record MUST be -1.

For date grouping, there can be up to seven levels of grouping hierarchy. The grouping level for a cache field (section 2.2.5.2.2) is specified by the iByType field of the BrtBeginPCDFGRange record. The cache field with the lowest iByType value is at the lowest level of the hierarchy, the cache field with the next lowest iByType value is at the next lowest level of the hierarchy, and so on. See the specification of the BrtBeginPCDFGRange record for the list of levels of date grouping hierarchy. Each cache field in the hierarchy MUST have an BrtBeginPCDFGRange record with a unique iByType value.

The cache field (section 2.2.5.2.2) corresponding to the lowest level of the date grouping hierarchy serves as both a grouping cache field and the base cache field. Therefore, in the cache field corresponding to the lowest level of the date grouping hierarchy, the ifdbBase field of the BrtBeginPCDFGroup (section 2.4.135) record that follows the BrtBeginPCDField record MUST specify a cache field index to that same BrtBeginPCDField record.

The cache items (section 2.2.5.2.3) of a grouping cache field (section 2.2.5.2.2), each corresponding to a single group, are specified by the sequence of records that conform to the PCDFGITEMS rule (defined in section 2.1.7.38).

For discrete grouping, the mapping between each group in a grouping cache field (section 2.2.5.2.2) and the cache items cache items (section 2.2.5.2.3) of the base cache field that are in each group, is specified by the PCDFGDISCRETE rule (defined in section 2.1.7.38) in the following way: there MUST be one BrtPCDIIndex (section 2.4.730) record corresponding to every cache item in the cache items collection of the base cache field. Each BrtPCDIIndex record specifies a cache item in the PCDFGITEMS rule that is the parent grouping cache item of the cache item in the cache items collection for the base cache field with the same index as that BrtPCDIIndex record in the PCDFGDISCRETE rule.

The following paragraphs explain the three different types of grouping and provide examples of them.

Numeric grouping combines numeric cache items (section 2.2.5.2.3) into ranges. For example, consider the following PivotTable report where the number of people (represented by "Count of Name") of a certain age are listed.

Figure 6: PivotTable report with ages

Analysis of specific ages might not be particularly meaningful. Instead, looking at age groups can be more interesting. The following PivotTable (section 2.2.5) report shows numeric grouping applied to the "Age" cache field (section 2.2.5.2.2). In this example, the numeric grouping is set to start at 0, end at 100 and have groups of 20 years.

Figure 7: PivotTable report with age groups

Date grouping is similar to numeric grouping and is used to group cache items (section 2.2.5.2.3) into date ranges.

One to seven grouping cache fields (section 2.2.5.2.2) can exist when date grouping is applied to a cache field, each corresponding to a different level of detail of date and time information. The cache field to which the date grouping is originally applied is included in the set of grouping cache fields and is considered the base cache field of the grouping cache fields. For date grouping, the base cache field represents the lowest level of the date grouping hierarchy. The following levels of detail of date information are available, each corresponding to one cache field:

• Years

• Quarters

• Months

• Days

• Hours

• Minutes

• Seconds

For example, consider the following PivotTable report where the number of sales is listed for each individual date.

Figure 8: PivotTable report with dates

This information could be too granular for some analytical purposes. With date grouping, a more useful higher level summary can be created. The following PivotTable report shows the result of applying date grouping to the "Date" cache field (section 2.2.5.2.2) and including two levels of grouping ("Years" and "Quarters"). In this example, the "Quarters" cache field represents the lowest hierarchical level of date information included and is therefore the base cache field for this date grouping. The "Years" cache field is a grouping cache field with the "Quarters" cache field as its base cache field. The items "<1/1/2007" in the two cache fields represent dates before 1/1/2007, the start date specified by the xnumStart field of the BrtBeginPCDFGRange record. The items ">12/13/2008" in the two cache fields represent dates after 12/13/2008, the end date specified by the xnumEnd field of the BrtBeginPCDFGRange record.

Figure 9: PivotTable report with date groups

Discrete grouping combines specifically selected cache items (section 2.2.5.2.3) into groups. When discrete grouping is applied to a cache field (section 2.2.5.2.2), a separate grouping cache field is created and the cache field that the grouping is applied to is the base cache field for that grouping cache field. Multiple grouping cache fields can exist for one base cache field, forming a hierarchy of grouping cache fields. A grouping cache field higher in the hierarchy is considered a parent grouping cache field of the grouping cache field or base cache field immediately following it in the hierarchy. For a grouping cache field, each cache item in the cache items collection represents one group.

For example, consider the following PivotTable report listing sales by states in the United States.

Figure 10: PivotTable report with state names

Discrete grouping can be used to group sets of states, for example, into geographical areas. The following PivotTable report shows the result of applying six groups ("Group1" through "Group6") to the cache field (section 2.2.5.2.2) representing states. The cache field representing states is considered the base cache field for the discrete grouping in this example. Each group in the example, represented by a cache item (section 2.2.5.2.3) in the grouping cache field, combines states in the same geographical area.

Figure 11: PivotTable report with state groups