Reporting Options for Analysis Services Cubes: MS Excel 2002 - Page 7

Viewing Member Details

The capability to "explode" the hierarchical levels of our dimensions to member children enhances business user data analysis by allowing them not only to view the reports to which they have become accustomed (for example, a trending of monthly organizational expenses), but also to drill down to the details below any summary value. This allows the analyst to see the details that make up the value that he / she drills down upon. The beauty of multidimensional analysis becomes clear, with this interactive ability to find root causes for changes in activity over time. Information consumers can also return to higher levels within hierarchies to view summary information.

The PivotTable report / cube combination provides the ability to view various levels of activity for members as a group, as well as to analyze the details of summary values on an individual member basis, as we will see. We can explore the drill-down capabilities of the PivotTable report by performing the following actions:

8.      Double-click the USA Store Country in the leftmost column of our existing report.

The report drills to the Store States, the immediate children of the Store Country, as shown below.

9.      Double click the Washington (WA) Store State to drill to its children.

The children of the Washington Store State appear, as shown below.

We can always reverse a drill-down action ("drill up," as it were) by double-clicking the original drill-down object. We will short-circuit the process and zoom up to the original Store Country level.

Double-click the USA Store Country once again. The result set should resemble that depicted below.

10.  Double-click USA again, to expose the three Store States once more. Click (once, to highlight) the Store State level heading (just above the CA member) on the leftmost side of the report, and then click the Show Detail button on the PivotTable toolbar (see Illustration 23 below for the Show Details and Hide Details button pictures). This provides another means of drilling down to the immediate children.

The children of all members of the Store State level appear, as illustrated below.

Next, let's assume that we want to hide the "USA" column of the report (logical enough, if all our stores are located in the US - the "USA" level is somewhat redundant, and takes up useful real estate.)

11.  Right-click the Store Country level heading, and click Hide Levels in the context menu that appears, as shown below.

This leaves us with a view that is more compact. Compare the result to that shown in Illustration 26.

There are many other options in browsing our cubes, as well as with formatting the views we generate. It pays to invest some time experimenting with the plethora of available choices, and determining the combination of setpoints needed to get information to the targeted consumers in a fashion that will be most useful to them. Next, we will delve a bit further into the concepts of adding multidimensionality to our reports, and demonstrate a straightforward approach to leveraging even more of the power of our OLAP cube to deliver analysis-focused data.