"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.
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.
» 
Comment and Contribute