Search code examples
sqlexcelexcel-formulassasolap

Drillthrough action not available after convert the pivot tables content to formulas

A Pivot table was build based on SSAS Multidimensional Cube. In the Cube a Drillthrough action was defined to show the documents details.

I converted only the content of the report from excel to formulas. This means the filters remains unchanged so the user can update the numbers only using the dropdown from filters.

The pivot table is listed below:

enter image description here

-> I observed that the Drillthrough action is not active anymore if you don't convert also the filters to formulas, just like below:

enter image description here

How can I enable the drillthrough action without to convert the filters to formulas?

Solution

  • I have noticed that if I have multiple items selected in the filter before converting to formulas the drillthrough actions are disabled. This means that the filter is a cubeset instead of a cubemember. I don't know why this is, but I think if you don't convert the filters, it treats them as cubesets regardless of if a single member is selected.

    To get around this issue you could go ahead and convert the filter to formulas and then replace it with your own drop-down using data validation. For whatever attribute you want to filter, make a cubeset and then do cuberanked members. Ex: In cell G2 I have =CUBESET("AWSSASMD","[Product].[Category].children","Categories") In cell G3, I have: =IFERROR(CUBERANKEDMEMBER("AWSSASMD",$G$2,ROW()-2),""). I pull that formula down to 3 or four past the number of items I think I might have.

    Then in cell B2 where I had the filter, I put a data validation. Allow: list Source: $G3:$G8 enter image description here

    Then I can change my data validation drop-down, and my numbers change. And my drillthrough action is still available to me.

    enter image description here

    This works for me in Excel 2016.