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:

1.      Click the Calculated Totals and Fields button (shown in Illustration 18) on the PivotTable toolbar.

Illustration 18: The Calculated Totals and Fields Button

2.      Select Create Calculated Detail Field from the dropdown options that appear.

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.

Illustration 19: The PivotTable Field List, with New Field, and the Associated Properties Box

3.      On the Calculation tab, in the text area below the Name text box, type the following:

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.

4.      Type "Net Sale" into the Name text box.

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

Illustration 20: The Properties Box, Calculation Tab


5.      Click the Format tab on the Properties dialog.

6.      In the Number format selector, select Currency.

7.      Click the Bold button in the Text Format section of the Format tab.

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

Illustration 21: The Properties Box, Format Tab

8.      Return to the Calculation tab.

9.      Click the Change button at the bottom of the Calculation tab.

10.  Close the Properties box and PivotTable Field List.