THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Using Dynamic MDX in Reporting Services: Part 1

If you're using Analysis Services as a data source for Reporting Services reports, you can build a simple dataset using the graphical query designer, but you'll want to switch to the generic query designer to create the MDX query string manually when you have more advanced requirements. Using the generic query designer, you can:

· Impose greater control over the sets that you want to add to the rows axis by using set functions.

· Add query-scoped named sets to the query in addition to calculated members. (Calculated members can also be added in the graphical query designer, but not named sets.)

· Build dynamic MDX queries.

When would you need a dynamic MDX query? Whenever you want to modify the query based on a condition known only at run-time, typically based on a parameter value. If you're using the graphical query designer, you can auto-generate the report parameter's query for available values by selecting the Parameter checkbox. When the user selects a value during report execution, Reporting Services passes the unique name for the selection to the query and all is well. However, there might be situations when the user selection doesn't come from the cube, so you must find a way to convert the parameter value into a value that will work with the query.

In a series of posts, I will explore the available options for working with dynamic MDX queries in Reporting Services. By dynamic MDX, I mean that the query can be different each time it executes. In this post, I cover the use of StrToMember() and StrToSet() functions in parameters.

A very common scenario is the requirement to pass dates into a query. If you have a date filter for the report, do you really want users to navigate through a list of dates from the cube as shown below?

Date Parameter List

This list of dates - even if it's arranged hierarchically by month, quarter, and year - is what you get when you build the parameter directly from the date hierarchy in the query designer as shown below.

Graphical Query Designer

Wouldn't a more user-friendly experience allow the user to select a data from a calendar control? I can do this by changing the auto-generated report parameter's data type to a Date/Time data type and clear the "Allow multiple values" check box. I must also change the Available Values setting for the parameter to None. I can set the default value to "No default value" to force the user to make a selection, or do something nice like define an expression to set a date, like =Today().

So far, so good. But the problem now is that the date data type returned by the calendar control cannot be used by the MDX query without some intervention. I need to change the Parameter Value mapped to the query parameter in the Dataset Properties to an expression, like this:

="[Date].[Calendar].[Date].[" + Format(CDate(Parameters!DateCalendar.Value), "MMMM d, yyyy") + "]"

The expression that you use to convert a date like 2/1/2008 to a valid unique name in your Date dimension might look different. My example is specific to the Adventure Works 2008 R2 cube, which requires the date member to look like this: [Date].[Calendar].[Date].[February 1, 2008].

That's fine so far, but the result of this expression is a string and the MDX query requires a member or a set. The autogenerated query already makes this change for you fortunately. However, if you're creating your query manually, you should understand what it's doing, especially if you need to make changes to it.

The autogenerated query looks like this before I make changes:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNS
FROM [Adventure Works])
WHERE ( IIF( STRTOSET(@DateCalendar, CONSTRAINED).Count = 1, 
STRTOSET(@DateCalendar, CONSTRAINED), [Date].[Calendar].currentmember ) ) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I prefer to simplify the query as shown below - removing the text highlighted in red text above. The function does what it says - changes the string (represented by the parameter @DateCalendar) into a set object. I remove the WHERE clause from the query as the FROM clause adequately restricts the query results to cell values related to the selected date. If I need the dimension properties in the report to display something or if I need the cell properties for report formatting, I'll include only the ones I need, but for this example I have removed them all from the query.

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS
FROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNS
FROM [Adventure Works])

You could change the StrToSet() function to a StrToMember() function and get the same result. It's not harmful to leave StrToSet() as it is. It just returns a set of one member in this case-the date from the calendar control which is a valid set. The CONSTRAINED flag is used to prevent an injection attack and requires the expression to resolve to a valid member before the query executes.

One challenge that often confounds people working with MDX queries in the generic query designer is the inability to copy and paste the query into Management Studio for testing when parameters are in the query as shown above. The MDX query editor doesn't support parameters. Teo Lachev (blog | twitter) posted some advice for working with parameterized MDX queries in Management Studio which I encourage you to check out.

In my next post, I'll explain how to use the OLE DB for OLAP provider with dynamic MDX to create a dataset.

Published Thursday, October 07, 2010 10:40 AM by smisner
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Magnus Helmvee (Elvis_Of_BI) said:

I do have severe problems with the dynamic parameters using MDX datasets

1. I have created a MDX dataset that populates the parameter @REPORTMONTH

2. the value of that dataset returns a string like 201101 for january 2011

Then I am trying to alter the generated MDX query from the reportdataset

whith the folowing part of the query instead of the standard "FROM GL_CUBE"

SELECT StrToMember("[Time Dim].[Time Hierarchy].[Year Month].&["+@REPORTMONTH+"]") on 0, FROM [GL_CUBE]

This resturns an error sting the query contains a parameter REPORTMONTH which is not declared

