Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 12, 2005

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

By William Pearson

"Going Multidimensional" in Cube Reporting

We will take the a few steps to demonstrate how we can further unleash the multidimensional power of our cubes, by making multiple dimensions share the same axis in our PivotTable report. The PivotTable report provides a full realization of the information presentation potential of the multidimensional cube when we intersect dimensions on a single axis, and therefore delivers the full impact of multidimensional data in the "two-dimensional world" of the typical reporting environment.

We will begin by making Product a part of the row axis to make analysis of Warehouse members more powerful, yet more compact.

1.  Drag the Product item from the page axis (upper left hand corner of the PivotTable report) to the right of the currently appearing Warehouse City column.

2.  Dropping the Product item to the right of City.

The result set should resemble that partially shown in Illustration 28.

Click for larger image

Illustration 28: Combining the Warehouse and Product Dimensions in the Row Axis

We can easily tell that only the Product Family label represents the top level of a dimension, as a drop-down arrow only appears at top levels. We can see in this simple scenario that multiple dimensions (in our case the Warehouse and Product dimensions), as well as multiple levels of a given dimension (here, the Warehouse State and Warehouse City levels of the Store dimension), can co-exist on a single axis. The possibilities that emerge are far reaching, indeed.

Now let's add another measure - Units Shipped - to the report, to present information about Warehouse shipping activity that coincides with Warehouse sales.

3.  Drag the Units Shipped item from the PivotTable Field List to the Data section of the PivotTable report (the column under the Year heading and 1997 in our present report).

4.  Drop the Units Shipped item on the top cell in the column, as depicted in Illustration 29.

Illustration 29: Drop Point for the Units Shipped Item

Once we drop the measure, the PivotTable report appears as shown in Illustration 30.

Illustration 30: Dual Measures now appear in the PivotTable Report (Partial Illustration)

A new Data column appears, and in accordance with its default behavior, represents what appears to be a fourth row dimension. "Data" itself represents a "measures dimension," as it were.

5.  Drag the Data label to the column cell just above its present position, as depicted in Illustration 31.

Illustration 31: Drag the Data Label Up to Swap Axes ...

6.  Drop the Data label in the cell above its present position.

We have placed the label in the column axis, which could be more along the lines of our presentation needs. The PivotTable report now appears as partially shown in Illustration 32.

Illustration 32: The PivotTable Report with a Second Column Dimension

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

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

The PivotTable report appears as partially depicted in Illustration 33, once we make this final change.

Illustration 33: 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.

As we can see, making a PivotTable report truly multidimensional is both straightforward and intuitive, once we get a good understanding of the basics. We can further improve the appearance of our report by making any of a myriad of adjustments within the standard Excel formatting options, or from the AutoFormat choices that we can access from the Format Report button on the PivotTable toolbar. We can experiment with these to find a style that approaches the needs of the information consumers within virtually any reporting and analysis scenario.

8.  Select File -> Exit to close Microsoft Excel 2003, saving the PivotTable report to a convenient location, as appropriate.

Having examined the PivotTable report for Excel 2003 to the extent of familiarity with which we explored it for its predecessor version, we will take a brief look at another exciting OLAP reporting option for Excel in the section that follows.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM