dcsimg

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

July 26, 2004

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.


SELECT 

  {[Measures].[Amount], 
    RS_ns_PX_GeogStore_Hier} ON COLUMNS,


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

FROM 

   [Exp_Finance]

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.

ds_px_LocationHier

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


SELECT 

   {RS_ns_PX_GeogStore_Hier} ON COLUMNS,

   {[Store].Members} ON ROWS

FROM 

[EXP_FINANCE]

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.

px_LocationHier

4.  Type the following into the Prompt box.

Location:

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers