Home VBA - OLAP Cube Filter - Test if Items exist
Reply: 0

VBA - OLAP Cube Filter - Test if Items exist

John Mitchell
1#
John Mitchell Published in 2017-12-07 11:46:31Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31441 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO