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 Apr 7, 2003

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

By William Pearson

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.

Illustration 20: Drilling Down to the Children of the Store Country USA

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

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

Illustration 21: Children of the Washington Store State

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.

Illustration 22: Zoom Up to the USA Store Country

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.

Illustration 23: The Show Details and Hide Details Buttons

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

Illustration 24: Children of the Entire Store State Level

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.

Illustration 25: The Context Menu - Hide Levels Option

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

Illustration 26: The PivotTable Report, sans the USA Store Country Column

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.

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