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.

 ="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 Query pane, containing our modified input, appears as shown in Illustration 36.

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