A pivot table is blank when it loads for the first time. You need to drag and drop fields to various locations to construct the table. Follow these steps after loading a new pivot table:
- If not already displayed, click the Field List icon in the pivot table toolbar to open the Pivot Table Field List.
- This list contains all of the fields you specified for the view's entity as well as for any linked entities.
- See Figure 10.15 for an example.
Displaying the Field List
- Select fields from the Field List and drag them to the various locations in the pivot table, as desired. Note that you can stack multiple fields in the same area but you cannot add a field to more than one area of the pivot table. The available locations include:
- Drop Filter Fields Here: If you did not specify view filters or if you want to further refine the results in the pivot table, you can add fields here to filter the table results. For example, you might want to filter a table based on order date.
- Drop Row Fields Here: Drop one or more fields in this area to create row entries for the table. You can add multiple fields to create layered rows. For example, you can add the Company field to the left edge of the row area and then add a Person field to the right of the company. This creates top level rows for each company in the dataset and then sub rows for each person at a particular company. See Figure 10.16 for an example.
- Drop Column Fields Here: Drop one or more fields in this area to create columns entries for the table. As with rows, you can add multiple fields to create layered columns. For example, you might want to display order data by person based on the product that appears on an order's first order line.
- Drop Totals or Detail Fields Here: Drop one or more fields in this area to specify the details of the report. For example, you might want to drop the Grand Total field to display the Grand Total for each order associated with a particular person and product.
Pivot Table with Sample Fields
- Move or rearrange fields as necessary. The Fields added to the pivot table layout can be modified in a variety of ways. For example, they can be moved to other areas of the pivot table, removed entirely from the pivot table, or sorted, as described below:
- To remove a field, select its heading in the pivot table. Right click the field and then select Remove Field from the pop up menu. The field is removed from the pivot table area but remains in the Field List and can be added again later if needed.
- Some fields can be sorted, either ascending or descending, depending on their location on the pivot table. To sort a field, right click on the field and select either Sort Ascending or Sort Descending.
- Add calculations to the pivot table, as desired.
- Calculations can be performed on certain numeric pivot table fields, depending on where they appear in the pivot table (typically in the Total or Details area).
- To apply calculations, right click the field and select AutoCalc. Then select the appropriate calculation from the list of choices: Sum, Min, Max, or Count. Not all choices may be available for each field.
- In the example in Figure 10.17, a Sum of Grand Total calculation has been added to the detail tab to add the totals from all of the displayed orders. The calculated field is added to the Field List automatically.
Adding a Calculated Field
- Use the plus (+) and minus ( ) signs to expand and collapse items to display only specific data. These options are also available in the right click menu when you select one or more items in the table.
- Modify the layout for a particular field using the Command and Options dialog.
- For example, you might want to display order totals as currency amounts (as shown in Figure 10.18).
- To open the dialog, either select Commands and Options... from a right click menu or click the Commands and Options icon in the pivot table toolbar.
Modify Formatting
- Export or print the pivot table, as needed.
- See "Exporting Pivot Table Data to Excel" on page 378 for export instructions.
- See "Printing a View" on page 251 for printing instructions.
Info: The steps above provide any an overview of the configuration options that are available when working with a pivot table. Refer to the Microsoft Office 2003 Pivot Table component's Help documentation (accessible from the Help button in the pivot table toolbar) for more information how to use a pivot table and the Commands and Options dialog.
Also, see "Pivot Table Tutorial" on page 382 for instructions on how to create a sample pivot table.