Drillthrough On Multiselect Now Works In Excel 2016 And SSAS 2016

One unadvertised – but still very welcome – feature of Excel 2016 is that it is now possible to do a drillthrough in a PivotTable when there is a multiselect on a filter or a slicer. It only works if you are using SSAS 2016 on the server, or if you’re querying the Excel Data Model/Power Pivot, because the fix needed changes both in Excel and on the server.

In Excel 2013 and earlier, when you try to do a default drillthrough where there is a multiselect on a filter or a slicer, you get the following error message:

image

Show Details cannot be executed when multiple items are selected in a report filter or in a slicer. Select a single item for each field in the report filter area and for each slicer connected to this PivotTable before performing a drillthrough.

For drillthrough actions, where there is a multiselect, you won’t see the action listed under the Additional Actions right-click menu at all.

This is the result of two limitations. First, there’s the issue with the MDSCHEMA_ACTIONS schema rowset that I blogged about here. In SSAS 2016 you can now pass in multiple members from the same hierarchy in the COORDINATE restriction column, as shown in this example I captured in Profiler:

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" 
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<CUBE_NAME>Model</CUBE_NAME>
<ACTION_TYPE>401</ACTION_TYPE>
<COORDINATE>
([DimDate].[DateKey].&amp;[20010701],
[DimDate].[DateKey].&amp;[20010702],
[Measures].[Sum of SalesAmount])
</COORDINATE>
<COORDINATE_TYPE>6</COORDINATE_TYPE>
</RestrictionList>

Second, subselects on a drillthrough MDX query are ignored in SSAS 2014 and earlier. For example, here’s a drillthrough query generated by an Excel 2016 PivotTable with a multiselect slicer connected to an SSAS Tabular model:

DRILLTHROUGH MAXROWS 1000 
SELECT [Measures].[Sum of SalesAmount] ON COLUMNS 
FROM 
(SELECT  FROM (
SELECT 
({[DimDate].[DateKey].&[20010702],[DimDate].[DateKey].&[20010701]}) 
ON COLUMNS  
FROM [Model]))

When run against SSAS 2014, this drillthrough returns records that are not filtered by date; when run against the same model in SSAS 2016, the subselect is respected and the resultset is filtered by the selected dates.

