OLAP cube Member Name instead of ID to filter Pivot

483 Views Asked by At

I have pivot table based of an OLAP cube, and I'd like to filter one of the fields based on the value of another cell. The filter works when I use the ID number, but not when I use the name, so I wonder if I have something wrong in my syntax.

This works:

ActiveSheet.PivotTables("Sales").PivotFields("[Buying].[MCH].[MCH3]"). _
    CurrentPageName = "[Buying].[MCH].[MCH3].&[69]"

But this doesn't

ActiveSheet.PivotTables("Sales").PivotFields("[Buying].[MCH].[MCH3]"). _
    CurrentPageName = "[Buying].[MCH].[MCH3].[L Womenswear]"

Even though the ID for L Womenswear is 69. What I'd like is some version of this

ActiveSheet.PivotTables("Sales").PivotFields("[Buying].[MCH].[MCH3]"). _
    CurrentPageName = "[Buying].[MCH].[MCH3].[X]"

Where X is a cell where I could write L Womenswear, or another department. I can get it to work with the ids if necessary, but it would simplify everything if I could use the names.

0

There are 0 best solutions below