Access Pivot Table Hierarchy In Report Filter

Hi,

Does anyone have any reading material on How to Access Pivot Table Hierarchy In Report Filter?

This describes my problem pretty much.

http://www.mrexcel.com/forum/excel-q...hierarchy.html

Here is the one I posted earlier..

http://www.excelforum.com/excel-char...ml#post4178022

Can't seem to find any documentation on this problem at all what so ever..

The Macro Recorder generates a code like this -

 ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[YEAR]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[QUARTER]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[MONTH]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[WEEK]").VisibleItemsList = Array( _
        "[TIME VIEW].[Date Hierarchy].[WEEK].&[2013]&[1]&[1]", _
        "[TIME VIEW].[Date Hierarchy].[WEEK].&[2013]&[1]&[2]")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[DAY]").VisibleItemsList = Array("")

It will makes it a lot easier if you show us, manualy typed the result in the file you posted in your earlier threat.

Hi oeldere!

Thank you for the reply.

Can you tell me what exactly you want me to type in the Excel? I will do it..

Also, see the file attached for the scenario. What I am trying to do is select a date from the Drop Down on Cell A2, and get all the Pivots to select the Date on the report filter.

This code works perfectly..

Sub UpdatePivot()

    Dim I, WS As Worksheet, PT As PivotTable
    
    For Each WS In ThisWorkbook.Worksheets
        For Each PT In WS.PivotTables
            With PT
                For I = 1 To .PivotFields.Count
                    If .PivotFields(I).Orientation = 3 And .PivotFields(I).Caption = "InvoiceDate" Then
                        .PivotFields("InvoiceDate").ClearAllFilters
                        .PivotFields("InvoiceDate").CurrentPage = Sheets("Sheet1").Range("A2").Text
                    End If
                Next
            End With
        Next PT
    Next WS
    
End Sub

However, my issue is, the Data is coming from a Cube, and the Report Filters show dates as a hirearchy, and which is where my code fails..

So I though of using the Macro Recorder to see what it generates when I select only 2 entries (Week Numbers) from the Report Filter, and it generated this code -

ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[YEAR]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[QUARTER]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[MONTH]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[WEEK]").VisibleItemsList = Array( _
        "[TIME VIEW].[Date Hierarchy].[WEEK].&[2013]&[1]&[1]", _
        "[TIME VIEW].[Date Hierarchy].[WEEK].&[2013]&[1]&[2]")
    ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "[TIME VIEW].[Date Hierarchy].[DAY]").VisibleItemsList = Array("")

Any Help please?