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 7

By William Pearson

Creating the Core Dataset

Recall, for purposes of our practice session in this article, that the Finance department has requested a basic expense summary report, which lists various store location's expense totals. The consumers have expressed the need for a single prompted parameter, based upon geographical store location, which allows them to filter on location at runtime. An ideal scenario, they have told us, would include the capability to select, within the same report, various other hierarchical "rollups" surrounding location, such as city, state and so forth.

We will design the basic report, beginning with the unparameterized dataset, prior to adding the parameter. This allows us to gain an appreciation for design first, for, as we will see, addition of the parameter makes dataset refreshment a bit less "elastic."

1.  Type (or cut and paste) the following basic MDX into the Query pane - Data tab of the Report Designer.


    RS_ns_PX_GeogStore_Hier} ON COLUMNS,

  {[Account].[All Account].
    [Net Income].[Total Expense]} ON ROWS



The Query pane appears as shown in Illustration 27.

Illustration 27: MDX Query in the Query Pane

2.  Click the Run button in the toolbar (the "!" icon), shown in Illustration 28, to execute the query.

Illustration 28: The Run Icon

The dataset appears in the Results pane, below the query, as partially shown in Illustration 29.

Illustration 29: Results Dataset Appears (Collapsed, Default View)

3.  Click the Refresh icon in the toolbar (circled in blue, to the left of the Run button), shown in Illustration 28 above, to update the data fields.

NOTE: It is important that this step is accomplished, and that the fields appear in the Fields tab (possibly hidden / undocked), before proceeding.

Creating the Parameter Picklist Dataset

1.  In the Dataset selector atop the Data tab, (which is now occupied by FoodMart 2000, the dataset we just created), select New Dataset, as depicted in Illustration 30.

Illustration 30: Select New Dataset in the Dataset Selector

The Dataset dialog appears.

2.  Type the following into the Name box.


3.  Type (or cut and paste) the following basic MDX into the Query string box.


   {RS_ns_PX_GeogStore_Hier} ON COLUMNS,

   {[Store].Members} ON ROWS



The Dataset dialog appears as shown in Illustration 31.

Illustration 31: Completed Dataset Dialog

4.  Click OK to accept our input.

The Dataset dialog closes.

5.  Click the Run icon in the toolbar, to execute the query.

The new dataset appears in the Results pane, below the query, as partially shown in Illustration 32.

Illustration 32: Results Dataset Appears (Partial View)

This dataset will serve as the support for our parameter picklist, (hence its name, ds_px_LocationHier) which we will establish next.

Creating the Parameter

1.  Select Report --> Report Parameters from the main menu atop the design environment, as depicted in Illustration 33.

Illustration 33: Select Report --> Report Parameters ...

The Report Parameters dialog appears.

2.  Click Add.

3.  Type the following into the Name box.


4.  Type the following into the Prompt box.


5.  In the Available Values section, click / select the radio button to the left of From query.

6.  In the Dataset selector to the right, select ds_px_LocationHier.

7.  In the Value field selector, select Measures_RS_PX_StoreGeogMSASName_Hier.

8.  In the Label field selector, select Measures_RS_PX_StoreGeogPLName_Hier.

9.  In the Default Values section, click / select the radio button to the left of None.

The Report Parameters dialog appears, as shown in Illustration 34.

Illustration 34: Results Dataset Appears (Partial View)

10.  Click OK to accept and save settings, and to close the Report Parameters dialog.

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