PDA

View Full Version : [SOLVED] VBA & OLAP Pivot:Code to set report filter pvt field with items from another pvt tabl



juliemozz
07-14-2016, 12:37 PM
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! FYI, I am using Office 2016, 64-byte version. Thank you!

Here is the code that works for the single item selection:


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

Aflatoon
07-18-2016, 05:26 AM
Since your Power Pivot pivot table is also an OLAP pivot, you should simply be able to set the VisibleItemsList of your main OLAP pivot field equal to the VisibleItemsList of the relevant pivotfield in the PP pivot table.

juliemozz
07-18-2016, 10:30 AM
Thank you! As I am fairly new to VBA coding, can you please give me the code to set the VisibleItemList on the second pivot table as I have tried all different combinations and something in my syntax is not correct.

Thank you for your help!

juliemozz
07-18-2016, 12:40 PM
Actually, I figured it out, so I can close this thread out. I used your input to tweak my formula which helped. But then I realized that the underlying problem wasn't my syntax in the VBA coding, it was the fact that my PowerPivot worksheet was named something different than the fields in the OLAP cube. As soon as I did a simple rename of the PowerPivot tab/worksheet/column so all fields matched, everything works great now. For reference for others who may be running into the same issue, here is my code:




Sub Sales_Group()

Dim Ary As Variant


Ary = Sheets("Dropdowns").PivotTables("PivotTable4").PivotFields("[EC_Sales_Group_RePar].[Sales_Unit].[Sales_Unit]").VisibleItemsList

Sheets("LSP Weekly Performance").PivotTables("PivotTable4").PivotFields("[EC_Sales_Group_RePar].[Sales_Unit].[Sales_Unit]").VisibleItemsList = Array(Ary)




End Sub



Thanks again!
J

Aflatoon
07-19-2016, 02:59 AM
Glad you got it sorted. :)

mbassham84
01-31-2018, 03:15 AM
Glad you got it sorted. :)

Hi All,

I'm trying to build something similar and can't seem to quite figure out what you did. VBA is also quite new to me, so unfortunately I have to have everything spelled out quite slow...

Are you actually ONLY using the second part of the code? Or have you combined the two different parts?

Something like this?


Sub Sales_Group()
Dim pt As PivotTable
Dim Field As PivotField
Dim SalesGroup As String
Dim Ary As Variant


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")

Ary = Sheets("Dropdowns").PivotTables("PivotTable4").PivotFields("[EC_Sales_Group_RePar].[Sales_Unit].[Sales_Unit]").VisibleItemsList
Sheets("LSP Weekly Performance").PivotTables("PivotTable4").PivotFields("[EC_Sales_Group_RePar].[Sales_Unit].[Sales_Unit]").VisibleItemsList = Array(Ary)


With pt
Field.CurrentPageName = "[EC_Sales_Group_RePar].[Sales_Unit].&[" & SalesGroup & "]"
End With
End Sub

Thank you!! I think I'm likely just missing one thing but cannot seem to figure out what.

I'm not completely clear on the Array, it is a range, i.e. C1:C8? Or it a selection you've made from an existin pivot table on the Sheets("Dropdowns") page?

Any help is greatly appreciated.

Thanks,
Michael

SamT
01-31-2018, 11:46 AM
t is a range, i.e. C1:C8? Or it a selection you've made from an existin pivot table on the Sheets("Dropdowns") page?
Pivot Table. The VisibleItemsList is an array, so it is written Ary = Array

There is no VBA array Type variable. There is a Variant type variable which can hold any type of information, including an array.

If you write Variant = Range, you are setting the Variant variable to an array of the cell values in the range. The same is true if you write Range =Array. The cells in the Range assume the respective values in the Array.

OTOH, if you write Set Variant = Range, then you are setting the Variant variable equal to the range itself, regardless of the values in the cells. this meas that you treat the Variant variable as if it was actually a Range. You can set the Variable's Cell's color, Font, Bold, etc, just like you can Ranges. Likewise, when you write Set Range =Variant Variable, you are setting all the Ranges Cell's various Properties to same as the variable's cells.