+ Reply to Thread
Results 1 to 2 of 2

Setting (OLAP) pivot filter from VBA

  1. #1
    Registered User
    Join Date
    10-12-2011
    Location
    Helmond, Holland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Setting (OLAP) pivot filter from VBA

    Trying to set the value of a filter a pivot table (SSAS OLAP-cube data-source) by program, I started macro-recorder and then selected from the filter drop-down manually. Manual selection from the drop-down list of about 9000 entries, refreshed the pivot with the correct data in less than 1 second.

    Running exactly the self same macro hangs (or takes longer than 10 minutes when I esc-aped out; at that point I get Error 7 Out of Memory, incidently!)

    Complete macro-code:

    Sub Macro1()
    ActiveSheet.PivotTables("PivotTable3").PivotFields( _
    "[Dw Worked Weeks].[Filter BSN].[Filter BSN]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields( _
    "[Dw Worked Weeks].[Filter BSN].[Filter BSN]").CurrentPageName = _
    "[Dw Worked Weeks].[Filter BSN].&[095990288]"
    End Sub

    I tried setting .ManualUpdate and .EnableItemSelection, but neither helped in any True/False combination. Any ideas please?

  2. #2
    Registered User
    Join Date
    10-12-2011
    Location
    Helmond, Holland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Setting (OLAP) pivot filter from VBA

    ... just discovered that the "hang" is at the ClearAllFilters step. If I comment out that step, it all works fine. But why is that step in the recorded macro ?? Am I likely to get bitten if I just drop the ClearAllFilters step, do you think?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1