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 Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

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

Featured Database Articles


Posted Aug 18, 2008

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

By William Pearson

Work with Underlying Dataset Queries to Enforce Cascading and to Support the Parameterized “Top Count” 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 parameterizedtop count” capability within the report, as 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.  From the Data tab, once again, click the downward selector arrow on the right side of the Dataset selector.

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

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

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

5.  Type the following into the selected cell:


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

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

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

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

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

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

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

13.  Remove the following from the existing dataset query:

FROM [Adventure Works]

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

            STRTOSET(@TimeYear, CONSTRAINED) ) ON AXIS(0) 
           [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

15.  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 retrieval 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 enhancements - this time to the primary dataset - to embed the “top count” capabilities requested by our client colleagues, based upon the selections they make at runtime for year, month, and “number of ‘top’ items to retrieve.”

Add Syntax to Enforce the Month Parameter Selection and Leverage the MDX TopCount() Function, Containing the TopCountSpan Parameter Placeholder

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 TopCountSpan 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:


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

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

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


5.  Carefully remove the following syntax from the query:

 [Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS

shown circled in the query presented in Illustration 20.

Illustration 20: Remove the Sales Reason–related Portion of the Query

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

TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan, [Measures].[Internet Sales Amount])

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, 
(STRTOSET(@TimeMonth, CONSTRAINED)   * TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan, 
 [Measures].[Internet Sales Amount])
* [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 ( SELECT ( { [Date].[Fiscal].[Fiscal Year].&[2004], [Date].[Fiscal].[Fiscal Year].&[2005] } ) ON COLUMNS 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 (the replacements have been buffered with extra space for easier viewing), with our substitutions (boxed in red), within the Query pane as shown in Illustration 21.

Illustration 21: The Dataset Query with Our Modifications

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

Illustration 22: The Modified ProductData Dataset (Partial View)

An examination of the query and the data it returns reveals that, 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

Thanks for your registration, follow us on our social networks to keep up-to-date