Integrating Microsoft SQL Server 2000 OLAP and Microsoft Office, Part 1: Creating an Excel PivotTable Report with an OLAP Cube
- Introduction to the Series
- What You Will Need to Complete the Series Tutorials
- Building an Excel PivotTable Report on OLAP Data Source
- Connecting Excel to the OLAP Cube
- Layout and Navigation of the PivotTable Report
- Browsing the Cube
- Drilling to Member Details
- Going Multidimensional
- Next in This Series
Browsing the Cube
A PivotTable report is highly flexible in that it serves as both a browser and a report writer. Using the dimension and measure components of the PivotTable report on the toolbar, we're restricted to dragging dimensions to the axes and measures to the data area, so the browsing process eliminates potential confusion in many ways. These clues as to the nature of the toolbar objects are a fringe benefit of the connection to the OLAP cube.
Let's begin a basic Browse process to illustrate the steps involved:
Drag the Store Sales button (a measure) from the PivotTable toolbar to the data area (the portion of the PivotTable area with Drop Data Items Here in gray). Positioning the mouse pointer over a toolbar button displays a ScreenTip with the name of the button. The ScreenTip also indicates that the button represents a measure and not a dimension, as it tells you to drag the button to the data area.
Figure 13 The ScreenTip for Store Sales.
Drop the Store Sales button in the data area of the PivotTable. The PivotTable report begins to take shape, showing the total of Store Sales (see Figure 14).
Figure 14 The Store Sales measure in the data area.
Drag the Store dimension to the row axis of the PivotTable report, where Total appears at present for Store Sales (see Figure 15). An icon appears as you drag, indicating the only allowed drop points; the shadowed area of the icon "lights" when the icon is in drop territory.
Figure 15 Drag the Store dimension to the row axis.
The PivotTable report now appears as shown in Figure 16. Store Country has become the row heading.
Figure 16 The Store dimension in the row axis.
Drag the Time dimension to the column axis of the PivotTable report, where the empty cell appears to the right of Store Sales and above Total (see Figure 17). Again, the shadowed area of the icon "lights" when the icon is in the correct position for dropping.
Figure 17 The Time dimension in the column axis.
Drag the Store Type dimension to the page axis of the PivotTable report (the blue outlined area at the upper-left corner of the worksheet, which probably still indicates Drop Page Fields Here), as shown in Figure 18.
Figure 18 The Store Type dimension in the page axis.
Let's center the column headings to enhance the appearance of the new report. Select Table Options from the PivotTable menu on the PivotTable toolbar (see Figure 19).
Figure 19 Preparing to select table options.
When the PivotTable Options dialog box appears, select the Merge Labels option and deselect Mark Totals with * as shown in Figure 20. (Many users interpret the asterisk to mean that there's a footnote on the page somewhere, causing a search for comment text and so forth, which raises unnecessary questions.)
Figure 20 The PivotTable Options dialog box.
For more information on the other setpoints, see the online Help and other documentation.
Click OK. The PivotTable should now look like Figure 21.
Figure 21 Format changes appear in the PivotTable report.