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

Work with Underlying Dataset Queries to Enforce Cascading and Support Parameterized “Look Back” Capability

Add Syntax to the TimeMonth Dataset Query to Enforce Cascading

We will return to the Data tab, at this point, to make modifications to the primary dataset query to both properly enable the date-related parameters for Year and Month, as well as to add and enable the MDX function that will support the parameterized “look back” capability within the report that our client colleagues have requested.

Before we work specifically with the primary dataset query, however, we will modify our TimeMonth dataset query to support the cascading parameter picklist for the Month parameter, the current absence of which we discussed earlier. As we mentioned before, we seek to enforce cascading so that the appropriate child members (Months) appear within the Month parameter picklist, based upon the selection made in the Year parameter parent level to which they belong.

At this stage, we face a couple of modifications to the TimeMonth dataset. First, we must add a Query Parameter within the dataset for TimeYear, and then we must embed the parameter, via a placeholder, into the syntax. We shall make these modifications within the steps that follow. We will then need to make a relatively minor final adjustment to the TimeYear report parameter, as a reaction to a reset that it performs based upon our addition of the Query Parameter within the TimeMonth dataset, as we shall see.

The dataset query appears, presenting the MDX syntax in Query view, as we saw in earlier steps. Because we are establishing a dependency, based upon the TimeYear dataset, within the TimeMonth dataset, we must establish this dependency within a Query Parameter within the dataset, just as we did on a larger scale within the primary dataset query earlier.

The Query Parameters dialog (currently empty) appears.

Here we add the new “variable” we are associating, via the query syntax, with the TimeMonth parameter picklist, which appears at runtime. It is in this manner that we build in support for cascading of (only) the appropriate child-months.

TimeYear

The Query Parameters dialog appears, with our addition, as shown in Illustration 14.

We are returned to the Data tab, where we can see the original MDX syntax in the Query pane. The MDX appears as depicted in Illustration 15.

All that remains to enforce a cascading TimeMonth parameter is to insert the appropriate syntax into the TimeMonth dataset query. Our overall objective is simply to force a dynamic “filter” based upon the value of the TimeYear parameter (which we have already “declared” within the Query Parameters dialog). We will do this in the steps that follow:

FROM [Adventure Works]

FROM 
   ( 
      SELECT 
         ( 
            STRTOSET(@TimeYear, CONSTRAINED) ) ON AXIS(0) 
         FROM  
           [Adventure Works]
         )

The entire query should appear as shown in Illustration 16 (I have expanded the additional syntax to promote easier understanding).

The query executes, and a data grid becomes populated, as partially depicted in Illustration 17.

We can scroll through the results dataset and easily see that only those Months that are the children of FY2005 (the default TimeYear parameter value) appear. This will support our desired objective, selections within the Month picklist at runtime that are associated with (and only those that are associated with) the year that is selected at runtime within the parent TimeYear parameter. We will ascertain the effectiveness of our handiwork via the performance of a simple Preview later, once we have completed the syntax modification procedures in the next subsection, where we make final syntax modifications - this time to the primary dataset - to embed the “look back” capabilities requested by our client colleagues, based upon the selections they make at run time for year, month, and “number of months to look back.”

Leverage the MDX LastPeriods() Function, Containing the Year and Month Parameter Placeholders

Our last procedure will comprise “turning it all on” by another syntax modification – this time within the query supporting the primary dataset, ProductData. Recall that we have already added query parameters for TimeYear, TimeMonth and LookBackSpan into the ProductData dataset. At this point, we have only to embed the parameters into the query syntax, which we will do in the steps that follow.

The dataset query appears, presenting the MDX syntax in Query view, as shown in Illustration 18.

 ([Date].[Fiscal].[Month].ALLMEMBERS

SELECT NON EMPTY { [Measures].[Internet
Order Quantity], [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY {  (LASTPERIODS(@LookBackSpan,
STRTOMEMBER(@TimeMonth, CONSTRAINED)) * [Sales Reason].[Sales
Reason].[Sales Reason].ALLMEMBERS * [Sales Territory].[Sales Territory
Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME, [Sales Reason].[Sales Reason].[Sales
Reason].KEY, [Sales Territory].[Sales Territory Group].[Sales Territory
Group].KEY ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR,
FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The dataset query appears, with our substitution (circled, once again), within the Query pane as partially shown in Illustration 20.

The query executes, and the data grid again becomes populated, as partially depicted in Illustration 21.

An examination of the query and the data it returns reveals the presence of data for six months (February 2004 through July 2004) which equates to the default “look back” value we have supplied within the query parameter we instituted for LookBackSpan earlier. From the perspective of the Data tab, therefore, our query appears to be performing as expected. We will verify performance of this and other items with which we have worked in the next section, where we will conclude our practice exercise.