Actions and Multiselect

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:

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="">
   5:         <CUBE_NAME>Adventure Works</CUBE_NAME>
   6:         <ACTION_TYPE>401</ACTION_TYPE>
   7:         <COORDINATE>([Customer].[Country].&amp;[Australia],[Measures].[Internet Order Quantity])</COORDINATE>
   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!

11 responses

  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!


  2. Pingback: Drillthrough On Multiselect Now Works In Excel 2016 And SSAS 2016 | Chris Webb's BI Blog

  3. Pingback: Drillthrough On Multiselect Now Works In Excel 2016 And SSAS 2016 - SQL Server - SQL Server - Toad World

  4. I really hope SSAS tabular can, one day, account for filters being used in a CALCULATE measure. For example, if I have a YTD aggregation and a click on a number displayed for this measure in the current month, SSAS should return all the rows contributing the calculation as opposed to the rows for just the current month or quarter. It kills certain Kimball drill-across scenarios when you want to drill down to detail.

    Granted, this is not an easy issue to solve…..

    • I agree – in fact I’ve had this conversation (as have many others) with Microsoft many times. I don’t think it’s possible to make drillthrough give the ‘right’ rows automatically, but it should be possible to specify which rows to display when drilling through.

  5. Pingback: Chris Webb's BI Blog: Drillthrough On Multiselect Now Works In Excel 2016 And SSAS 2016 Chris Webb's BI Blog

Leave a Reply to Steven Neumersky Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: