Drillthrough On Multiselect Now Works In Excel 2016 And SSAS 2016

Reposted from Chris Webb's blog with the author's permission.

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:

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.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .