John Mitchell
New Member
I have a filter on an OLAP Cube table in excel
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
eventually taking the filter down to ...
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
Code:
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:
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:
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.
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