MS Access for the Business Environment: Create a PivotTable View in Access - Page 4

July 1, 2003

Adding Calculated Detail Fields and Calculated Totals

While we can bring much of the data we need into the PivotTable simply by selecting the appropriate data fields, as we have seen above, we can also present derived detail data via calculated fields. We can also present calculated totals. These calculations can make our PivotTable far more useful to information consumers, as we will see by taking the following steps:

The PivotTable Field List appears, with a new field, whose default name is Calculated, appearing at its bottom, as shown in Illustration 19. In addition, the Properties dialog for the new Calculated field appears, typically in front of the PivotTable Field List, defaulted to show the Calculation tab.

UnitPrice * Quantity - Discount

NOTE: We can use the Insert Reference To button, after selecting each of the fields in sequence (inserting the appropriate operators), as an option for inputting our calculation. This is perhaps cumbersome, but can often help us to ensure that the fields are represented correctly; an example here would be Unit Price, which appears in the PivotTable Field List as "Unit Price" (with a space between the words). If we simply type in "Unit Price," our calculation results in an error. However, if we build the calculation using the Insert Reference To button (a form of "expression builder" approach), the correct format of "UnitPrice" (no space between words) is input - saving us the time, ultimately, in clearing the error.

The Properties box, Calculation tab, appears as shown in Illustration 20.

The Properties box, Format tab, appears as shown in Illustration 21.