Consulting

Results 1 to 7 of 7

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

  1. #1

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

    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
    Last edited by juliemozz; 07-14-2016 at 12:52 PM.

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    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.
    Be as you wish to seem

  3. #3
    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!

  4. #4
    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

  5. #5
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Glad you got it sorted.
    Be as you wish to seem

  6. #6

    Please clarify

    Quote Originally Posted by Aflatoon View Post
    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

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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