Search code examples
excelvbapivotfilteringolap

OLAP filtering hierarchy confusion


I'm trying to use specified values within my file to determine the filters to apply to my OLAP pivot but I'm struggling to notate the custom array correctly due to the Hierarchy that exists.

When recording I get the below results

ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "[Date].[Date Hierarchy].[Month]"). _
        VisibleItemsList = Array( _
        "[Date].[Date Hierarchy].[Year].&[2019].&[2]" _
        )

The problem piece is this section where it is selecting the Year and month at the same time.

"[Date].[Date Hierarchy].[Year].&[2019].&[2]" _
        )

I've successfully written a similar script where I only need to select the year and month where they are not connected through a hierarchy but the year/month combo is throwing me for loops and cannot be separated.

Dim pt As PivotTable
Dim Field As PivotField
Dim Field2 As PivotField
Dim year As String
Dim month As String


Set pt = Worksheets("Csum").PivotTables("PivotTable1")
Set Field = pt.PivotFields("[CC Date].[CC Year].[CC Year]")
Set Field2 = pt.PivotFields("[CS Date].[CS Month].[CS Month]")
year = Worksheets("Sheet1").Range("B8")
month = Worksheets("sheet1").Range("B9")


Sheets("CashSummary").Select


With pt
    Field.CurrentPageName = "[CC Date].[CC Year].&[" & year & "]"
    Field2.CurrentPageName = "[CS Date].[CS Month].&[" & month & "]"
End With

Solution

  • Function SetFilterOfMonth(yr, mon)
    mFilter = "&[" & yr & "].&[" & mon & "]"
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields( _
            "[CS Date].[CS Date Hierarchy].[CS Year]" _
            ).VisibleItemsList = Array("")
        ActiveSheet.PivotTables("PivotTable3").PivotFields( _
                "[CS Date].[CS Date Hierarchy].[CS Month]" _
                ).VisibleItemsList = Array( _
                "[CS Date].[CS Date Hierarchy].[CS Year]." & mFilter _
                )
    
    End Function
    
    Sub Test()
        mYear = Worksheets("Sheet1").Range("B8")
        mMonth = Worksheets("Sheet1").Range("B9")
        SetFilterOfMonth mYear, mMonth
    End Sub