dcsimg

Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I - Page 6

July 29, 2008

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 ...








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers