Procedure: Adding and Parameterizing the Date Dimension within the LastPeriods() Function in
Reporting Services 2005
As we
noted earlier, our intent within this article is to examine the detailed
mechanics involved in adding – and parameterizing – a single MDX
function, LastPeriods() within our report. Moreover, we will
parameterize the Date dimension itself (both Fiscal Year and Month)
so as to allow our information consumers to declare an “as of” date within the
report (from which to “look back,” as it were), rather than forcing them
to rely upon “current month” (or the last month with activity within the cube)
as the “starting point” from which they look back. We will accomplish the initial
steps from the Data tab, to which we will return in the following steps.
1.
Click the
Data tab, once again
We
return to the Data tab, where this time we will ultimately replace some
of the syntax that currently exists within the MDX query - syntax which
has, up until now, been assembled graphically via the MDX query designer
in Design mode.
As most of us know,
when we create a dataset from an Analysis Services data source, Report
Designer initially offers us the MDX query designer in Design mode.
We can work within Design mode to interactively build an MDX query using
graphical elements. Design mode affords us the capability to select a cube
and to drag numerous objects to the Data pane, including:
-
Dimensions
-
Dimension Attributes
-
Levels
-
Hierarchies
-
Measures
-
Key Performance Indicators
(KPIs).
We can also add calculated
members, set default values for variables (we will expose a
specific instance of this in our practice exercise, as part of the focus of our
article), and automatically see result sets returned for the queries we
build (particularly useful as we make changes within the Query pane) to
the Data pane. We can set filters (like the one we removed in our
earlier steps to prepare our OLAP report clone for use within our practice
session), to limit the data retrieved from the data source by the query,
and define parameters.
As many of us have no
doubt already learned, the need for dealing with “direct MDX” has not
disappeared. This can be particularly true when the need arises to tailor an
MDX query to meet (especially more sophisticated) business requirements within Reporting
Services. One example will be illustrated within the focus of our article
– the use of any but the most basic (those that are implicit within the
arrangement of objects within the Data pane of the MDX functions within
the MDX query designer in Design mode) MDX functions. (We
illustrate other examples where we must use the MDX query designer in Query
mode in numerous other articles of the MSSQL Server Reporting Services series.)
To view
or edit MDX query syntax directly, we must switch to Query mode by clicking the Design Mode
toggle button on the toolbar. The Design
Mode toggle button appears within
the Report Designer as shown in Illustration 24.
Illustration 24: Design
Mode Toggle Button in Report Designer
2.
Click the Design Mode toggle button on the toolbar.
The syntax
for the underlying query appears, as depicted in Illustration 25.
Illustration 25: MDX
Syntax Appears within the Query Pane of the Query Mode
What we
need to do, to meet the requirements of our client colleagues, includes the
following (in the order specified):
-
Add a query
parameter for each of Year, Month, and Span of Lookback;
-
Ensure that
the datasets automatically created for each of the newly added query
parameters are appropriate, adjusting as necessary to support report parameters
in meeting the business requirement;
-
Add the syntax
to the query to leverage the MDX LastPeriods() function, which will
enclose the parameterized input, ultimately, of the three query
parameters, to achieve the retrieval / presentation dictated by the
selections of information consumers at runtime.
We will
accomplish these steps within the following subsections.
Add the
Required Query Parameters to Support Date and Function Parameterization
1.
Click the
Query Parameters
button on the toolbar, as shown in Illustration 26.
Illustration 26: Click
the Query Parameters Button atop the Query Pane ...
The Query
Parameters dialog appears as depicted in Illustration 27.
Illustration 27: The
Query Parameters Dialog Appears ...
Here we
add the three “variables” we will be associating, via the query syntax,
with the report.
NOTE: Simply typing the placeholders into the query syntax (preceded by an
“@” sign in Reporting Services 2005) first does not automatically
create the query parameters or the associated report parameters –
contrary to some of the documentation (and other sources) I have seen on the
Web. Simply typing in the placeholder(s) first in the query, and following
this with an attempt to run the query via the Execute Query button,
results in a message informing us of “query preparation failure.” The
message relates that “the query contains the [term following the “@” sign in
our placeholder] parameter, which is not declared.” An example of this
message, for a parameter placeholder added to the query syntax as “@LookBackSpan,”
for which no entry has been made in the Query Parameters dialog, appears
as shown in Illustration
28.
Illustration 28: Message
Noting Query Preparation Failure, Due to an Undeclared Parameter ...
2.
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, as shown
in Illustration 29.
Illustration 29: Select
the Cell Marked “<Enter Parameter>” ...
3.
Type the
following into the selected cell:
TimeYear
4.
Within the
cell to the immediate right of the Parameter cell just populated,
labeled Dimension, select Date, as depicted in Illustration 30.
Illustration 30: Select
the Date Dimension ...
5.
Within the
cell to the immediate right of the Dimension cell just populated,
labeled Hierarchy, select Date.Fiscal, as shown in
Illustration 31.
Illustration 31: Select
the Date.Fiscal Hierarchy ...
6.
Leave the
Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy
cell just populated, unchecked.
7.
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.
8.
Select the FY
2005 member, as depicted
in Illustration 32.
Illustration 32: Select FY
2005 as the Default ...
NOTE: Assigning a Default is a requirement to allow us
to test the dataset query anytime we add query parameter
placeholders to the query syntax. Doing so also initially populates the default
of a subsequently created report parameter, as we shall see.
9.
Click OK
to accept our selection, and to dismiss the Default selector.
The row
within which we have been working in the Query Parameters dialog appears
as shown in
Illustration 33.
Illustration 33: The
Query Parameters Dialog with Our Input ...
» 
Comment and Contribute
