"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, as we have
mentioned in previous articles, the handling of parameterization for an 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.
Reporting
from an OLAP cube, with anything but the simplest, non-parameterized MDX,
requires a bit more effort than simply creating an MDX query to create a Dataset,
as we did with the original in our example. 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 on the Data tab - hence the
unwieldiness that some developers find challenging.
Let's
revisit our initial query, through which we will make the
soon-to-be-handicapped Report Designer aware of all the fields we expect
to require within 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 ProductData Dataset in the Dataset selector, as depicted in Illustration
48.
Illustration
48: Select the ProductData Dataset Again ...
2.
Replace the
original ProductData Dataset query with the following (cut and paste, if
helpful):
SELECT
{ [Measures].[Store Sales], [Measures].[Store Cost] } ON AXIS(0),
{TopCount([Product].[Product Name].Members, 10, ([Measures].[Store Sales]))} ON
AXIS(1),
{[Time].[1997].[Q1]} ON AXIS(2)
FROM
[SALES]
3.
Click the Run
button in the toolbar to execute the query,
4.
Click the Refresh
Fields button to refresh the data fields with the data retrieved within the
new Dataset, as shown in Illustration 49.
Illustration 49: Execute
the Query, and then Refresh Data Fields
The new Dataset
appears in the Results pane, below the query, as depicted in Illustration
50.
Illustration 50: Results
Dataset Appears (Compressed View)
Running
the query with "placeholder", hardcoded TopCount() and Time
specifications, as above, allows us to populate the data set with a
representative of each of the data fields, as well as giving us an opportunity
to see the function work within a simple, non-parameterized query. Having
refreshed the data fields to match the new query results, we will now insert
parameterization. Once this is done, the query must be combined into a single
string, as we shall see.
5.
Modify exactly
the existing Dataset query by substituting parameter references, shown in the "With"
column of Table 4, for the portion of the query designated under "Replace"
|
Replace:
|
With
|
|
TopCount (in
3rd Row/ Axis(1))
|
" +Parameters!pX_OutlierType.Value +"
|
|
10 (in
3rd Row / Axis(1))
|
" +Parameters!pX_TopBotNum.Value +"
|
|
[Time].[1997].[Q1] (in 4th Row / Axis(2))
|
" +Parameters!pX_Date_Hier.Value +"
|
Table 4: Expression Components Summary
The Query
pane, containing our modified input, appears as shown in Illustration 51.
Illustration 51: MDX
Query in the Query Pane
6.
Combine the
query into a single string, enclosing it in quotes, with an equals (=) sign in
front of the query, precisely as shown in the following string (cut and paste,
if helpful):
="SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON AXIS(0), {"
+Parameters!pX_OutlierType.Value +"([Product].[Product Name].Members, "
+Parameters!pX_TopBotNum.Value
+", ([Measures].[Store Sales], " +Parameters!pX_Date_Hier.Value +"))} ON AXIS(1), {"
+Parameters!pX_Date_Hier.Value +"} ON AXIS(2) FROM [SALES]"
NOTE: The query must exist as a single line, without "artificially"
creating line breaks via the Enter key, etc. It often helps to do so
within Notepad or another editor. Although line breaks may appear to exist in
the query above, this is simply the way the text is presented in this
document; the query is a single string.
The Run icon is disabled once the query is enclosed
in double quotes. The importance of constructing the query correctly becomes
obvious, in that there is no way to execute it within the Data tab at
this stage.
An explanation of the components of the above expression
appears in Table 5.
|
Component
|
Meaning
|
="SELECT
|
The = sign alerts Reporting Services
that an expression is to follow. The expression must be enclosed in
double quotes.
|
{[Measures].[Store Sales],
[Measures].[Store Cost] } ON AXIS(0)
|
The measures we wish to include in the report, specifying
the Column Axis of the query (Axis(0))
|
{" +Parameters!pX_OutlierType.Value
+"([Product].[Product Name].Members, "
+Parameters!pX_TopBotNum.Value +",
([Measures].[Store Sales], "
+Parameters!pX_Date_Hier.Value +"))} ON
AXIS(1),
|
The Row Axis of the query (Axis(1)),
whereby we are specifying - just as we did in our first, simpler MDX
expression, that we wish Product Names to appear in the rows of the
returned Dataset.
The following syntax comprises parameterization of the TopCount()
/ BottomCount() functions:
+Parameters!pX_OutlierType.Value +
and
+Parameters!pX_TopBotNum.Value +
The
first string above enables selection of "Top" or "Bottom",
passing the associated keyword to be concatenated with the rest of the syntax
required to build the function for the members of the Product Name
level of the Product dimension. The second string above passes in the
<< Count >> portion of the function, and the resulting
construction is concatenated to the Numeric Value, Store Sales,
upon which the "top" or "bottom" selection is based. The
result is a complete TopCount() / BottomCount() function.
NOTE: For more on the functions
themselves, see the section entitled A
Quick Overview of the TopCount and BottomCount Functions above.
|
{" +Parameters!pX_Date_Hier.Value +"} ON
AXIS(2)
|
The AXIS(2) specification is populated by the
unique name from the cube's Time dimension, enclosing the reference to
the value of the parameter within the brackets ({}) - in the present
case, the value of the date parameter selected from the hierarchical
picklist, stored in the "qualified" format of the respective Time
dimension specification.
|
FROM [SALES]"
|
The FROM clause, a staple of any MDX query,
merely specifies the cube (Sales) we are designating as the source of
our data.
The final set of double quotes (") is vital, as it signals "end
of the expression" to MSAS.
|
Table 5: Parameterized
Expression Components Summary
Having
completed the parameterization of the report, we have a final pair of modifications
to make to the report's properties to render the report fully functional from
the perspective of the new business requirements with which we began our
practice exercise. We will perform this alteration in the following section.
» 
Comment and Contribute
