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:
[sourcecode language=”text” padlinenumbers=”true”]
<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].&[20010701],
[DimDate].[DateKey].&[20010702],
[Measures].[Sum of SalesAmount])
</COORDINATE>
<COORDINATE_TYPE>6</COORDINATE_TYPE>
</RestrictionList>
[/sourcecode]
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:
[sourcecode language=”text” padlinenumbers=”true”]
DRILLTHROUGH MAXROWS 1000
SELECT [Measures].[Sum of SalesAmount] ON COLUMNS
FROM
(SELECT FROM (
SELECT
({[DimDate].[DateKey].&[20010702],[DimDate].[DateKey].&[20010701]})
ON COLUMNS
FROM [Model]))
[/sourcecode]
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.
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.
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.
That does sound strange. You’re right, you’d need to run a Profiler trace to look at the Drillthrough statement Excel is generating.
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,
What build number do you have for Excel 2016? Are you using the click-to-run version from Office 365?
Hi, The version is: Microsoft Excel 2016 MSO (16.0.4366.1000) 64-bit.
Thanks,
I’m using desktop version not click-to-run….
OK, I can repro this. Let me ask Microsoft and get back to you…
I have unofficial confirmation that this *looks* like a bug. The advice is to open a support case.
Thanks for your effort. Are you willing to open one or point in the right direction – where etc.
I can’t open one, I’m afraid – it’s something you would have to do yourself. If you’re a large company you may have a Microsoft account manager or someone else from Microsoft who can help; if you have an MSDN or Technet subscription I believe you can open a support call through the MSDN/Technet portals too. Otherwise this site seems to be the way to go: https://support.microsoft.com/en-us/assistedsupportproducts?wa=wsignin1.0
Thanks. I’ll try….
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?
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
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
Chris, Daniel,
Did you hear back from microsoft? Do you have the ticket# to track.
Thanks,
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
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.
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?
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?
There are still some bugs that have not been fixed, I believe, but it does work in most cases. Are you sure you have the click-to-run version of Excel though?
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?
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…”
I don’t know, sorry – I think this might be one of the existing bugs that still hasn’t been fixed.
Still no updated I guess?
how can I address this to MS?
I think they are still working on it, and fixes will be rolled out in Excel 2016 click-to-run when they are ready…
Hi guys,
As I mentionned previously, I opened a support ticket at Microsoft regarding the problem. I went through the usual steps of repro, business impact statement and so forth and was finally in discussion with a US support engineer in the Excel product group who stated that what I reported was accepted as a bug and that a patch would be released (in foreseable future in C2R, in the next major release for MSI deployment)
BUT :
A few weeks later, the support engineer came back with what would be the actual resolution, which I transcribed integrally below.
“Hello Dominic,
I had been following up with our Product Group (PG) to get more information around the fix/release. Unfortunately the “fix” is not exactly what you (or I) were hoping for
After much investigation from our PG, the initial resolution was on the MDX Query side (Excel), but it was later found that the query is an unsupported scenario due to a SQL limitation. The drill through query is obtained, but SQL does not take multiple cells as coordinates. The “fix” from an Excel perspective is to add an error message that lets the user know that the “Drill through action is not supported”. Our Excel PG researched this with our SQL PG and due to the limitation it cannot be addressed within the scope of a hotfix, but there is consideration for future changes in larger version releases. They cannot guarantee that it will be changed, rather this will be revisited/reconsidered for the larger release when more testing is feasible for such a change
Assuming no roadblocks, I was told that the above “fix” (message dialog) will be seen in C2R build 16.0.7827.1000, and MSI build 16.0.4521.1000. Once these builds are released, I would like to spend some time to verify that you do see the updated error message in your environment, and if there are any other aspects of the issue that may need to be addressed. I can then take that back to our PG for feedback
Again, my apologies, as you and I were both hoping for a more complete fix. I do understand that our PG weighs these decisions based on the amount of risk to any changes in the source code and maintain the integrity of the application
If you have any questions, please feel free to call or email me anytime to discuss”
Sorry for the bad news, at least he mentionned in a subsequent conversation that :
“Our Excel PG researched this with our SQL PG and due to the limitation it cannot be addressed within the scope of a hotfix, but there is consideration for future changes in larger version releases. They cannot guarantee that it will be changed, rather this will be revisited/reconsidered for the larger release when more testing is feasible for such a change”
Let’s not keep our hopes too high…
Thanks Dominic. Sounds like we will have to wait even longer…