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

July 26, 2004

"Parameterizing" the Core Dataset

We began the design of our report with a simple, unparameterized core dataset. We can now logically approach the parameterization of the core dataset, with everything in place that will be required to make it work. While the handling of parameterization of our MDX query takes some getting used to, the important thing to remember is that the objective is simply to supply a precise MDX string, to pass to MSAS from Reporting Services.

This requires only a bit more effort than coding the MDX we have used to create the original dataset; the clumsiness comes with the introduction of the string by which we pass the MDX / parameter combination. In essence, we implant the parameter, and then turn the existing MDX into a string. While it works fine in passing the code to MSAS, converting the query to a string disables dataset refreshment.

Let's revisit our initial query, through which we have made the soon-to-be-inhibited designer aware of all the fields we expect to require from the dataset. Precision is important in the next steps, particularly with regard to the absence of carriage returns (pressing the Enter key to "push down" a line), and other stray actions.

1.  On the Data tab, select the FoodMart 2000 dataset in the Dataset selector, as shown in Illustration 35.

Illustration 35: Select the FoodMart 2000 Dataset Again ...

2.  Modify the original FoodMart 2000 dataset query to precisely the following (cut and paste, if helpful):

 ="SELECT {[Measures].[Amount]} ON COLUMNS,
{[Account].[All Account].[Net Income].[Total Expense]} ON ROWS FROM
[Exp_Finance] WHERE(" +Parameters!px_LocationHier.Value +")"

NOTE: Type as if a single line, without "artificially" creating line breaks via the Enter key, etc.

An explanation of the components of the above expression appears in Table 2.




The = sign alerts Reporting Services that an expression is to follow. The expression must be enclosed in double quotes.

{[Measures].[Amount]} ON COLUMNS, {[Account].[All Account].[Net Income].[Total Expense]} ON ROWS FROM [Exp_Finance]

The corpus of our initial query, sans the store / location dimension component. We will be setting up the capability to filter by geographic location via our hierarchical picklist; because this means that the "qualified" geography dimension will be specified in the WHERE clause (by its nature as a filter), we will remove the reference to the same store location information here.

WHERE(" +Parameters!px_LocationHier.Value +")"

The WHERE clause, a staple of any MDX query (whether explicit or not), enclosing the reference to the value of the parameter - in the present case, the value of the location parameter from the hierarchical picklist, stored in the "qualified" format, which therefore "completes" the WHERE clause.

The final set of double quotes (") is vital, as it signals "end of the expression" to MSAS.

Table 2: Parameterized Expression Components Summary

The Query pane, containing our modified input, appears as shown in Illustration 36.

Illustration 36: MDX Query in the Query Pane

NOTE: The Run icon is disabled, as depicted above, once the query is enclosed in double quotes.