Can set but can't read the drilled down property in PowerPivot pivot field using VBA RRS feed

  • Question

  • Hi, hoping someone can help.

    I have an excel application where I would like the user to be able to build a powerpivot report and then save the layout via VBA to a text file, which can then be retrieved to rebuild the layout at a later date.

    VBA can set the drilleddown (Expand/Collapse) property of a pivot field using the below code , but I can not read the same property.  FALSE is always returned.

    'this works in both Sub and immediate window.
    wsReportSheet.PivotTables("JDDPivot").PivotFields("[RptBuild].[TotLev2].[TotLev2]").Drilleddown = True
    'But this always returns false

    I have spent hours trying to find a workaround but am stumped.

    Is there an alternative way to read whether a field is expanded or collapsed?

    Many thanks.

    • Edited by PQUK Tuesday, October 9, 2018 6:50 PM
    Sunday, October 7, 2018 7:07 PM

All replies

  • Hi PQUK,

    Have you checked the data sources(only support OLAP)? Also, I noticed that the post mentioned, "You cannot set this property if the field or item is hidden." 

    Related reference link:

    Loop through PivotItems and Setting DrillDown to True

    Hopefully it helps you.



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 8, 2018 10:25 AM
  • Hi Simon

    Yes I am using an OLAP cube which is causing the issue.  Not sure why one can set the property but not read the same property in a given circumstance. 

    Just to confirm that being an OLAP cube does mean that it is not possible to read the property and that the PT can't be converted to a standard field???

    I have developed some code which derives whether the field is collapsed or not.  See attached.

    • Edited by PQUK Tuesday, October 9, 2018 7:10 PM
    Tuesday, October 9, 2018 6:55 PM
  • I've developed the attached code which can derive whether a field is expanded or collapsed in most circumstances (see comments).  Attaching in case other users might need to derive this property when using an OLAP field.

    Please advise if there is an easier way, this feels like a very agricultural approach.

      Sub testCollapse1()
      Dim objPFs As PivotFields
      Dim objPF As PivotField
      Dim AllFieldRows, SubTotFieldRows, DataItemFieldRows As Integer
      Dim subtotalsRowCount, PriorFieldSubTotals, ActualRows, CurFieldSubTotals, DataItemPotential, RowCount, PriorAllTot As Integer
      Dim bolCollapsed As Boolean
      Dim RowArr()
    ' The following will return True for collapsed if the number
    ' of data items and preceeding subtotals count equals the number
    ' of rows shown in the pivot table
     Dim i As Integer
     RowCount = 0
      Set objPFs = wsReportSheet.PivotTables("JDDPivot").PivotFields
        For Each objPF In objPFs
           If objPF.Orientation = 1 Then
               RowCount = RowCount + 1
           End If
        Next objPF
        ReDim Preserve RowArr(RowCount - 1)
         For Each objPF In wsReportSheet.PivotTables("JDDPivot").PivotFields
           If objPF.Orientation = 1 Then
               RowArr(objPF.Position - 1) = objPF.Name
           End If
        Next objPF
        bolCollapsed = False
     DataItemPotential = 0
     PriorSubTot = 0
     PriorAllTot = 0
     For j = LBound(RowArr) To UBound(RowArr)
     Set objPF = objPFs(RowArr(j))
            AllFieldRows = 0
            SubTotFieldRows = 0
            DataItemFieldRows = 0
                DataItemPotential = DataItemPotential + objPF.PivotItems.Count
                    For Each cell In objPF.DataRange
                    AllFieldRows = AllFieldRows + 1
                        If cell.PivotCell.PivotCellType = xlPivotCellSubtotal Or cell.PivotCell.PivotCellType = xlPivotCellCustomSubtotal Then
                            SubTotFieldRows = SubTotFieldRows + 1
                        ElseIf cell.PivotCell.PivotCellType = 1 And Len(cell) > 0 Then
                            DataItemFieldRows = DataItemFieldRows + 1
                        End If
                    Next cell
        If DataItemFieldRows + SubTotFieldRows >= AllFieldRows And SubTotFieldRows <= PriorSubTot Then bolCollapsed = True
        'If DataItemFieldRows + SubTotFieldRows = PriorAllTot Then prior field is actually False
        'If DataItemFieldRows is zero then field is defaulted to True but could be Expanded in PT
    'Uncomment next 2 lines to get debug print
            'Debug.Print bolCollapsed & "  AllFieldRows= " & AllFieldRows & _
            "; SubTotFieldRows= " & SubTotFieldRows & _
            "; DataItemFieldRows= " & DataItemFieldRows & _
            "; DataItemPotential= " & DataItemPotential & _
            ";   " & objPF.Name
        PriorSubTot = SubTotFieldRows
        PriorAllTot = DataItemFieldRows + SubTotFieldRows
    Next j
    '    Next i
     End Sub

    Tuesday, October 9, 2018 7:02 PM