Pivottable DrilledDown <> ShowDetail

davmun

New Member
Joined
Aug 11, 2018
Messages
2
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.
Code:
            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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,180
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.

Code:
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").PivotItems("North").ShowDetail = False
 
Last edited:

davmun

New Member
Joined
Aug 11, 2018
Messages
2
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

Well-known Member
Joined
Jan 28, 2015
Messages
1,180
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.

Code:
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

Code:
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
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,978
Messages
5,767,426
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top