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 Jul 26, 2004

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

By William Pearson

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

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