36 thoughts on “Drillthrough On Multiselect Now Works In Excel 2016 And SSAS 2016

  1. Hi,I’ve got both SSAS 2016 and Excel 2016 and drill-through is enabled but not pulling correct results. Does it only works in tabular, or maybe cubes need to be developed in SSDT 2015? Am I doing something wrong? Thanks, Daniel

    • What kind of measure are you using it on? Drillthrough will only ever return correct results for simple sum or count measures. If you are doing something more complicated in your measure, then unfortunately you’re out of luck.

  2. Hi Chris, Thanks for this. The measure it’s fairly simple. It’s a sum of quantity in the fact table, there are two other measures in the cube but they’re also simple sums (one of them has FormatString set on the measure but I don’t think that is the problem).

    The funny thing is that when I double click on the number it actually drill through and displaying correct number of rows, the problem is I’ve got additional drillthrough actions defined and they not work (default action gives me only Unique Keys from the data warehouse whereas in additional actions I’ve added a couple of detail columns from other dimensions as business requires this). Maybe adding additional details in the action causing that issue.

    I probably need to run profiler to see what is actually being passed to SSAS. This is one of the features I was waiting for a long time so if it’s really limited to default actions that would be disappointing.

  3. Hi Chris,

    I used the trace to get MDX queries when drilling through using default action (when you double click) and compared that with MDX generated through custom drill through action added in VS. For obvious reasons I changed the field names but the context is the same. I selected 3 random products from the cube (multi-select) and when used custom action the MDX only included first selected product not the other 2:

    (correct – default)
    DRILLTHROUGH MAXROWS 1000
    SELECT [Measures].[Count of Services] ON COLUMNS
    FROM (
    SELECT
    FROM (
    SELECT (
    { [Product].[Product Name].&[ProductName1], [Product].[Product Name].&[ProductName2], [Product].[Product Name].&[ProductName3] } ) ON COLUMNS
    FROM [Cube]
    )
    )

    (custom action – only returns top 1 product from multi selection)
    DRILLTHROUGH
    SELECT ( [Product].[Product Name].&[ProductName1], [Measures].[Count of Services] ) ON 0
    FROM [Cube] RETURN [$Product].[Product Name],
    [$Organisation].[Name],
    [$Organisation].[Size],
    … and list of other included dimensions as above

    Any ideas?

    Thanks,

  4. Hi Chris,

    Longtime fan of your posts, from over the ocean in Montréal, Canada !

    I have been asked for this multi-value filter drillthrough feature for a while by users and was extremely happy to see your post. Unfortunately, I’m am not able to make it work…

    I explain below in (maybe too much) details the steps I’ve taken to troubleshoot the issue. I have access to MS data platform TSPs and support engineers through our premier support account, so I’ll be able to go further on my own, but I wanted to check with you first if you could pinpoint some error in my thinking since as you mentionned, this feature hasn’t been advertised. Opening cases at MS is VERY time consuming for us even though our tickets get escalated quickly. I promise I’ll come back and post the resolution if I have to contact Microsoft !

    Before you asks, here are our platform versions :
    Excel 2016 MSO 16.0.4390.1000 x64 -> French Canada Locale Id (3084), but I guess it changes nothing
    SSAS 2016 RTM 13.0.1601.5 x64 -> About to install CU1 13.0.2149.0, but I guess it won’t change anything

    Thanks for your help on this issue if you can provide some, and for all your other posts anyway !

    ————–

    Here is what I have done so far (forgive the French samples, I guess you’ll be able to make an adequate translation, not that it matters).

    I make a pretty simple pivot table where I slice and dice in order to have the workorder count (default measure) for a specific year (filtered) / month (filtered) / installation (filterd on rows) / type of work (not filtered on columns)

    Année 2016
    Mois 02

    Nb de bons de travail Type de travail
    Installation CON COR LOG PRO SYS TNR Total général
    17204 43 11 6 3 136 8 207
    Total général 43 11 6 3 136 8 207

    Double-clicking any non-total cell in the table triggers the drillthrough action defined as the default action for this measure group and returns the expected data (e.g. 6 rows for the LOG work type). Note that the cube code has been edited manually to order the returned columns but once again I don’t think it relates to this issue.

    When I profile the drillthrough request, I get something clean like what I would have typed myself :

    DRILLTHROUGH
    MAXROWS 10000
    SELECT
    FROM [SuiviBonsTravail]
    WHERE
    (
    [Date].[Annee].&[2016],
    [Date].[Mois].&[2],
    [Measures].[NombreBonsTravail],
    [OrganisationHisto].[Installation].&[17204],
    [BonTravail].[TypeTravail].&[COR]
    )

    If I keep the same layout and make the month filter multiple (i.e. Mois in 2, 3), the Excel pivot table reacts correctly :

    Année 2016
    Mois (Plusieurs éléments) –> Many members selected

    Nb de bons de travail Type de travail
    Installation CON COR LOG PRO SYS TNR Total général
    17204 107 30 11 8 228 12 396
    Total général 107 30 11 8 228 12 396

    But on the default drillthrough of the same cell, I get an error saying :

    Members, tuples or sets must use the same hierarchies in the function.

    I don’t understand which function we’re talking about since there are no function in use, but the double space between “the” and “function” makes me thinks the error message may not be accurate and no function in concerned…

    The exact MDX profiled from this multiple selection is the (uglier) following MDX :

    DRILLTHROUGH
    MAXROWS 10000
    SELECT [Measures].[NombreBonsTravail] ON COLUMNS
    FROM
    (
    SELECT
    {
    [Organisation].[Installation].&[17204],
    [BonTravail].[TypeTravail].&[LOG]
    } ON COLUMNS
    FROM
    (
    SELECT
    FROM
    (
    SELECT
    (
    {
    [Organisation].[Installation].&[17204]
    }
    ) ON COLUMNS
    FROM
    (
    SELECT
    (
    {
    [Date].[Mois].&[2],
    [Date].[Mois].&[3]
    }
    ) ON COLUMNS
    FROM [SuiviBonsTravail]
    )
    )
    )
    )

    If I simplify the pivot table by moving the work type and location dicing in the filters, the results are good (i.e. the total stays the same at 396) :

    Année 2016
    Mois (Plusieurs éléments)
    Type de travail (Tous)
    Installation 17204

    Nb de bons de travail
    396

    When I try the drillthrough action, it works, but I get 10000 rows of data, the filters aren’t applied correctly anymore !

    Accordingly, the MDX generated is the following :

    DRILLTHROUGH
    MAXROWS 10000
    SELECT [Measures].[NombreBonsTravail] ON COLUMNS
    FROM
    (
    SELECT FROM
    (
    SELECT
    (
    {
    [Date].[Mois].&[2],
    [Date].[Mois].&[3]
    }
    ) ON COLUMNS
    FROM [SuiviBonsTravail]
    )
    )

    I tried understanding things upstream by analysing the discovery requests targeting the MDSCHEMA_ACTIONS, but I wasn’t able to profile them even though I selected Discover Begin and Discover End in the traced events. Do I miss something ?

    I tried running the sample XMLA you provided as I tought it should be sent by Excel and I got results I didn’t expect. I removed schemas and namespaces for clarity.

    Request for a single-value month filter :

    MDSCHEMA_ACTIONS

    Global

    (
    [Date].[Mois].&[2],
    [Measures].[NombreBonsTravail]
    )

    6

    Returns the expected drillthrough action :

    MASGA_S8V1
    Global
    DrillthroughBonsTravail
    256

    (
    [Date].[Mois].&[2],
    [Measures].[NombreBonsTravail]
    )

    6
    Forage des données de bons de travail

    DRILLTHROUGH Select
    (
    [Date].[Mois].&[2],
    [Measures].[NombreBonsTravail]
    )
    on 0 From [Global] RETURN [$Organisation].[Direction],[$Organisation].[UniteAdministrative],[$Organisation].[Installation] + many other attributes

    1

    But request for multi-value month filter (as in your code sample) :

    MDSCHEMA_ACTIONS

    Global

    (
    [Date].[Mois].&[2],
    [Date].[Mois].&[3],
    [Measures].[NombreBonsTravail]
    )

    6

    Returns an error : The ‘Mois’ hierarchy appears more than once in the tuple.

    2
    5

    6
    5

    1
    90

    If I try using a set instead of a cell (for fun !) :

    MDSCHEMA_ACTIONS

    Global

    (
    {
    [Date].[Mois].&[2],
    [Date].[Mois].&[3]
    }
    [Measures].[NombreBonsTravail]
    )

    5

    It doesn’t return anything at all :

    I hope the comment wasn’t too painful to read, thank you if you made it up to here !

    • D’oh! XMLA part now with encoding :

      I tried running the sample XMLA you provided as I tought it should be sent by Excel and I got results I didn’t expect. I removed schemas and namespaces for clarity.

      Request for a single-value month filter :

      <Discover xmlns=”…”>
      <RequestType>MDSCHEMA_ACTIONS</RequestType>
      <Restrictions>
      <RestrictionList xmlns=”…” xmlns:soap=”…”>
      <CUBE_NAME>Global</CUBE_NAME>
      <COORDINATE>
      (
      [Date].[Mois].&[2],
      [Measures].[NombreBonsTravail]
      )
      </COORDINATE>
      <COORDINATE_TYPE>6</COORDINATE_TYPE>
      </RestrictionList>
      </Restrictions>
      <Properties>
      </Properties>
      </Discover>

      Returns the expected drillthrough action :

      <return xmlns=”…”>
      <root xmlns=”…” xmlns:xsi=”…” xmlns:xsd=”…” xmlns:msxmla=”…”>
      <xsd:schema targetNamespace=”…” xmlns:sql=”…” elementFormDefault=”qualified”>

      </xsd:schema>
      <row>
      <CATALOG_NAME>MASGA_S8V1</CATALOG_NAME>
      <CUBE_NAME>Global</CUBE_NAME>
      <ACTION_NAME>DrillthroughBonsTravail</ACTION_NAME>
      <ACTION_TYPE>256</ACTION_TYPE>
      <COORDINATE>
      (
      [Date].[Mois].&[2],
      [Measures].[NombreBonsTravail]
      )
      </COORDINATE>
      <COORDINATE_TYPE>6</COORDINATE_TYPE>
      <ACTION_CAPTION>Forage des données de bons de travail</ACTION_CAPTION>
      <DESCRIPTION />
      <CONTENT>DRILLTHROUGH Select
      (
      [Date].[Mois].&[2],
      [Measures].[NombreBonsTravail]
      )
      on 0 From [Global] RETURN [$Organisation].[Direction],[$Organisation].[UniteAdministrative],[$Organisation].[Installation] + many other attributes
      </CONTENT>
      <APPLICATION />
      <INVOCATION>1</INVOCATION>
      </row>
      </root>
      </return>

      But request for multi-value month filter (as in your code sample) :

      <Discover xmlns=”…”>
      <RequestType>MDSCHEMA_ACTIONS</RequestType>
      <Restrictions>
      <RestrictionList xmlns=”…” xmlns:soap=”…”>
      <CUBE_NAME>Global</CUBE_NAME>
      <COORDINATE>
      (
      [Date].[Mois].&[2],
      [Date].[Mois].&[3],
      [Measures].[NombreBonsTravail]
      )
      </COORDINATE>
      <COORDINATE_TYPE>6</COORDINATE_TYPE>
      </RestrictionList>
      </Restrictions>
      <Properties>
      </Properties>
      </Discover>

      Returns an error : The ‘Mois’ hierarchy appears more than once in the tuple.

      <return xmlns=”…”>
      <root xmlns=”…” xmlns:xsi=”…” xmlns:xsd=”…” xmlns:msxmla=”…”>
      <xsd:schema targetNamespace=”…” xmlns:sql=”…” elementFormDefault=”qualified”>

      </xsd:schema>
      <Exception xmlns=”…” />
      <Messages xmlns=”…”>
      <Error ErrorCode=”3238658084″ Description=”The ‘Mois’ hierarchy appears more than once in the tuple.” Source=”Microsoft SQL Server 2016 Analysis Services” HelpFile=””>
      <Location xmlns=”…” xmlns:ddl2=”…” xmlns:…”…”>
      <Start>
      <Line>2</Line>
      <Column>5</Column>
      </Start>
      <End>
      <Line>6</Line>
      <Column>5</Column>
      </End>
      <LineOffset>1</LineOffset>
      <TextLength>90</TextLength>
      </Location>
      </Error>
      </Messages>
      </root>
      </return>

      If I try using a set instead of a cell (for fun !) :

      <Discover xmlns=”…”>
      <RequestType>MDSCHEMA_ACTIONS</RequestType>
      <Restrictions>
      <RestrictionList xmlns=”…” xmlns:soap=”…”>
      <CUBE_NAME>Global</CUBE_NAME>
      <COORDINATE>
      (
      {
      [Date].[Mois].&[2],
      [Date].[Mois].&[3]
      }
      [Measures].[NombreBonsTravail]
      )
      </COORDINATE>
      <COORDINATE_TYPE>5</COORDINATE_TYPE>
      </RestrictionList>
      </Restrictions>
      <Properties>
      </Properties>
      </Discover>

      It doesn’t return anything at all :

      <return xmlns=”…”>
      <root xmlns=”…” xmlns:xsi=”…” xmlns:xsd=”…” xmlns:msxmla=”…”>
      <xsd:schema targetNamespace=”…” xmlns:sql=”…” elementFormDefault=”qualified”>

      </xsd:schema>
      </root>
      </return>

      • Hi Dominic, it’s difficult to know what’s going on here without being able to run my own tests, but I suspect this is a bug in SSAS or Excel and you will have to open a case. Excel should not be showing an error message like the one you have seen. Is this on Tabular or Multidimensional? If it’s Multidimensional, are you testing with a calculated measure?

  5. Hi Chris,
    Thanks for the reply.
    The drillthrough is done on a Multidimensional database, on a regular mesure (count aggregate function). I already reached out to Microsoft contacts, but I guess I’ll have to open a case as you said.
    The thing that puzzles me is that I wasn’t able to make the simple case multi-select work like you showed in the blog post. I wll try it again with a cube built from scratch with minimal design and see if I can make the multi-select drillthrough work. I’ll let you know then.
    Thank you again for your time.
    Dominic

  6. Hi Dominic,

    I am having a similar issue with Actions and multi select. DId you raise a ticket with Microsoft? If yes, could you please let me know the ticket# and details for me to track. Thanks in Advance.

    -Ananth

    • Hi Ananth

      For now I only submitted the bug to our dedicated Data Platform Solution Architect at MS, and I am waiting for his input before opening it with MS Premier Support since, as mentionned by Chris, the feature is unadvertised…

      I’ll post here when (if!) I have more info, but the ticket will propably be private since it goes through our company’s support account, and I don’t think MS makes this info publicly available.

      Have a good day
      Dominic

      • Just so you know, I didn’t have any success with our internal Microsoft channel, so I built a sample cube based on the World Wide Importers DW database and was able to reproduce the problem.
        I opened an incident with MS Premier Support that our technical account manager will escalate in order to shorten the investigation and possible fix… I’ll let you know when I have more info, but I doubt it will be short term. I could also provide the samble files (.xlsx document and .abf database backup) if you want to look into it in more details.
        Have a good day, thanks again for your help.
        Dominic

      • Thanks Dominic. I did raise a ticket with Microsoft and they have confirmed that this is a Bug with Excel 2016 ( custom drillthrough action with multilple filters not returning all results). They don’t have a date for the fix. Could be a hot fix for Excel

  7. I have encountered the same problem. In my case I have dates in columns and products on rows. Multiselect on slicers does not work apart from the slicer concerning dates. If I remove the dates from the columns, i.e. just have rows with sums, I am able to do a drilldown. The problem, at least in my case, seems to be connected to me having data divided in two dimensions.

    To check if my theory was correct I started a fresh workbook and created a very simple table with date, city, product group and sales. I added it to the data model and made a pivot with date in columns and cities in rows. Then I did multiselect filtering, in this case with ordinary filters and no slicers. The problem was the same, so it was not connected to my rather complex datamodel in the original case or slicers.

    I am not sure if this is a bugg or by design. It seems very odd to limit the possibility to drilldown in this way, so I guess it will be solved in a future release.

  8. Hi
    the drillthrough action does not show up at all for me when I select mutiple values in a filer or slicers (works fine when I select one or all values)
    I have the latest versions of both excel 2016 and sql server 2016

    Show details (out of the box drillthrough) seems to work but custom drilltrough actions does not work

    Is this as expected?
    Is there a property I need to set on the drillthrough action?

  9. Hi
    custom drillthrough action is still not working for me, it does not show up when selecting mutiple values in a filter/slicer
    I have the latest versions of excel 2016 and sql server 2016

    show details (out of the box drillthrough seems to work though)

    Is this as expected or do I need to set a property on the drillthrough action?

  10. yes, I have installed excel from my office365 subscription
    Version 16.0.7341.2035

    What do you mean by work in most cases? I dont manage to get custom drillthrough action to work at all

    • I have Excel version 16.0.7830.1013, and that’s not the latest version available. You need to talk to your Office 365 administrator and ask to be put on a faster release channel so you can get a more recent version.

      • Thanks for getting back!
        I have upgraded to version 1702 (build 7870.2031), which I think is the latest version
        But I still have the same issue
        Out of the box drillthrough (show details) works with muti selection in filter/slicer but customer drillthrough action does not work (the action is hidden when I right click – additional action)
        any ideas? anything I can try?

  11. Hi again
    I created a new test cube in SSDT2015 (the old one was created in SSDT2013 and migrated to SSDT2015)
    this time the custom drillthrough actons shows up but I am getting the error message from pre SSAS/excel 2016:
    “Drilltrough action us not supported when mutiple items are sekect in a report slicer or filter…”

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