Defining the Data Fields within the
PivotTable View
Remaining within the Queries section of the Database
window, let's take the following steps to begin construction of our new
PivotTable view.
1.
Click the new Customer_Orders_Query
to select it
2.
Click Open
on the Database window toolbar, as partially shown in Illustration 12.
Illustration 12: The Database Window (Relevant Portions)
3.
Select View --> PivotTable
View from the main menu, as shown in Illustration 13.
Illustration 13: Select View --> PivotTable View
After a few seconds, a blank PivotTable appears, with a floating PivotTable
Field List appearing in the foreground, as shown in Illustration 14.
Illustration 14: A Blank PivotTable View Appears
The blank PivotTable offers an excellent "conceptual"
view of the basic makeup of a PivotTable. With a PivotTable we can create views
of our data that strongly resemble, but far out power, a cross-tab query. The
PivotTable allows us to define the data values that we wish to occupy multiple
rows, columns, pages (or "layers") and summaries.
We can see above that, the
center of the table contains numeric data ("measures"), while the
rows and columns present (often hierarchical) dimensional data. We will see
illustrations of how these areas of the conceptual "map" are
populated in the steps that follow.
Let's define our view to effectively
present the data set returned from our new query.
4. If
the PivotTable Field List does not appear, initialize it clicking the Field
List button, shown in Illustration 15, on the toolbar.
Illustration 15: The PivotTable Field List Button
5. In
the Field List, click Country to highlight it.
6. Select
Filter Area in the selector box, to the right of the Add to button
(at the bottom of the Field List), as shown in Illustration 16.
Illustration 16: The PivotTable Field List with our
Selections
7. Click
the Add to button.
8. In
the Field List, click City to highlight it.
9. Select
Column Area in the selector box, to the right of the Add to button.
10. Click the Add to button.
11. In the Field List,
click Company Name to highlight it.
12. Select Column Area in
the selector box, to the right of the Add to button.
13. Click the Add to button.
This adds the Company Name field to the right of the City
field in the column section of the PivotTable.
14. In the Field List,
click Order Date by Month to highlight it.
15. Select Row Area in
the selector box, to the right of the Add to button.
16. Click the Add to button.
17. In the Field List,
click Order ID to highlight it.
18. Select Detail Data in
the selector box, to the right of the Add to button.
19. Click the Add to button.
20. Repeat steps 14
through 16 for the Product Name, Unit Price, Discount,
and Quantity fields.
A small sample of the PivotTable view is partially shown in Illustration
17.
Illustration 17: The PivotTable with Our Field Assignments
(Sample View)
Now let's make our PivotTable more useful by adding
calculated detail fields and totals.
» 
Comment and Contribute
