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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 27, 2008

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

By William Pearson

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

1.  Click the Data tab once again.

2.  Click the downward selector arrow on the right side of the Dataset selector.

3.  Select the TimeMonth dataset, if necessary.

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.

4.  Click the Query Parameters button on the toolbar, as we did 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.

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

6.  Type the following into the selected cell:

TimeYear

7.  Within the cell to the immediate right of the Parameter cell just populated, labeled Dimension, select Date, just as we did within the same position within the primary dataset query earlier.

8.  Within the cell to the immediate right of the Dimension cell just populated, labeled Hierarchy, select Date.Fiscal, as we did within the same position within the primary dataset query earlier.

9.  Leave the Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy cell just populated, unchecked, once again.

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

11.  Select the FY 2005 member, as we did within the same position within the primary dataset query earlier.

12.  Click OK to accept our selection, and to dismiss the Default selector.

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


Illustration 14: The Query Parameters Dialog, with Our Input ...

13.  Click OK to accept our new parameter addition, and to close the Query Parameters dialog.

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.


Illustration 15: The TimeMonth Dataset Query

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:

14.  Remove the following from the existing dataset query:

FROM [Adventure Works]

15.  Replace the newly removed clause with the following syntax (simply add it to the end of the query):


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


Illustration 16: Modified TimeMonth Dataset Query

16.  Click the Execute Query (“!”) button in the toolbar.

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


Illustration 17: The Dataset with Our Modifications (Partial View)

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.

1.  From our present position on the Data tab, click the downward selector arrow on the right side of the Dataset selector, once again.

2.  Select the ProductData dataset, at the top of the list of datasets.

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


Illustration 18: The Primary Dataset (ProductData) Query

3.  Carefully remove the following syntax from the query:

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

shown circled in the portion of the query depicted in Illustration 19.


Illustration 9: Remove the Date–related Portion of the (Partially Shown) Query

4.  Leaving the cursor in place, type the following substitute syntax into the query:

(LASTPERIODS(@LookBackSpan, STRTOMEMBER(@TimeMonth, CONSTRAINED))

So that the complete query is as follows (cut and paste it into the place of the existing query, if that is easier):

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

Note: The “replacement” syntax is in red in the code above.

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


Illustration 20: The Dataset with Our Modifications (Partial View)

5.  Click the Execute Query button on the toolbar, once again, to run the newly modified query.

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


Illustration 21: The Modified ProductData Dataset (Partial View)

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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