Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Actions and Multiselect

with 4 comments

At the beginning of this week a customer asked me why, in a certain third-party client tool that shall remain nameless, they could no longer do a drillthrough when they did a multiselect on a filter axis. It seemed a bit weird to me, and it got weirder when I asked around for ideas and Greg Galloway pointed out that Excel 2007 didn’t show any actions at all when there was a multiselect, and Marco Russo noted that the current beta of Excel 2010 didn’t either. This made me wonder whether the problem was in fact with Analysis Services rather than the client tools…

I didn’t actually know how a client tool worked out what actions were available when, so I did some research and found out that the MDSCHEMA_ACTIONS schema rowset was how it was done. Here’s the documentation on MSDN:
http://msdn.microsoft.com/en-us/library/ms126032.aspx

For example, if a client tool needs to know which actions can be called when a user clicks on a cell in a resultset, then it will execute an XMLA command something like this one on Adventure Works:

   1: <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   2:     <RequestType>MDSCHEMA_ACTIONS</RequestType>
   3:     <Restrictions>
   4:       <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   5:         <CUBE_NAME>Adventure Works</CUBE_NAME>
   6:         <ACTION_TYPE>401</ACTION_TYPE>
   7:         <COORDINATE>([Customer].[Country].&amp;[Australia],[Measures].[Internet Order Quantity])</COORDINATE>
   8:         <COORDINATE_TYPE>6</COORDINATE_TYPE>
   9:       </RestrictionList>
  10:     </Restrictions>
  11:   <Properties>
  12:   </Properties>
  13: </Discover>

You can see that a tuple is being passed into the COORDINATE to indicate which cell we’re interested in. But when there’s a multiselect, which cell in the cube are we actually clicking on? Good question… Different client tools handle multiselect in different ways, and it turns out there’s no way of telling SSAS you’re doing a multiselect in this situation. If you try to pass a set of tuples to the COORDINATE you get no actions returned, for instance.

Having talked this over with Akshai Mirchandani from the dev team, what the client tool needs to do is to make multiple calls to MDSCHEMA_ACTIONS, one for each member selected in the multiselect. It then needs to work out from each of the rowsets returned which actions should be available in the current context – and of course, in this case, there’s a good chance that different client tools will do different things (if they do anything at all). Not ideal.

To be honest, this really needs to be something that is solved in SSAS rather than on the client and the key to solving it properly would be to have a standard way of handling and detecting multiselect in MDX. As Mosha hinted here, it’s something that’s been on the dev team’s radar for a while but it’s still not made it into the product unfortunately. In the meantime, if there are any client tool developers from the Excel team or third parties out there reading this, it would be great if you could at least do something rather than nothing here!

Written by Chris Webb

October 26, 2009 at 2:50 pm

Posted in Analysis Services

4 Responses

Subscribe to comments with RSS.

  1. Hi

    Could you please verify if this still is accurate?
    I have a client that iam working for that wants an URL Action or a Report Action to be created that they can click on in Excel 2010 and open up a SSRS report.
    This works fine as long as I only use one value in any Report Filters, as soon as I chose more than one value in a report filter the action disappears.

    Just want to verify to my client that this is a feature of Excel and that it cant be worked around!

    BR
    /Martin

    Martin

    November 28, 2011 at 2:54 pm

  2. I know this is old but thanks Chris. Ran in to this today, saved me a lot of time trying to see what i did wrong in my design. :)

    devinknight

    June 7, 2012 at 12:45 pm

  3. Dear,
    I’ve made a post on MSDN on drill through in Power Pivot in combination with multi select, hoping there is a yet a good idea on how solving the shortcoming in this great feature. Any ideas? Thank you

    http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/278a9e08-7445-40a1-a77a-4e30c1f466f6

    Dave Kilgaard

    June 6, 2013 at 8:42 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers

%d bloggers like this: