Filtering OLAP Pivot Table by array

[krogh101]

Hi all,

I am trying to filter a Pivot Table that has a OLAP source, by multiple ItemId's in the worksheet.

The values that I want to filter by is in cell A2 and down. The list of ItemId's that should be visible in the Pivot Table could be anything from 1 to 500

As a small test i wrote the following line of code, and it works great.

input_SH.PivotTables("PT_INPUT").PivotFields("[Item].[ItemID].[ItemID]").VisibleItemsList = Array("[Item].[ItemID].&[1133339]", "[Item].[ItemID].&[1133340]")

However, when using a variable (ITEMID) I only get a Run-time Error '1004'.

Sub UpdateItemPT()

        Dim ITEMID As Variant
        Dim i As Integer
        
        
        ITEMID = ""
        For i = 2 To input_SH.Range("A2").End(xlDown).Row
            
            If i = 2 Then
                ITEMID = """" & "[Item].[ItemID].[ItemID].&[" & input_SH.Cells(i, 1).Value & "]"""
            Else
                ITEMID = ITEMID & ",""" & "[Item].[ItemID].[ItemID].&[" & input_SH.Cells(i, 1).Value & "]"""
            End If
            
        Next
             
'ITEMID is now: "[Item].[ItemID].&[1133339]", "[Item].[ItemID].&[1133340]"
    
input_SH.PivotTables("PT_INPUT").PivotFields("[Item].[ItemID].[ItemID]").VisibleItemsList = Array(ITEMID)

End Sub

Any help would be appreciated!

 

[krogh101]

Cross-posted here: Filtering OLAP Pivot Table by array