"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.
|
Component
|
Meaning
|
="SELECT |
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.
» 