Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 13, 2008

Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I - Page 5

By William Pearson

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:


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM