Customizing pivot report fields
After running a pivot report, you can customize which fields appear and how they are arranged. In a pivot-report, there are four areas where fields can reside:
- Row area and column area: these “dimensional” areas determine the structure of your pivot-report and also how data are grouped and sorted.
- Data area: these are the fields being summarized in the main body of the report.
- Filter area: (above the main body of the report): these fields do not participate directly in the results display, but are still available for filtering data.
There are two primary methods available for changing the arrangement of your pivot-report.
Drag-and-Drop Method
You can adjust fields directly from the report’s tab by dragging and dropping.
You can move a field from one area to another. You can change the order of fields within a particular area. You can also filter on any field (when you hover on the field, you’ll see a little filter icon appear).
Whenever you make a change, your report results are instantly updated; you can see the effect immediately.
Interactive Field List Method
You can access the Field List dialog box, which offers a separate dialog box for rearranging fields.
You can show the field list in either of two ways:
- From the Pivot Tools > Pivot Report Ribbon, choose “Field List”
- Right-click any field icon on the report and select Show Field List from the menu that appears.
The Field List dialog box shows the current state of the fields in your report including. Within the dialog box, you can drag fields among the areas outlined above to customize your report (the effect is the same as dragging the icons on the report), plus one additional area:
- Row Area: Fields whose values determine the rows
- Column Area: Fields whose values determine the columns
- Data Area: Fields currently being summarized based on the row and column structure
- Filter Area: Fields that are not currently being used to display data
- Hidden Fields: These fields are completely removed from the display and don’t appear in any of the above areas. You can get them back only via the Field List
The report is automatically updated as you make these changes, allowing you to interactively see how your changes impact the report itself. When you’re done customizing the report, simply close the Field List dialog box.
Adjusting the display order of pivot report field items
You can adjust the order of the field items displayed for a pivot report in two ways:
- You can drag-and-drop the items, moving them to any desired location. This can be done either directly on the report result or via the Field List dialog box.
- You can also right-click an item on the report, select Order from the menu that appears, and specify where you want to move the item (to the beginning of the list, one space to the left or right, or to the end of the list).
Using prefiltering
Prefiltering is when you restrict data so that it never becomes part of the report at all – it won’t even appear when you attempt to filter an individual field. This feature is accessed in two different ways:
1). From the Pivot Tools > Pivot Report Ribbon, click the “Prefilter” button.
2). Right-click any field icon and select Show Prefilter from the menu that appears.
The PivotGrid Prefilter dialog box then allows you to specify prefiltering rules.
