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: bring 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! Thank you!
Code:
Sub Sales_Group()
Dim pt As PivotTable
Dim Field As PivotField
Dim SalesGroup As String
Set pt = Worksheets("LSP Weekly Performance").PivotTables("PivotTable4")
Set Field = pt.PivotFields("[EC_Sales_Group_RePar].[Sales_Unit].[Sales_Unit]")
SalesGroup = Worksheets("INPUT").Range("C1")
Sheets("LSP Weekly Performance").Select
With pt
Field.CurrentPageName = "[EC_Sales_Group_RePar].[Sales_Unit].&[" & SalesGroup & "]"
End With
End Sub