What do i do wrong?

/Magnus

March 3, 2011 6:27 AM
 

smisner said:

Hi Magnus,

It's hard to say exactly but did you add the query parameter to the Dataset Properties?

Also, although it's unrelated, I think you should remove the comma in front of the FROM clause like this:

SELECT StrToMember("[Time Dim].[Time Hierarchy].[Year Month].&["+@REPORTMONTH+"]") on 0 FROM [GL_CUBE]

March 4, 2011 10:04 AM
 

Darren Watkins said:

Hi,

I have been recently referring to your book (Microsoft SQL Server 2008 Reporting Services Step by Step) and noticed there are some surprises residing in the code when attempting to use this in the 2008R2 env. One troublesome one was defining parameters with names that are reserved words such as Date. In 2008R2 as you know, none of the reserved words can be used as parameter names any more. Are you able to provide an update in your books to ensure the reader gets more enjoyment rather than frustration. I've enjoyed reading your book btw.

Cheers,

Darren

December 22, 2011 7:58 PM
 

smisner said:

Hi Darren, the change in 2008 R2 has indeed caused some problems. Unfortunately, there isn't any update for the Step by Step book planned for 2008 R2. As with any product, one cannot expect features described for one version to work the same way in future versions. Hopefully you haven't encountered too many other changes. I'm glad to hear that you've enjoyed the book despite the frustration you experienced!

January 11, 2012 1:57 AM
 

Jane said:

Hi Stacia,

I came across this blog when searching for a way to pass in the report paramter to MDX's member properties.

First, I was not able to assign a default value in the Query Designer to a member properties. As an alternate, I tried using the SSRS parameters!startDate directly in my MDX query like this [Measures].[YearMonth] >=  Parameters!startDate.Value, but got an error. The member properties format looks like "201101" for Jan 2011, etc...

Would you please help shed some light on this?

Thanks,

Jane

January 20, 2012 1:44 PM
 

smisner said:

Hi Jane,

It's not clear to me what you're trying to do. You won't be able to reference an expression like Parameters!startDate.Value in your query. You have to use tokens for the parameter. So if you have a query parameter (which you can see in the Dataset Properties dialog box) called startDate, you can use that in your MDX query as @startDate. In the Dataset Properties dialog box, you can set the value of the query parameter to the value of your report parameter. In other words, you will see startDate in the first column, and use Parameters!startDate.Value in the second column.

Hope that helps!

January 25, 2012 10:13 PM
 

Solomon said:

Hey Stacia,

I have an MDX query from which I developed a report but the default parameters arent running as they should. meaning, I have to click view report to run it instead of the report running on its own using the default parameters.  

Here is the query

SELECT NON EMPTY { [Measures].[NWBudget] } ON COLUMNS,

 NON EMPTY { ([Dim Organization].[Hierarchy].[Org Code].ALLMEMBERS *

  [Dim Employee].[Hierarchy].[Lname].ALLMEMBERS *

  [Dim Employee].[Emp Code].[Emp Code].ALLMEMBERS *

  [Dim Time].[Month].[Month].ALLMEMBERS *

  [Dim Time].[Month Of Year].[Month Of Year].ALLMEMBERS *

  [Dim Time].[Acct Period].[Acct Period].ALLMEMBERS )

            }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@ReportingCurrencyCurrencyKey, CONSTRAINED) ) ON COLUMNS

   FROM ( SELECT ( STRTOSET(@DimTimeYear, CONSTRAINED) ) ON COLUMNS

     FROM ( SELECT ( STRTOSET(@DimOrgRegion, CONSTRAINED) ) ON COLUMNS

       FROM [Environ DW]

                 )

              )

           )

WHERE (

                 IIF( STRTOSET(@DimTimeYear, CONSTRAINED).Count = 1,

                       STRTOSET(@DimTimeYear, CONSTRAINED),

                      [Dim Time].[Year].currentmember ),

                 IIF( STRTOSET(@ReportingCurrencyCurrencyKey, CONSTRAINED).Count = 1,

                       STRTOSET(@ReportingCurrencyCurrencyKey, CONSTRAINED),

                      [Reporting Currency].[Currency Key].currentmember ),

                  IIF( STRTOSET(@DimOrgRegion, CONSTRAINED).Count = 1,

                       STRTOSET(@DimOrgRegion, CONSTRAINED),

                     [Dim Organization].[Region Name].currentmember )

             )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Do you have any tips?

Thanks.

October 21, 2012 1:11 AM
 

smisner said:

Hi Solomon - If you have to click View Report to run your report, does your report run correctly? If so, then your report query is fine. If you have to click View Report, that means one or more of the report parameters does not have a default value set (or does not have it set correctly).

October 28, 2012 8:50 PM
 

Tatyana said:

Thank you, very useful article!

February 14, 2014 12:43 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement