+ Reply to Thread
Results 1 to 1 of 1

VBA & OLAP Pivot:Code to set report filter pvt field with items from another pivot table

  1. #1
    Registered User
    Join Date
    08-05-2014
    Location
    Seattle, WA
    MS-Off Ver
    2016, 64-byte version
    Posts
    1

    VBA & OLAP Pivot:Code to set report filter pvt field with items from another pivot table

    Hi there! I have a workbook that uses multiple slicers for users to select their dashboard views from an OLAP cube. As you can imagine, this is incredibly slow. I cannot bring the data into PowerPivot because there are too many rows (over 4billion) and Excel/PowerPivot can't handle it and it breaks/won't load the data from the cube. Plus, my clients want CUBE formulas coming directly from the cube, not through PowerPivot. As I cannot seem to find any kind of code that will prevent slicer calculation until all filters/slicers are selected, I am looking for other alternatives to speed up processing time and prevent the workbook from refreshing from the cube every time a choice is made by the user. I have tried all the usual methods (e.g. set to manual calculation, pivot table manual update, PivotCache.EnableRefresh = False, etc.), to no avail. I found a workaround: b[cocring simple data lists into PowerPivot (which would only be about 7 tables, 3-30 rows each), create a simple pivot table with only report filters visible for users to use as a dropdown menu to make their choices. As it is coming from PowerPivot, no refresh from the cube is necessary, so it is fast. With this, I am able to set pivot fields in the OLAP pivot tables using VBA, and refresh all fields at one time, after filter selections are made (going to create a "Refresh Data" button for users to push after making all their selections). Works great....until the user chooses multiple items in the dropdown. As I am not a VBA coding expert, I am not sure how to set the VBA coding so that it will take the "multiple items" value in one pivot table field and set the pivot field in the other pivot table with those multiple items (hopefully, I am explaining it so that it makes sense). I believe I have to use the 'VisibleItemsList = Array' coding but I am not sure how to code it. The code I have listed below works, when only one item is chosen. However, I don't know how to translate this into an array. Assuming I can't set 'SalesGroup' to String as it will just read "Multiple Items", and I am not sure if I should be setting SalesGroup to Range or Variant, and how I write the VisibleItemsList Array code properly. Any help would be appreciated as I would desperately like to get rid of these slicers and speed up the processing time! FYI, I am using Office 2016, 64-byte version. Thank you!

    Here is the code that works for the single item selection:
    Please Login or Register  to view this content.
    Last edited by juliemozz; 07-14-2016 at 03:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Pivot Table Page Filter for OLAP Pivot
    By Bstone2015 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2015, 10:54 AM
  2. Collect and set multiple items into a non-OLAP Pivot Report Filter
    By MNBUARQUE in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2014, 02:56 PM
  3. Replies: 2
    Last Post: 06-02-2014, 10:50 AM
  4. [SOLVED] How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?
    By Rhino_dance in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-28-2013, 11:01 AM
  5. Rename items in report filter in Pivot table
    By jenzz22 in forum Excel General
    Replies: 1
    Last Post: 03-07-2012, 02:36 PM
  6. Pivot table filter data field to show items with count more than n
    By kuraitori in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2010, 07:36 PM
  7. zero values in olap pivot table report
    By johnE in forum Excel General
    Replies: 0
    Last Post: 12-21-2005, 03:45 PM

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