Set a filter on an "OLAP" Pivot using VBA

S

simerw90

New member
Joined
Mar 26, 2015
Messages
2
Reaction score
0
Points
0
Hi Everybody,I'm glad to join the forum.I'm a biginner in vba, and I come here to ask for help.

Here is my problem : I wish to apply a filter on an Olap Pivot filed. When I use VBA recorder, I got the following :
Code: 
sub Macro1              
    ActiveSheet.PivotTables("PivotName").PivotFields( _            
        "[COUNTRY].[CITY]").CurrentPageName = _           
        "[COUNTRY].[CITY].&[124]"   
end sub

This works great. But the problem is that I need to apply the filter using a value as
"[COUNTRY].[CITY].&[Paris]"
and not a cube id as
"[COUNTRY].[CITY].&[124].

I tried the following and it doesn't work :
Code: 
sub Macro1              
    ActiveSheet.PivotTables("PivotName").PivotFields( _            
        "[COUNTRY].[CITY]").CurrentPageName = _           
        "[COUNTRY].[CITY].&[Paris]"   
end sub

and I have tried also :
Code: 
sub Macro1              
    ActiveSheet.PivotTables("PivotName").PivotFields( _            
        "[COUNTRY].[CITY]").CurrentPageName = _           
        "[COUNTRY].[CITY].[Paris]"   
end sub

Really appreciate your help.
 
Last edited by a moderator:
It probably doesn't work because City is a numeric id, perhaps you need another field such as CityName.
 
Bob Phillips said:
It probably doesn't work because City is a numeric id, perhaps you need another field such as CityName.
Click to expand...

Bob Phillips said:
It probably doesn't work because City is a numeric id, perhaps you need another field such as CityName.
Click to expand...

Hi Bob,

Thank you for you answer.
Well, when I use the filter on Cities, I see Citys Names and not their ids.
For information, with Cubemember function, both expressions below works. I'm facing the problem only in VBA.
cubemember("connexion_name","[COUNTRY].[CITY].&[124]"
cubemember("connexion_name","[COUNTRY].[CITY].[Paris]"

Do you have an idea?
Thank you
 
Can you post the workbook?
 
I just knocked up a simple test, and I got different code for changing the filter than you with the macro recorder

Code: 
    ActiveSheet.PivotTables("pvtTest").PivotFields("[Country].[City].[City]"). _
        CurrentPageName = "[Country].[City].&[124]"
 
You must log in or register to reply here.
Back
Top