Pivottable DrilledDown <> ShowDetail

[davmun]

I can't seem to figure out how to determine which rows (or columns) in a PivotTable are collapsed/expanded.


PivotItem.ShowDetail works for some tables but as I understand it DrilledDown is the equivalent for the other types of tables - "data model/OLAP tables".




However, while setting the DrilledDown Property appears to work similar to setting ShowDetail, reading the DrilledDown Property doesn't. The DrilledDown property for every PivotItem appears to be always False even if set to true immediately prior.

The following code expands the Pivot Table but the MsgBox never pops up.

            pvtItem.DrilledDown = True
            If pvtItem.DrilledDown = True Then
                 MsgBox ("!!True!! :  " & pvtItem.Name)
            End If

I am certain I am misunderstanding what DrilledDown means or how to access it for a Row "heading". Does anyone know how to step through every configured Row in a "data model" Pivottable and determine if that row is expanded/collapsed?


I did see the following post with zero replies:
https://www.mrexcel.com/forum/excel...illeddown-property.html?highlight=drilleddown




Assumption: PivotField and PivotItem names with square brackets and periods mean the pivottable is a "data model"/"OLAP" pivottable. And that means .ShowDetail doesn't work.

 

[Kamolga]

If what you mean is extand and collapse the rows, this is ShowDetail command. For example, in the code below, I chose to collapse the sales for the North Region. The head of the column in my data was called "Region" in witch I only had North or South. Collapsing the region hide the articles in my case but keeps the total turnover.

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").PivotItems("North").ShowDetail = False

 

[davmun]

Create an identical PivotTable from the exact same source with exact same fields & values EXCEPT put a check in the checkbox "Add this data to the Data Model". I suspect your .ShowDetail code doesn't work.

I believe I understand that .ShowDetail works for certain types of PivotTables. I have code that works using .ShowDetail. But .ShowDetail doesn't work for every type of PivotTable, for some reason. I am curious what the alternative is for these other types of PivotTables. As I understand it the alternative is .DrilledDown, but it works differently at times.
I think you can easily test it yourself. A PivotTable created with the checkbox "Add this data to the Data Model" no longer works with .ShowDetail. It does "work" with .DrilledDown (only when setting, not when reading). So my assumption, based off the vba reference guides is that that checkbox makes the PivotTable/source "OLAP"-ey.


https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotitem-drilleddown-property-excel


https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotitem-showdetail-property-excel


To clarify my question: I relied on the .ShowDetail function previously for some functions but it doesn't seem to work for certain PivotTables and the only alternative I can find is .DrilledDown. Does anyone know a better reference than that MSDN link for how to use .DrilledDown or what it does or what "drilled" means?

Or does anyone know a better alternative to READing if something is collapsed or expanded in the PTables that don't work with .ShowDetail?

I am fairly certain that .ShowDetail and .DrilledDown mean more than just expanded/collapsed (ever double-click a cell in a PivotTable or try Quick Explore!). Unfortunately, expanded/collapsed is all I care about.
So if there is a better way to check for expanded/collapsed please set me straight.

 

[Kamolga]

Hi,

Indeed I tried the Add to Data model, I never noticed the collapsing issue while using data model/pover pivot as I usually do not use the function.

I came to the same conclusion, no problem to collapse and extend but when you go from false to true, stop the macro and read next line, the pivotitem.drilldown status is "false" for vba even though it perfectly extended for the user. Basically I receive the MsgBox with the code below, which makes no sense at all.

Dim pvtitem As PivotItem
    Set pvtitem = ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table1].[Region].[Region]").PivotItems("[Table1].[Region].&[North]")
    pvtitem.DrilledDown = True
            If pvtitem.DrilledDown = False Then
                 MsgBox ("I should never get his message!!!")
            End If

I looked for alternatives and found none, so made an ugly one

Sub IsPivotItemcollapsed()
    Dim pvtitem As PivotItem
    Set pvtitem = ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table1].[Region].[Region]").PivotItems("[Table1].[Region].&[North]")
    
        pvtitem.DrilledDown = True
'WorkAround
    Dim i As Long
        i = pvtitem.Position
    Dim Nextitem As PivotItem
    Set Nextitem = ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table1].[Region].[Region]").PivotItems(i + 1)
            If pvtitem.DataRange.Row <> Nextitem.DataRange.Row - 1 Then
                 MsgBox (pvtitem.Name & "is extended")
            End If
End Sub

Note that this workaround should consider an option for last item, the "ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table1].[Region].[Region]").PivotItems(PivotItems.count)" I believe

 

[Razorman]

I'm encountering the same issue when trying to Expand/Collapse a pivotfield.
OLAP data so it requires using DrilledDown (and not ShowDetail)

You can create individual lines of code to Expand or to Collapse the pivotfield
When you try to create a toggle, then it doesn't work. Always brings back a FALSE for the PivotField.DrilledDown property (even if you immediately set it it to true just before that)

Amazingly, still can't find any solution for this on popular forums now in 2024