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
Layout and Navigation of the PivotTable Report
The PivotTable report is composed of four general sections (see Figure 11). We exploit the power of OLAP in the PivotTable report by simply placing dimensions in the axes sections in such a way as to present data in the desired combinations. As you drag and drop the dimensions and their members into different positions, the measures change to match the new placement of the combinations. Values are therefore presented in the context of the axes.
Figure 11 The PivotTable "map."
Dimensions and measures are presented as buttons on the PivotTable toolbar (see Figure 12). Each distinct row is labeled by one of two types of icons representing dimensions and measures. The icons appear as tiny PivotTable "maps," with sectional shading in each that indicates whether the icon represents a measure or a dimension.
Figure 12 Buttons in the PivotTable toolbar.
The PivotTable toolbar buttons are the "main ingredients" of the Excel PivotTable report. The toolbar can be anchored to the top of the screen (docked) simply by dragging it to the desired location, making it a fixed target (something I find easier to handle than the "floating" approach), and can be made to reappear at anytime by simply choosing View, Toolbars, PivotTable.