About grouping items
From Office PivotTable
You can group items by specifying the type of interval and the size of the interval. For example, you can group a field with date values into different months, and specify the interval as 2, to create groups such as Jan-Feb, Mar-Apr, and so on.
The type of interval you can specify for a field depends on the data type of the field.
- Numeric fields You can group in intervals of any integer, such as 1, 2, 5, 100, and so on. For example, you can group the EmployeeID field into groups of 1-100, 101-200, and so on.
- Date and time fields You can group in intervals such as days, weeks, and quarters. For example, you can group the values in the ShippedDate field into months to show data for orders shipped in January, orders shipped in February, and so on.
- Text fields You can group based on the first n characters of the individual items. For example, you can group the LastName field by first letter to create groups, such as A, B, and so on.
You can specify the start and end values for the grouping range. For example, if you specify the start range as 01-Jul-1999 while grouping the ShippedDate field in weekly intervals, the following groups will be created:
<01-Jul-1999, 01-Jul-1999, 08-Jul-1999, ...
Dates prior to 01-Jul-1999 are grouped into a single group titled <01-Jul-1999.
If you also specify the end value as 31-Dec-1999, the following groups will be created:
<01-Jul-1999, 01-Jul-1999, 08-Jul-1999, ... 31-Dec-1999, >31-Dec-1999
Dates that fall after 31-Dec-1999 are grouped into a single group titled >31-Dec-1999.
You can randomly select items from a row or column field and group them into higher-level groups. For example, you can select from the Promotions row field all the promotions that run for a specific period and create a group. This would add a new row field named Promotions 2 above the Promotions row field with two members: Group1 and Other. You can change the caption of Group1 to Fixed and Promotions 2 to Category in the Commands and Options dialog box.
The Other group will contain all items that you did not include in the Fixed custom group. You can then select all the popular promotions from the Other group and create a new custom group that will be captioned Popular. After you do this, the Category field will have three members: Fixed, Popular, and Other.
The following illustration shows how the PivotTable list will look after the captions of the custom field and groups have been changed.
A custom group field. Contains custom groups as its items and appears as the parent of the field whose items you grouped.
Custom groups. Appear as the parent of items you explicitly selected to create the group.
The Other group is the parent of the items you did not assign to any specific custom group.
Creating and deleting custom groups and custom group fields
-
You cannot group items that belong to different fields or items that belong to parent items into a custom group.
-
When you create the first custom group for a field, a custom group field is automatically added as the field's parent. Similarly, when you delete the last custom group for a field, the custom group field is automatically deleted.
-
When you delete a custom group that is not the last custom group in the field, the deleted group's members automatically move to the Other group.
-
The groups you create are persisted, but exist only on the client side. So any groups you create are available on the Web page you create, but are not part of the source data.
Moving a custom group field to the detail area
When you move a fieldset that has custom group fields to the detail area or filter area, you will not see the custom group fields. However, the bound field will show the custom group hierarchy in the drop-down list, so you can filter data by selecting custom groups or individual values. When you move the fieldset back to a row or column area, the custom group fields will show in the fieldset.
Adding and removing nested custom groups
You can select two or more custom groups to create a higher-level grouping. For example, you can group the members of the Category field into two groups: high priority promotions and low priority promotions.
The following illustration shows how the row area will look with nested custom groups.
A custom group field that appears as the parent of the Category custom group field.
You can also remove a lower-level custom group. The following illustration shows what the data will look like after the Category field has been removed.
