Using Dynamic MDX in Reporting Services: Part 1

October 7, 2010
Tags: , ,

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.

4 Responses to “Using Dynamic MDX in Reporting Services: Part 1”

  1. [...] might want to refer back to some of my recent posts about using dynamic MDX in Reporting Services: Part 1 and Part [...]

  2. [...] add a post on this technique to my series on Using Dynamic MDX in Reporting Services soon. (The first post describes the use of StrToMember() and StrToSet() and the second post shows how to use MDX in a dynamic OLE DB [...]

  3. Hi there,

    My user have to enter as parameter several codes. Thanks to your blog I can “translate” them into MDX to use them as fileteer.
    But it may happen that my user enter an erroneous code (which is not part of the possible members. In that case I have an error message.
    I tried to remove the “CONSTRAINED” option of my parameter in my main MDX query but then I loose the cascading parameter possibility.
    Would you have an idea how MDX could handle a code which is not part of its members?

  4. Hi Pascal,

    MDX doesn’t like working in the situation as you describe, as you’ve discovered. Ideally, the user doesn’t have to enter codes, but instead chooses from a drop-down list and then MDX works just fine. There’s probably a creative way to address your situation, but I’d need to ponder on it a bit. Around everything else I have on my plate. But it would make a good blog post, so I’ll see what I can come up with for you before the end of the year.

Leave a Reply