Pivot Table with OLAP Data Source - Collapse Pivotfields with one item in group RRS feed

  • Question

  • Hi,

    This has me stumped.  I have a pivot table connected to an OLAP data source.  I have four row fields:

    1. Cost Center
    2. Expense Category 1
    3. Expense Category 2
    4. Account ID

    In most cases, there is only one Account ID for each Expense Category 2.  For items with multiple accounts in Expense Category 2, I want to show a subtotal for Expense Category 2.  Unfortunately, subtotaling Expense Cat 2 generates subtotals for single items groups.

    I want to use VBA to determine how many Account IDs are included in each Expense Category 2 and collapse groups with single items to hide the subtotal.  For each Expense Cat 2 with multiple Account IDs, I want to leave the group expanded and show the subtotal.

    I tried the following code, but .RecordCount returns zero for each pivotitem.  [Object].[CODE 19].[CODE 19] is the equivalent of Expense Category 2 in the description above.

    Public Sub HideSubtotalRows()
        Dim wkb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim pi As Excel.PivotItem
        Set wkb = ThisWorkbook
        Set ws = wkb.Worksheets("EXPENSES")
        Set pt = ws.PivotTables("IS")
        Set pf = pt.PivotFields("[Object].[CODE 19].[CODE 19]")
        For Each pi In pf.PivotItems
            With pi
                If .RecordCount > 1 Then
                    .DrilledDown = True
                    .DrilledDown = False
                End If
            End With
        Next pi
    End Sub

    Any ideas?


    Tuesday, October 25, 2016 3:49 PM