Work
with Underlying Dataset Queries to Enforce Cascading and to Support the Parameterized
“Top Count” Capability
Add
Syntax to the TimeMonth Dataset Query to Enforce Cascading
We will
return to the Data tab, at this point, to make modifications to the
primary dataset query to both properly enable the date-related parameters
for Year and Month, as well as to add and enable the MDX function
that will support the parameterized “top count” capability within
the report, as our client colleagues have requested.
Before we
work specifically with the primary dataset query, however, we will modify
our TimeMonth dataset query to support the cascading parameter
picklist for the Month parameter, the current absence of which we
discussed earlier. As we mentioned before, we seek to enforce cascading
so that the appropriate child members (Months) appear within the Month
parameter picklist, based upon the selection made in the Year
parameter parent level to which they belong.
At this
stage we face a couple of modifications to the TimeMonth dataset. First
we must add a Query Parameter within the dataset for TimeYear,
and then we must embed the parameter, via a placeholder, into the
syntax. We shall make these modifications within the steps that follow. We
will then need to make a relatively minor final adjustment to the TimeYear
report parameter, as a reaction to a reset that it performs based upon our
addition of the Query Parameter within the TimeMonth dataset, as
we shall see.
1.
From the Data
tab, once again, click the downward selector arrow on the right side of the Dataset
selector.
2.
Select the TimeMonth dataset, if necessary.
The dataset
query appears, presenting the MDX syntax in Query view, as we saw in
earlier steps. Because we are establishing a dependency, based upon the TimeYear
dataset, within the TimeMonth
dataset, we must
establish this dependency within a Query Parameter within the dataset,
just as we did on a larger scale within the primary dataset query earlier.
3.
Click the
Query Parameters
button on the toolbar, as we did earlier.
The Query
Parameters dialog (currently empty) appears.
Here we
add the new “variable” we are associating, via the query syntax,
with the TimeMonth parameter picklist, which appears at runtime. It is
in this manner that we build in support for cascading of (only) the
appropriate child-months.
4.
In the single
row of the Query Parameters dialog, in the table underneath the caption
“Specify details of the parameters defined in the query,” click
the leftmost cell (containing the placeholder “<Enter Parameter>”)
to select it.
5.
Type the
following into the selected cell:
TimeYear
6.
Within the
cell to the immediate right of the Parameter cell just populated,
labeled Dimension, select Date, just as we did within the same
position within the primary dataset query earlier.
7.
Within the
cell to the immediate right of the Dimension cell just populated,
labeled Hierarchy, select Date.Fiscal, as we did within the same
position within the primary dataset query earlier.
8.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked, once again.
9.
Using the
selector in the Default cell (the rightmost cell in the row within which
we have been working), expand the All Periods entry by clicking the “+”
sign to its immediate left.
10.
Select the FY
2005 member, as
we did within the same position within the primary dataset query earlier.
11.
Click OK
to accept our selection, and to dismiss the Default selector.
The Query
Parameters dialog appears, with our addition, as shown in Illustration 14.
Illustration 14:
The Query Parameters Dialog, with Our Input ...
12.
Click OK
to accept our new parameter addition, and to close the Query Parameters
dialog.
We are
returned to the Data tab, where we can see the original MDX syntax in
the Query pane. The MDX appears as depicted in Illustration 15.
Illustration 15:
The TimeMonth Dataset Query
All that
remains to enforce a cascading TimeMonth parameter is to insert the
appropriate syntax into the TimeMonth dataset query. Our overall
objective is simply to force a dynamic “filter” based upon the value of the TimeYear
parameter (which we have already “declared” within the Query Parameters
dialog). We will do this in the steps that follow:
13.
Remove the
following from the existing dataset query:
FROM [Adventure Works]
14.
Replace the
newly removed clause with the following syntax (simply add it to the end of the
query):
FROM
(
SELECT
(
STRTOSET(@TimeYear, CONSTRAINED) ) ON AXIS(0)
FROM
[Adventure Works]
)
The
entire query should appear as shown in Illustration 16 (I have expanded the additional
syntax to promote easier understanding).
Illustration 16:
Modified TimeMonth Dataset Query
15. Click the Execute Query (“!”)
button in the toolbar.
The
query executes, and a data grid becomes populated, as partially depicted in Illustration 17.
Illustration 17: The
Dataset with Our Modifications (Partial View)
We can
scroll through the results dataset and easily see that only those Months
that are the children of FY2005 (the default TimeYear
parameter value) appear. This will support our desired retrieval objective,
selections within the Month picklist at runtime that are associated with
(and only those that are associated with) the year that is
selected at runtime within the parent TimeYear parameter. We will
ascertain the effectiveness of our handiwork via the performance of a simple Preview
later, once we have completed the syntax modification procedures in the next
subsection, where we make final enhancements - this time to the primary dataset
- to embed the “top count” capabilities requested by our client
colleagues, based upon the selections they make at runtime for year, month,
and “number of ‘top’ items to retrieve.”
Add
Syntax to Enforce the Month Parameter Selection and Leverage the MDX TopCount()
Function, Containing the TopCountSpan Parameter Placeholder
Our last
procedure will comprise “turning it all on” by another syntax modification –
this time within the query supporting the primary dataset, ProductData.
Recall that we have already added query parameters for TimeYear, TimeMonth
and TopCountSpan into the ProductData dataset. At this point, we
have only to embed the parameters into the query syntax, which we
will do in the steps that follow.
1.
From our
present position on the Data tab, click the downward selector arrow on
the right side of the Dataset selector, once again.
2.
Select the ProductData dataset, at the top of the list of
datasets.
The dataset
query appears, presenting the MDX syntax in Query view, as shown in Illustration 18.
Illustration 18: The Primary
Dataset (ProductData) Query
3.
Carefully
remove the following syntax from the query:
([Date].[Fiscal].[Month].ALLMEMBERS
shown circled in the portion of the query depicted in Illustration
19.
Illustration 19: Remove the
Date–related Portion of the (Partially Shown) Query
4.
Leaving the
cursor in place, type the following substitute syntax into the query:
(STRTOSET(@TimeMonth, CONSTRAINED)
5.
Carefully
remove the following syntax from the query:
[Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS
shown circled in the query presented in Illustration 20.
Illustration 20: Remove
the Sales Reason–related Portion of the Query
6.
Leaving the
cursor in place, type the following substitute syntax into the query:
TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan, [Measures].[Internet Sales Amount])
So that the complete query is as follows (cut and
paste it into the place of the existing query, if that is easier):
SELECT NON EMPTY { [Measures].[Internet Order Quantity],
[Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY { (STRTOSET(@TimeMonth, CONSTRAINED) * TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan,
[Measures].[Internet Sales Amount])
* [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME, [Sales Reason].[Sales Reason].[Sales Reason].KEY,
[Sales Territory].[Sales Territory Group].[Sales Territory Group].KEY ON ROWS
FROM ( SELECT ( { [Date].[Fiscal].[Fiscal Year].&[2004],
[Date].[Fiscal].[Fiscal Year].&[2005] } ) ON COLUMNS
FROM [Adventure Works]) CELL PROPERTIES VALUE,
BACK_COLOR,
FORE_COLOR,
FORMATTED_VALUE,
FORMAT_STRING,
FONT_NAME,
FONT_SIZE,
FONT_FLAGS
Note: The “replacement” syntax is in red in the code above.
The dataset
query appears (the replacements have been buffered with extra space for
easier viewing), with our substitutions (boxed in red), within the Query pane
as shown in Illustration
21.
Illustration 21: The
Dataset Query with Our Modifications
7.
Click the Execute
Query button on the toolbar, once again, to run the newly modified query.
The query
executes, and the data grid again becomes populated, as partially
depicted in Illustration
22.
Illustration 22: The Modified
ProductData Dataset (Partial View)
An
examination of the query and the data it returns reveals that, from the
perspective of the Data tab, therefore, our query appears to be
performing as expected. We will verify performance of this and other items
with which we have worked in the next section, where we will conclude our
practice exercise.
» 
Comment and Contribute
