Procedure:
Adding and Parameterizing the Date Dimension Elements and the TopCount()
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, TopCount() 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. 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 that
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 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 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 my 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 TopCount;
-
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 TopCount() function, which will work in
conjunction with the other two 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 myriad 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 within an
earlier article), for which no entry had been made in the Query Parameters
dialog, appears as shown in Illustration 28.
Illustration 28: Message
Noting Query Preparation Failure, Due to an Undeclared Sample 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
