OLAP VBA Filter

[dml5055]

Hi,

I was able to create some code that will create an array and filter the OLAP field with the list of items in the array. However I have not found a solution to exclude items which are not in the field. I am getting the 1004 Error "The item could not be found in the OLAP Cube".

I have tried searching for solution but haven't really found a simple answer for the code to exclude items that don't exist. Below is the code I'm using to populate the array and filter on OLAP. Any help on fixing the error would be greatly appreciated!

Thanks,
Dan

Sub OLAP_Filter2()
 Dim myArray() As Variant
 Dim myR As Range
 
 
 
 Set myR = Sheets(13).Range("A6", Sheets(13).Range("A6").End(xlDown))
 
 
 
 
 ReDim myArray(0 To myR.Cells.Count - 1)
 
 'Populate the array
 
 For i = 0 To myR.Cells.Count - 1
 myArray(i) = "[Product].[SKU Nbr].&[" & myR.Cells(i + 1).Value & "]"
 Next i
 
 ThisWorkbook.Sheets("OLAP").PivotTables("PivotTable5").PivotFields( _
 "[Product].[SKU Nbr].[SKU Nbr]").VisibleItemsList = myArray
 
 
 
 
 
 End Sub

 

[RolandKol]

Hi again Jerry,

Thanks a lot for your patience

yes it does...

But it has SUM / Totals ("Sum of Totals") in the Pivot Value section.
debug names it as [Measures].[Sum of TOTAL]

As I understood, the code
"With Sheet1
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

vItemsToBeHidden = Application.Transpose( _
.Range("A2:A" & lLastRow).Value)
End With
"
Collects values, which filter will be based on....

Still cannot crack it down...

My MACRO to create Pivot:

Sub PivotForReport()


    Sheet1.Select
    
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim LastRow As Long
    Dim pivotS, pivotS1, pivotS2 As String
  
    With Application
    .EnableEvents = False
    .DisplayStatusBar = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    End With
    
    On Error Resume Next
    If ActiveWorkbook.Connections.Count > 1 Then
    Do While ActiveWorkbook.Connections.Count > 1
    ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
    Loop
    End If
    
    Sheets("Pivot1").Delete
    On Error GoTo 0
    
    With Sheet1
    LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
    End With
    
    pivotS = "WorksheetConnection_MainData!$A$4:$AA" & LastRow
    pivotS1 = "WORKSHEET;C:\Users\Invoices\Google Drive\Work Related Docs\[New Test VBA.xlsm]MainData"
    pivotS2 = "MainData!$A$4:$AA" & LastRow
   
    
    Workbooks("New Test VBA.xlsm").Connections.Add2 pivotS, "", pivotS1, pivotS2, 7, True, False
    
    
    'Create the Cache
    
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=ActiveWorkbook.Connections(pivotS))
    
    With ThisWorkbook
        .Sheets.Add(before:=.Sheets(1)).Name = "Pivot1"
    End With
  
    'Create the Pivot table
    Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _
        TableDestination:=Range("A4"), TableName:="reportPivot")

    ActiveWorkbook.ShowPivotTableFieldList = True

    'Adding fields
    With ActiveSheet.PivotTables("reportPivot").CubeFields("[Range].[Project Refn]" _
        )
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("reportPivot").CubeFields("[Range].[Job Name]")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("reportPivot").CubeFields.GetMeasure _
        "[Range].[Account Reference]", xlCount, "Count of Account Reference"
    ActiveSheet.PivotTables("reportPivot").AddDataField ActiveSheet.PivotTables( _
        "reportPivot").CubeFields("[Measures].[Count of Account Reference]"), _
        "Head Count"
    With ActiveSheet.PivotTables("reportPivot").PivotFields( _
        "[Measures].[Count of Account Reference]")
        .Caption = "Head Count"
        .Function = xlDistinctCount
    End With
    ActiveSheet.PivotTables("reportPivot").CubeFields.GetMeasure _
        "[Range].[Net Amount]", xlSum, "Sum of Net Amount"
    ActiveSheet.PivotTables("reportPivot").AddDataField ActiveSheet.PivotTables( _
        "reportPivot").CubeFields("[Measures].[Sum of Net Amount]"), _
        "Sum of Net Amount"
    ActiveSheet.PivotTables("reportPivot").CubeFields.GetMeasure "[Range].[DUE]", _
        xlSum, "Sum of DUE"
    ActiveSheet.PivotTables("reportPivot").AddDataField ActiveSheet.PivotTables( _
        "reportPivot").CubeFields("[Measures].[Sum of DUE]"), "Sum of DUE"
    ActiveSheet.PivotTables("reportPivot").CubeFields.GetMeasure "[Range].[TOTAL]" _
        , xlSum, "Sum of TOTAL"
    ActiveSheet.PivotTables("reportPivot").AddDataField ActiveSheet.PivotTables( _
        "reportPivot").CubeFields("[Measures].[Sum of TOTAL]") 
    With ActiveSheet.PivotTables("reportPivot").CubeFields("[Range].[TOTAL]")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("reportPivot").CubeFields(11).EnableMultiplePageItems _
        = True
    ActiveSheet.PivotTables("reportPivot").PivotFields("[Range].[TOTAL].[TOTAL]"). _
        VisibleItemsList = Array("")
        
    ActiveSheet.PivotTables("reportPivot").DataPivotField.PivotItems( _
        "[Measures].[Distinct Count of Account Reference]").Caption = _
        "Head Count"
    ActiveSheet.PivotTables("reportPivot").DataPivotField.PivotItems( _
        "[Measures].[Sum of Net Amount]").Caption = "GROSS"
    ActiveSheet.PivotTables("reportPivot").DataPivotField.PivotItems( _
        "[Measures].[Sum of DUE]").Caption = "DUE"
    
    Columns("B:E").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Columns("C:D").Select
    Selection.Style = "Currency"
    Range("A1").Select

    With Application
    .EnableEvents = True
    .DisplayStatusBar = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    End With
    
    Call FilterOutBlanksandZeros
    
    MsgBox "Ready"
'*****************************************************
End Sub

 

[RoryA]

Please learn to use code tags when posting code. Thank you.

 

[Kolobok]

Hi Guys,

As I understand the code above my post creates the string with all values, however it is not able to handle values which are higher then 10, as a Filter string has to include "E1", which is absent in this code.

'****************[/code]

Hi, Roland.

I seem to have run into a similar issue: numbers greater than 10 seem to be stored as a string with "E1" (or whatever the number of decimal points there is), even though they show as normal numbers in the pivot table, and seem to also have number DataType in the field, so when I try to set VisibleItemsList property of the field using normal numbers, I get an error that it could not be found.

How did you deal with this problem in the end?

Many thanks

Michael

 

[RolandKol]

Hi / Privet Kolobok,

You probably will not like my answer, but the only workaround I managed to use was:
1) copy all data to a separate sheet,
2) delete 0 and blanks
3) and process the pivot from that point...
it takes much longer, - but I have not managed to find any other way to get rid of 0 values and blanks.

I am not sure if it will help in your project...

Unfortunately, my solution does not involve OLAB anymore....