Passing Multi-value Parameters to a MDX Query in SSRS

Let’s say our report needed ProductCategory as a parameter.  The ProductCategoryKey in the relational database for Bikes is 1, and our cube member for Bikes is [Product].[Category].&[1].  So we can transform our parameter value to pass into our mdx query like so:

“[Product].[Category].&[”+Cstr(Parameters!ProductCategory.Value) + “]”

Simple right?

BUT, I needed to be able to pass multiple values, which meant I had to take some extra steps before I could parse my string.  If ProductCategory is a multi-value parameter, then Parameters!ProductCategory.Value is an object – an array of values.  So we need to create a single string out of that array, format that string, and then break it up again back into an array since that is what the MDX query is expecting.  Whew!

Here’s the magic:

Split(“[Product].[Category].&["+Replace(Join(Parameters!ProductCategory.Value,"],”)+”]”,”,”,”,[Product].[Category].&["),",")

Cool eh? but, what’s happening here?

Join() is a handy little function that takes our array and joins the values into a string.  We can delimit the values with a string, in this case “],”, like so:

Join(Parameters!ProductCategory.Value, “],”)

Bikes, Components, and Clothing for our parameter values, our string after using the join function will be“1],2],3″.  We can then add an ending bracket to the end via simple string concatenation so now we have “1],2],3]”.

Applying the Replace() function, we replace “,” with “,[Product].[Category].&[“ and now we have “1],[Product].[Category].&[2],[Product].[Category].&[3]“.  Finish with one final string concatenation in the front and we have “[Product].[Category].&1],[Product].[Category].&[2],[Product].[Category].&[3]“

Unfortunately we can’t just pass this into our MDX query.  It’s expecting an array object, not a string!  But luckily, the Split() function is the counterpart to Join() and breaks up our string into that array object we want via the commas as a delimiter.

VBA functions in MDX and DAX

It turns out that the Visual Basic for Applications library functions are a great way to extend the functionality of your MDX statements, allowing you to do some very handy stuff.

Analysis Services uses stored procedure technology to extend the number of built-in MDX functions. It automatically registers two libraries: the Visual Basic for Applications library and the Microsoft Excel worksheet library. Visual Basic for Applications library is registered as a CLR assembly and Excel library – as COM assembly. MDX expressions can use any of supported VBA and Excel functions, as if there were native MDX functions.

However, it comes with a cost of performance,  CLR assemblies are hosted in a separate application domain and in addition to marshaling from native to managed code and back, all the calls are also marshaled across application domains, therefore calling a VBA function is an expensive operation comparing to calling an MDX function.

Here is a list of all the VBA functions available in Microsoft Visual Basic for Applications Language Reference that are supported in MDX; also, the list includes a note when there is functional equivalence with the DAX language.

MDX last day of week with tuples

So, working on a project with the TFS cube, a business requirement needed to retrieve the Last Day of the Week.

Now, in the TFS cube, there is a Date Dimension with two hierarchies already created out of the box.

The problem started when I was trying to use the LastChild function to get the last day of the week from the [Year – Week – Date] hierarchy. This function expects a Member of the hierarchy, and I needed to pass a tuple (or a set of members).

After doing some research and ask a couple of senior colleagues, we found the answer:

GENERATE(
[Date].[Year - Week - Date Hierarchy].[Week]
, TAIL(
DESCENDANTS(
[Date].[Year - Week - Date Hierarchy].CURRENTMEMBER
),1))

By using the Generate function we work with each member of a set separately, and then the function  joins the resulting sets by union. The Tail function is similar to LastChild, but Tail returns a subset from the end of a set and can be used to return the last member of the dimension. The Descendants function is needed to return the set of descendants of a member at a specified level or distance to memory.

MDXLastWeek

Video

Differences between SQL and MDX

I like the way the presenter explains the difference between SQL and MDX.

Understanding these differences is crucial for any developer who is interested in learning how to query OLAP cubes with the MultiDimensional eXpressions (MDX) language.

I found these really useful links and videos to help us get started with the basics: