2.2.5.3.7 Grouping

Grouping is used to combine a set of cache items (section 2.2.5.3.6), typically ones that are related in some logical way. 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.3.5) that contains the cache items 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.

Numeric grouping is specified by records in the PivotCache Storage part (section 2.1.7.12) that conform to the GRPSXOPER (section 2.1.7.12) and SXRANGE (section 2.1.7.12) rules.

For numeric grouping, there is only one cache field associated with the grouping and it serves as both the parent grouping cache field and the base cache field. The numeric grouping is specified by the fRangeGroup and fNumField fields of the SXFDB record (section 2.4.283) associated with the cache field being equal to 1. The fHasParent field of the SXFDB record MUST be 0.

The cache items that specify the groups are specified by SXString records (section 2.4.304) that follow the SXFDB record. The grouping criteria is specified by the SxRng record (section 2.4.300) that follows the SXFDB record. The iByType field of the SxRng record MUST be 0. For more details, see section 2.4.300.

Date grouping is specified by records in the PivotCache Storage part that conform to the GRPSXOPER and SXRANGE rules.

For date grouping, there can be up to seven levels of grouping hierarchy. The grouping level for a cache field is specified by the iByType field of the SxRng record that follows the SXFDB record which specifies that cache field. The cache field with the lowest iByType value has the finest level of detail, the cache field with the next lowest iByType value has the next finest level of detail, and so on. Each cache field in the hierarchy MUST have an SxRng record with a unique iByType value.

The cache field corresponding to the finest level of detail of date information included serves as both a parent grouping cache field and the base cache field. Other parent grouping cache fields specify additional levels in the hierarchy. The date grouping is specified by the fRangeGroup and fNumField fields of the SXFDB record associated with the cache field being equal to 1 and 0 respectively for all cache fields in the grouping. The ifdbBase field of each SXFDB record associated with the date grouping, except for the SXFDB record corresponding to the base cache field, MUST specify a cache field index to the SXFDB record corresponding to the lowest level of the hierarchy.

The cache items that specify the groups are specified by SXString record that follow the SXFDB record for the parent grouping cache fields. The grouping criteria is specified by the SxRng record that follows the SXFDB record. The iByType field of the SxRng record MUST be greater than or equal to 1 (Seconds) and less than or equal to 7 (Years). For more details, see section 2.4.300.

Discrete grouping is specified by the GRPSXOPER rule, the SxIsxoper record (section 2.4.290), and the Continue records (section 2.4.58) in the PivotCache Storage part.

For discrete grouping, a hierarchy of parent grouping cache fields can exist, where each parent grouping cache field combines the cache items of the cache field at the next lower level. The discrete grouping is specified by the fRangeGroup field of the SXFDB record associated with the cache field being equal to 0 and the csxoper field of the SXFDB record being greater than 0. The ifdbBase field of the SXFDB record specifies a cache field index to the base cache field at the lowest level of the grouping hierarchy. The ifdbParent field of the SXFDB record specifies a cache field index to the parent grouping cache field at the next higher level of the hierarchy. If there is no higher level, then the fHasParent field of the SXFDB record MUST be 0 and ifdbParent MUST be ignored.

The cache items that specify the groups are specified by records that conform to the GRPSXOPER rule following the SXFDB record. The mapping between the cache items in the lower level cache field and the cache items in the parent grouping cache field is specified by the SxIsxoper record following the SXFDB record for the parent grouping cache field. The rgSxIsxoper field in the SxIsxoper record contains an array element for each cache item in the lower level cache field. The value of the array element is the index of the cache item in the parent grouping cache field that the cache item in the lower level cache field is grouped by. For more details, see section 2.4.290.

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

Numeric grouping combines numeric cache items into ranges of values. 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 report (section 2.2.5) illustrates numeric grouping applied to the "Age" cache field. 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 into date ranges.

One to seven parent grouping cache fields 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 parent grouping cache fields and is considered the base cache field of the parent grouping cache fields. For date grouping, the base cache field represents the finest level of detail of date and time information. The following levels of detail of date information are available, each corresponding to one cache field:

• Seconds

• Minutes

• Hours

• Days

• Months

• Quarters

• Years

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 can be too detailed for some analytical purposes. With date grouping, a more useful higher level summary can be created. The following PivotTable report illustrates the result of applying date grouping to the "Date" cache field and including two levels of grouping ("Years" and "Quarters"). In this example, the "Quarters" cache field represents the finest level of date information included and is therefore the base cache field for this date grouping. The "Years" cache field is a parent 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, where the start date is specified by the first SXNum record (section 2.4.296) following the SxRng record. The items ">12/13/2008" in the two cache fields represent dates after 12/13/2008, where the end date is specified by the second SXNum record following the SxRng record.

Figure 9: PivotTable report with date groups

Discrete grouping combines specifically selected cache items into groups. When discrete grouping is applied to a cache field, a separate parent grouping cache field is created and the cache field that the grouping is applied to will become the base cache field for that parent grouping cache field. Multiple parent grouping cache fields can exist for one base cache field, forming a hierarchy of parent grouping cache fields. A parent grouping cache field higher in the hierarchy is considered the parent of the cache field just below it in the hierarchy. For a parent grouping cache field, each cache item in the cache items collection represents one group.

For example, consider the following PivotTable report listing sales by state in the Unites 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 illustrates the result of applying six groups ("Group1" through "Group6") to the cache field 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 in the parent grouping cache field, combines states in the same geographical area.

Figure 11: PivotTable report with state groups