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.
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.
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.
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.
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.