1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - OLAP Cube Filter - Test if Items exist

Discussion in 'VBA Macros' started by John Mitchell, Dec 7, 2017 at 11:53 AM.

  1. John Mitchell

    John Mitchell New Member

    Messages:
    2
    I have a filter on an OLAP Cube table in excel

    Code (vb):
    ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields(17).EnableMultiplePageItems = _
    True
    ActiveSheet.PivotTables("cohortCLVTwoYear").PivotFields( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].[CUST_COMBINEDmonthsFromOrder]"). _
    VisibleItemsList = Array( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[01-03_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[04-06_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[07-09_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[10-12_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[13-18_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[19-24_Months]")
    On the whole this works, however i have some data sets that dont have the later items

    i tried to create some error handling that if the above doesnt work, try

    Code (vb):
    ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields(17).EnableMultiplePageItems = _
    True
    ActiveSheet.PivotTables("cohortCLVTwoYear").PivotFields( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].[CUST_COMBINEDmonthsFromOrder]"). _
    VisibleItemsList = Array( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[01-03_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[04-06_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[07-09_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[10-12_Months]", _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[13-18_Months]")
    eventually taking the filter down to ...

    Code (vb):
    ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields(17).EnableMultiplePageItems = _
    True
    ActiveSheet.PivotTables("cohortCLVTwoYear").PivotFields( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].[CUST_COMBINEDmonthsFromOrder]"). _
    VisibleItemsList = Array( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[01-03_Months]")
    However as soon as an error is hit, i get the following error.

    So this is where i am at now, i need to be able to check if the items exist, if they are all there, do the first filter, if one is missing filter without it.

    Could anyone help me write that checker?

    Cheers

    John
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    Hi, welcome to the forum :)

    I'll need bit more info.

    What is the scope of this code?
    How is it triggered and how are you determining what items you want to see?
    Is there a reason for not using slicer?

    Please, upload sample workbook with sanitized data so I can test solution(s) and see exactly what you are trying to accomplish here.
  3. John Mitchell

    John Mitchell New Member

    Messages:
    2
    Hi

    Thank you, i have been using this site for a long time, have usually found my answer myself, but i'm totally stumping my self

    VBA
    The whole vba is triggered by me manually, it loads the sheet from my downloads folder into excel as a datamodel, i have detailed the vba below.
    1. jmitFileLoad()
      1. this loads a file called data1.csv into excel as a dataModel table, it is done this way as sometimes the data set is over a million rows
      2. You'll just need to change the path to the download folder on your PC.
      3. When you run this bit the prompt for file information just state the file has a header and its comma seperated.
    2. jmitcreateSheets()
      1. In the full version this creates about 50 sheets for me, but here will just create two sheets.
    3. jmitCreatePivots()
      1. This macro will add the the pivot table to the sheet and then build the pivot table out
      2. I have included 1 before called 'CohortCustomers just to highlight more happens in the file.
      3. The pivot with filter thats causing me issues is on a sheet called 'cohortCLVTwoYear' it starts in this vba with the commented line 'CLV 2Years
    Data
    The field i am trying to filter on is a time based grouping for customers, its bucketing users in length of relationship.

    This macro is ran against many different files and on some data sets the customer length of relationship isnt long.

    The column CUST_COMBINEDmonthsFromOrder can only ever have the following data

    01-03_Months
    04-06_Months
    07-09_Months
    10-12_Months
    13-18_Months
    19-24_Months
    25+_months

    Some times a data set might not have all fields available, so where one file might have all timestamps above populated another might only have

    01-03_Months
    04-06_Months
    07-09_Months

    I was trying to create a filter using
    VisibleItemsList = Array and say if there is an error trying the complete list try it with one less, until the last item of 01-03_months is present only.

    I tried error handling, but i always get the item doesnt exist in the field, so want to create a check before applying the filter.

    I hope that makes sense.

    I have added two data sets

    data1 - this has the months
    https://drive.google.com/file/d/15IrNxFp9wb9xbxOSo-aoui4_5ZNtfJKm/view?usp=sharing
    01-03_Months
    04-06_Months
    07-09_Months
    10-12_Months
    13-18_Months

    data 2 - this has the months
    https://drive.google.com/file/d/1xZXRvne6qYOrMIjUbBFEz6dfetb9w1KQ/view?usp=sharing
    01-03_Months
    04-06_Months
    07-09_Months

    and the vba script
    https://drive.google.com/file/d/1sr4oO0mWZlCSv5Rm_WTuAiMWND8h1_Eb/view?usp=sharing
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    I've reviewed your code and I'm a bit confused what you are trying to accomplish.

    Since you added the filed to xlPageField... by default all members are visible.
    Code (vb):
        With ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields( _
            "[data1].[CUST_COMBINEDmonthsFromOrder]")
            .Orientation = xlPageField
            .Position = 1
        End With
    No need to specify it through code like you are trying to. Or am I missing something here?

Share This Page