Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ... - Page 3

September 12, 2005

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.

Click for larger image

Illustration 11: The PivotTable "Map"

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

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers