SSRS Drill-through: passing multiple values to another report (MDX list of values)

When I build large dashboards, I often fall back to basic stored procedures to combine datasets, but also to be able to manipulate parameter values (month, first week, previous week, previous year totals, etc.).
An annoying thing is that when you want to create a drill-though action to another report which uses MDX, you’ll need to magically join these values.

Well…here’s how you could achieve it.
Let’s say I have these three values (keys): XXX, YYY and ZZZ

MDX expects a prefix and a suffix (according to the dimension properties/levels).
So here’s what MDX expects:

[Dim Customer].[Customer Number].&[XXX]
[Dim Customer].[Customer Number].&[YYY]
[Dim Customer].[Customer Number].&[ZZZ]

Important: MDX expects different objects/rows, like you need to use separate rows in VS/SSDT for the default parameter values.

Your dataset returns the basic key values, first step is to join these (including the prefix and suffix):

="[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]"

The results is:

[Dim Customer].[Customer Number].&[XXX],[Dim Customer].[Customer Number].&[YYY],[Dim Customer].[Customer Number].&[ZZZ]

Which seems correct, but MDX isn’t expecting a multi-valued string (compared to SP’s).

Final thing, you’ll need to do is to split it again in order to provide a record/row per value:

=Split("[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]",",")








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s