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