I have a filter on an OLAP Cube table in excel

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

The data set that this is filtering is a length of time a person has been a customer.

On some of the brands I work on that are newer, they haven't existed for as long, so don't have some of the later items to filter on such as 19-24_months

I tried to create some error handling that if the above doesn't work, try

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

    ActiveSheet.PivotTables("cohortCLVTwoYear").CubeFields(17).EnableMultiplePageItems = _
    True
ActiveSheet.PivotTables("cohortCLVTwoYear").PivotFields( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].[CUST_COMBINEDmonthsFromOrder]"). _
    VisibleItemsList = Array( _
    "[data1].[CUST_COMBINEDmonthsFromOrder].&[01-03_Months]")

01-03_months will always work.

However as soon as an error is hit when a filter item doesn't exist, i get the following error.

Run-Time error '1004: The Item could not be found in the OLAP Cube

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

upvote
  flag
What do you mean by "I have some data sets that don't have the later items"? You mean items with dates after some month? – Aspasia
1 upvote
  flag
Hi Sorry, i should be clearer. This is a macro that will run over different data sets with the same schema. Some of the tables will have all of the filter fields, its a time based dimension based on length of time as a customer, some newer files I have havent been around for a while, so they dont have the later filter fields. – John Mitchell

0 Answers 11

Not the answer you're looking for? Browse other questions tagged or ask your own question.