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

Execute the Report and Verify Results

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.

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

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.

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