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

  • 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

John Mitchell

New Member
I have a filter on an OLAP Cube table in excel

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
 
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.
 
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
 
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:
    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?
 
Back
Top