Results 1 to 5 of 5

Thread: Set a filter on an "OLAP" Pivot using VBA

  1. #1

    Set a filter on an "OLAP" Pivot using VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Bob Phillips; 2015-03-27 at 01:03 PM. Reason: Tidied up post

  2. #2
    It probably doesn't work because City is a numeric id, perhaps you need another field such as CityName.

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    It probably doesn't work because City is a numeric id, perhaps you need another field such as CityName.
    Quote Originally Posted by Bob Phillips View Post
    It probably doesn't work because City is a numeric id, perhaps you need another field such as CityName.
    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

  4. #4
    Can you post the workbook?

  5. #5
    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]"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •