Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I - Page 5June 13, 2008 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:
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.
2. Click the Design Mode toggle button on the toolbar. The syntax for the underlying query appears, as depicted in Illustration 25.
What we need to do, to meet the requirements of our client colleagues, includes the following (in the order specified):
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.
The Query Parameters dialog appears as depicted in Illustration 27.
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.
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.
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.
5. Within the cell to the immediate right of the Dimension cell just populated, labeled Hierarchy, select Date.Fiscal, as shown in Illustration 31.
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.
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.
|
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
