CODEWITHSUNDEEP
excelvba
Louis Nielsen
Louis Nielsen

Reputation: 11

Changing Filter in Pivot Table connected to a OLAP Cube

I have a Pivot Table, that are connected to a OLAP cube. In this table i would like to change the value in the Report filter, depending a a cell value. The report filter i like to change contains Administrators ID numbers which all starts with RS (fx RS34000).

I have tried to use this example, but that dosen't work, and it seems to be because my ID number is not a value. VBA to change Pivot Filter in OLAP cube to a range

The only part I want to change in the code is the ID number "RSxxxxx".

Can someone please help me with this issue?

I have tried this but that dosn't work.

Dim MyArrayRS As Variant
Dim RS As Variant

Set RS = Range("RS_Nummer")

MyArrayRS = "Array(" & """[DimProjektRessource_ProjektAdministrator].[ProjektRessourceNr].&[RS" & RS & "]""" & ")"

Worksheets("KUBE").PivotTables("KUBE_NAV").PivotFields("[DimProjektRessource_ProjektAdministrator].[ProjektRessourceNr].[ProjektRessourceNr]").VisibleItemsList = MyArrayRS

This is the code when I record a macro that change the report filter.

ActiveSheet.PivotTables("KUBE_NAV").PivotFields( _
    "[DimProjektRessource_ProjektAdministrator].[ProjektRessourceNr].[ProjektRessourceNr]" _
    ).VisibleItemsList = Array( _
    "[DimProjektRessource_ProjektAdministrator].[ProjektRessourceNr].&[RS34762]")

Upvotes: 1

Views: 603

Answers (1)

BigBen
BigBen

Reputation: 50008

From the PivotField.VisibleItemsList documentation:

Returns or sets a Variant specifying an array of strings that represent included items in a manual filter applied to a PivotField. Read/write

Currently you have "Array" as part of MyArrayRS: this is just the String "Array" and not the Array function.

Try the following:

Dim RS As Range
Set RS = Range("RS_Nummer")

Dim MyArrayRS As Variant
myArrayRS = Array("[DimProjektRessource_ProjektAdministrator].[ProjektRessourceNr].&[RS" & rs.Value & "]")

Upvotes: 1

Related Questions