3.      Drag the Data label to the column cell just above its present position. The PivotTable report now appears as illustrated below.

We have placed the label in the column axis, which is probably more along the lines of our presentation needs.

Illustration 29: The PivotTable Report with a Second Column Dimension

We will move the Time dimension to the page axis to make our presentation a bit less confusing for its audience.

4.      Drag the Year dimension item to the page axis in the top row of the PivotTable report.

The report appears as shown in Illustration 30, once we make this final change. Making a PivotTable report truly multidimensional is both straightforward and intuitive, once we get a good understanding of the basics.

Illustration 30: The PivotTable Report with Final Changes

Keep in mind that hiding either measure is as simple as clicking the drop-down arrow next to the Data dimension button and clearing the respective check box.

We can further improve the appearance of our report by making any of a myriad of format adjustments from the standard Excel formatting options, or from the AutoFormat choices that we can access from the Format Report button on the PivotTable toolbar. Experiment with these to find a style that approaches the needs of the information consumers.

Next in Our Series ...

In this lesson, Reporting Options for Analysis Services Cubes: Microsoft Excel 2002, we explored features that integrate Analysis Services and Excel 2002 to provide a vehicle for client reporting and other business intelligence pursuits. The central focus of the article was a basic overview of the Excel PivotTable Report functionality. We examined the PivotTable Report in its role of local "client representative" of the Analysis Server.

We exposed PivotTable Report features that are available with Microsoft Excel 2002 (most of which were available in Excel 2000) for creating robust and flexible reports. We explored setting up a connection to an OLAP cube, creation of the PivotTable Report, and the general navigation of member information and cube data. We practiced Drilling down to (and zooming up from) details of reporting summaries, then exposed the use of intersected dimensions to make the PivotTable Report truly multidimensional. We discussed a few formatting options at relevant junctures in our exploration of the Excel 2002 PivotTable Report.

In our next lesson, Reporting Options for Analysis Services Cubes: Microsoft FrontPage 2002, we will examine some of the options offered by the Office PivotTable List (the FrontPage "equivalent" of the Excel PivotTable Report) for report building with Analysis Services cubes. As we did with the Excel PivotTable Report in the first of our Reporting Options articles, we will present an introduction to using FrontPage 2002 to retrieve and display information from our cubes, first discussing the steps needed to define our data source, and to establish a connection to the cube. Next, we will expose the layout of the PivotTable List and its navigation, and explore its use in browsing and reporting our cube data. We will discuss the intersection of dimensions in the PivotTable report axes to achieve multidimensional reporting within the classical two-dimensional presentation of print media and PC screens, as well as general formatting considerations at relevant points in the tutorial.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.