MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 11

July 26, 2004

Execute the Report and Verify Results

1.  Click the Preview tab, atop the design environment.

This begins the chain of events surrounding parameterization. The parameter selector box, labeled Location, appears immediately below the Preview tab, indicating initialization of the parameter and the execution of the picklist dataset.

2.  Scroll down the selector, and click / select USA, as shown in Illustration 53.

Click for larger image

Illustration 53: Select USA Level in the Store Location Hierarchy

Note the indentation of the various members of the hierarchy, and how it makes selection at various levels easy.

3.  Click View Report to activate the primary dataset query.

The parameter we have chosen (in its "qualified" format) is, in turn, passed to MSAS. The results display quickly, and appear similar to those depicted in Illustration 54.

Illustration 54: Results Dataset, Based upon Our Parameter Selection

4.  Explore using the parameter further, selecting various levels in the picklist to return varying results, as desired.

Conclusion and a Look Ahead ...

In this article, we explored an approach for supporting a hierarchical pick list for the reporting environment. Our focus was the creation of calculated members, housed within a named set, to provide cube-based parameter prompt support. We explored reasons that this might be beneficial, and then created the structures involved, before processing our cube to ready it for reporting.

We created a report within Reporting Services to gain some experience using the parameter picklist components we assembled in MSAS, primarily because it is readily available at no cost to MSSQL Server licensees, and to anyone else via download of a free evaluation copy from Microsoft (in contrast to the prohibitive cost of most other enterprise reporting packages). As we noted, our cube-based parameter support concept will work equally well with any business intelligence solution that supports connectivity to an MSAS cube. We added a parameter in the report, based upon the cube structure we built, and verified its operation by generating the report with a run-time parameter.

In our next article, we will return to Time concepts, and explore scenarios surrounding running totals.

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