Layout and Navigation of the PivotTable Report
The PivotTable
report is composed of the four general sections. We exploit the power of
OLAP in our PivotTable report by simply juxtaposing our
dimensions in the sections in such a way as to present data in the desired
combinations - at the "intersects" of the dimensions, as it were. As
we drag and drop the dimensions and their members into different positions, the
measures we have placed in the Data Items section change to match the
new placement of the combinations. Values are therefore presented within the
context of the axes. The four sections of the PivotTable report are
shown in Illustration 11.
Dimensions and measures are presented as items
on the PivotTable Field List, which, for our current example, appears in
Illustration 12.
Illustration
12: The Items of the PivotTable Field List
As we can see, each
item is paired with one of two types of icons that represent dimensions
and measures respectively. The dimension icons appear as tiny "reports"
or "tables"; the measure icons contain a characteristic "01 10"
pattern.
The PivotTable Field
List items are the main ingredients of the Excel PivotTable
report. The PivotTable Field List can be anchored to either side of the
Excel window ("docked") simply by dragging it to the desired
location, making it a fixed target (something I find easier to handle than the "floating"
approach - I have docked mine in illustrations of the PivotTable Report depicted
throughout the article), and can be made to disappear and reappear easily with
the rightmost button (default position) on the PivotTable toolbar. The
same is true for the PivotTable toolbar, except that it can be docked at
the top, bottom, or sides of the window, along with the other toolbars, and can
be retrieved from hiding with the View > Toolbars >
PivotTable selection sequence from
the top menu.
Browsing Our Cube Data
A PivotTable report
is highly flexible in that it serves as both a browser and a report
writer. As we have seen, the "candidate" dimension and
measure components of the PivotTable report appear on the PivotTable
Field List. We are restricted to dragging dimensions to the axes,
and measures to the Data section, so potential confusion is
eliminated to a large extent. Indications as to the nature / identity of the
toolbar objects are a fringe benefit of our connection to the OLAP cube, as we
shall see.
Let's begin a basic Browse
process to illustrate the steps involved:
1.
Drag the Warehouse Sales
item (a measure) from the PivotTable Field List to the Data
section (the portion of the PivotTable area where "Drop Data
Items Here" appears in gray).
A tiny icon appears in
the image of the four-part map of the PivotTable area. The "data"
section of the icon image is blue, indicating that the item we are dragging is
a measure.
2.
Drop the Warehouse Sales
item in the "Drop Data Items Here" (the "Data")
section of the PivotTable "map."
Warehouse Sales appears within the PivotTable as depicted
in Illustration 13.
Illustration 13: The
Warehouse Sales Measure in the Data Section (with Docked, Partial PivotTable
Field List in Place)
Most
of the section highlights disappear, as shown, and the PivotTable report
displays the Total of Warehouse Sales in the cube.
3.
Select the Warehouse
dimension.
4.
With Row
Area selected in the selector under the Field List, click the Add
To button to its immediate left.
The PivotTable
report now appears as shown in Illustration 14, where we see that Warehouse
Country has become the row header label.
Illustration 14: The
Warehouse Dimension in the Row Axis
5.
Drag the Time
dimension to the column axis of the PivotTable report (an
alternative means to using the selector / Add To button for placing Field
List items), where the empty cell appears to the right of "Warehouse
Sales" and above "Total," in the area marked "X"
in Illustration 15.
Illustration 15: Drop
Point for the Time Dimension in the Column Axis
The
mouse pointer becomes an icon, to indicate when we are in the correct position
for dropping.
6.
Drop the Time
item in the cell indicated.
1997 and 1998 (both Year levels of the Time
dimension) appear as column header labels within the PivotTable, as
depicted in Illustration 16.
Illustration 16: The
Time Dimension in the Column Axis
7.
Drag the Product
dimension to the page axis of the PivotTable report (the blue
outlined area at the top left corner of the worksheet, which probably still
indicates "Drop Page Fields Here"), as shown in Illustration
17.
Illustration 17: The
Product Dimension in the Page Axis
Let's
center the column headings to enhance the appearance of the new report.
8.
Select and
click Table Options from the PivotTable menu on the PivotTable
toolbar, as depicted in Illustration 18.
Illustration 18:
Selecting the Table Options Dialog
The PivotTable
Options dialog appears. Many
formatting and other global settings are represented within this dialog.
9.
Leaving all other
settings at default, click the checkbox to the immediate left of Merge
labels.
The PivotTable
Options dialog appears, with our modification, as shown in Illustration
19.
Illustration 19: The
PivotTable Options Dialog
NOTE: For information regarding the
purposes of the other settings, see the online Help and other
documentation.)
10.
Click OK to
save our change, and to dismiss the PivotTable Options dialog.
Our PivotTable
report appears, with the column labels now centered, as depicted in Illustration
20.
Illustration 20: Format
Changes Appear in the PivotTable Report
» 
Comment and Contribute
