Pivot table array - PivotItemExists

[Souljacked]

Hello,

I have a functioning array that generates dates from a start date to end date. The array is then passed to the pivot table and everything functions correctly.

However occasionally there are dates generated that are not included in my pivot table and when the routine trys to select them I get an error.

What I want to do is when a pivot item does not exist, the routine will just carry on and try to apply the remaining values in the array.

I've tried to read around the subject but so far found no solutions.

Can anyone help?

'create trade date array
    numDays = enDate - stDate
    
    ReDim dateArr_TradeDate(numDays + 1)
    
    dateArr_TradeDate(0) = "[Trade Date].[Year -  Quarter -  Month -  Date].[Year].&[20" & Right(stDate, 2) & _
    "].&[" & Application.WorksheetFunction.RoundUp(month(stDate) / 3, 0) & "].&[" & (Mid(stDate, 4, 2) * 1) & _
    "].&[20" & Right(stDate, 2) & "." & Mid(stDate, 4, 2) & "." & Left(stDate, 2) & "]"
    
    For x = 1 To numDays
    
    dateArr_TradeDate(x) = "[Trade Date].[Year -  Quarter -  Month -  Date].[Year].&[20" & Right(stDate + x, 2) & _
    "].&[" & Application.WorksheetFunction.RoundUp(month(stDate + x) / 3, 0) & "].&[" & (Mid(stDate + x, 4, 2) * 1) & _
    "].&[20" & Right(stDate + x, 2) & "." & Mid(stDate + x, 4, 2) & "." & Left(stDate + x, 2) & "]"
    
    Next x

'set trade date criteria
    pt.PivotFields("[Trade Date].[Year -  Quarter -  Month -  Date].[Date]").VisibleItemsList = _
        Array(dateArr_TradeDate())

Cheers,
Souljacked

 

[Jerry Sullivan]

You could try the Filter_PivotField function below and use your array as a parameter.

pvtField: The PivotField to be filtered
varItemList: A Variant Array of the items to be Visible
​

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
    Dim strItem1 As String, blTmp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    With pvtField
        For i = LBound(varItemList) To UBound(varItemList)
            blTmp = Not (IsError(.PivotItems(varItemList(i)).Visible))
            If blTmp Then
                strItem1 = .PivotItems(varItemList(i))
                Exit For
            End If
        Next i
        If strItem1 = "" Then
            MsgBox "None of filter list items found."
            Exit Function
        End If
 
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
End Function

Below are two examples of how to call the function.

The first uses an array for the items, which hopefully will work for your array of dates.

Sub Filter_ItemListInCode()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1") _
            .PivotFields("SalesRep"), _
            varItemList:=Array("Adams", "Baker", "Clark")
End Sub

The second example gets the Item list from a named range.

Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1") _
            .PivotFields("SalesRep"), _
            varItemList:=Application.Transpose(Sheets("Sheet2") _
            .Range("SalesRepsToShow"))
End Sub

 

[Souljacked]

Hi Jerry,

Thanks very much for taking the time to reply. The solution works perfectly when I test it on a normal pivot table however when I try to do the same with an OLAP cube it always returns no items found.

I've triple checked field names and array values and they seem to match.

my code:

Sub Filter_ItemListInCode()
    Filter_PivotField _
        pvtField:=Sheets("Sheet4").PivotTables("PivotTable1") _
            .PivotFields("[Dim Legacy].[Dim Legacy].[Dim Legacy]"), _
            varItemList:=Array("[Dim Legacy].[Dim Legacy].&[H]", "[Dim Legacy].[Dim Legacy].&[L]")

macro recorder - changing items manually

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Dim Legacy].[Dim Legacy].[Dim Legacy]").VisibleItemsList = Array( _
"[Dim Legacy].[Dim Legacy].&[H]", "[Dim Legacy].[Dim Legacy].&[L]")

Do you have any idea what could be causing this?

Once again thanks for your help.

Souljacked

 

[Jerry Sullivan]

OLAP cubes are new to me. I did some experimenting and so far haven't found a way to change the Visible state of a single PivotItem from an OLAP source.

It looks the method you've tried of setting .VisibleItemsList using an Array is the primary way to change .Visible states. If that's actually the only way to do it, then the most promising approach would be to revise your Array to only include PivotItems that exist, then set the .VisibleItemsList using your validated Array.

What makes that more challenging is that it appears that when working with an OLAP source, the .PivotItems collection is just the Visible items; whereas with a normal PivotTable the .PivotItems include Visible and Hidden items. There is a .PivotFilter collection that is described to be the Hidden items, but when I've tried to access that, the collection seems to be empty even when some PivotField items are Hidden.

I'm hoping someone else with more experience with OLAP can help provide some insight. If not, I'll try again when the room stops spinning.

 

[Souljacked]

Yes, that is my current method.

Thanks for digging deeper on this. I really appreciate it.

 

[Jerry Sullivan]

Hi Souljacked,

This approach might work. It tests each array element to see if it can be used to set the .VisibleItemsList property without error.

Based on those tests, an array is built representing only the PivotItems in the first array that currently exist in the PivotTable.

Here is the function...

Private Function Filter_Cube_PivotField(pvtField As PivotField, _
        varArrIn As Variant)
    Dim varExists() As Variant
    Dim i As Long, lCount As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    With pvtField
        For i = LBound(varArrIn) To UBound(varArrIn)
            .VisibleItemsList = Array(varArrIn(i))
            If (varArrIn(i) = .VisibleItemsList(1)) Then
                lCount = lCount + 1
                ReDim Preserve varExists(lCount)
                varExists(lCount) = varArrIn(i)
            End If
        Next
        If lCount > 0 Then .VisibleItemsList = varExists
    End With
End Function

Below is an example of how to call the function.
In this example: "[media_type].[All].[This Item Does Not Exist]"
will not be added to the list of validated PivotItems.

Sub Filter_Cube_ItemListInCode()
    Filter_Cube_PivotField _
        pvtField:=ActiveSheet.PivotTables("PivotTable1") _
        .PivotFields("[media_type].[media_type]"), _
            varArrIn:=Array("[media_type].[All].[Daily Paper]", _
                "[media_type].[All].[Radio]", _
                "[media_type].[All].[This Item Does Not Exist]", _
                "[media_type].[All].[Sunday Paper]", _
                "[media_type].[All].[Sunday Paper, Radio]", _
                "[media_type].[All].[TV]")
End Sub

Please let me know if it works for your application.

 

[Souljacked]

Jerry you're a legend. Thank you.

All works as it should.

 

[Souljacked]

Hi Jerry,

I spoke slightly too soon unfortunately.

The code works perfectly when I tap in the array when calling the function as below.

Filter_Cube_PivotField _
pvtField:=ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Maturirty Date].[Year - Quarter - Month - Date].[Date]"), _
varArrIn:=Array("[Maturirty Date].[Year - Quarter - Month - Date].[Year].&[2010].&[3].&[8].&[2010.08.04]", _
"[Maturirty Date].[Year - Quarter - Month - Date].[Year].&[2099].&[3].&[8].&[2099.08.05]")

But when I just tell the function which array to use [as below] I get a type mismatch error on the 'if' statement in the function. Is it possible that I need to define that the array is filled with strings?

    Dim dateArr_MaturityDate As Variant
    Dim stdate As Date
    Dim endate As Date
 
 
    stdate = "01/01/2011"
    endate = "02/02/2012"
 
    numDays = endate - stdate
    ReDim dateArr_MaturityDate(numDays + 1)
 
    dateArr_MaturityDate(0) = "[Maturirty Date].[Year -  Quarter -  Month -  Date].[Year].&[9999].&[4].&[12].&[9999.12.31]"
 
    dateArr_MaturityDate(1) = "[Maturirty Date].[Year -  Quarter -  Month -  Date].[Year].&[20" & Right(stdate, 2) & _
    "].&[" & Application.WorksheetFunction.RoundUp(month(stdate) / 3, 0) & "].&[" & (Mid(stdate, 4, 2) * 1) & _
    "].&[20" & Right(stdate, 2) & "." & Mid(stdate, 4, 2) & "." & Left(stdate, 2) & "]"
 
    For x = 2 To numDays + 1
 
    dateArr_MaturityDate(x) = "[Maturirty Date].[Year -  Quarter -  Month -  Date].[Year].&[20" & Right(stdate + (x - 1), 2) & _
    "].&[" & Application.WorksheetFunction.RoundUp(month(stdate + (x - 1)) / 3, 0) & "].&[" & (Mid(stdate + (x - 1), 4, 2) * 1) & _
    "].&[20" & Right(stdate + (x - 1), 2) & "." & Mid(stdate + (x - 1), 4, 2) & "." & Left(stdate + (x - 1), 2) & "]"
 
    Next x
 
    Filter_Cube_PivotField _
        pvtField:=ActiveSheet.PivotTables("PivotTable1") _
        .PivotFields("[Maturirty Date].[Year -  Quarter -  Month -  Date].[Date]"), _
            varArrIn:=Array(dateArr_MaturityDate)

Sorry I promise I will stop bothering you when I can find a fix

Souljacked

 

[Jerry Sullivan]

Sorry I promise I will stop bothering you when I can find a fix

No worries Souljacked, I'm delighted to see some light at the end at the end of the tunnel!

I see two problems in your calling code.

1. Since dateArr_MaturityDate is already an Array of PivotItems,
you need to drop the Array() on the second parameter and just write:

varArrIn:=dateArr_MaturityDate

2. I believe the type mismatch error is due to the way you are handling the Date data types as if they are Strings in a few places.
Even though you assign the value "01/01/2011" to stdate, the expression Left(stdate, 2) will return "1/" instead of your intended "01".

Below is some code you can use to test this, and experiment with some alternatives using Format()

Sub Test_Date_Format()
    Dim stdate As Date
    stdate = "01/01/2011"
    Debug.Print stdate 'returns "1/1/2011"
    Debug.Print Left(stdate, 2) 'returns "1/"
    Debug.Print Format(stdate, "DD/MM/YYYY")  'returns "01/01/2011"
    Debug.Print Left(Format(stdate, "DD/MM/YYYY") , 2)  'returns "01"
    Debug.Print Format(Month(stdate), "0#") 'returns "01"
End Sub

Hopefully the code will work for you after fixing those two things.

 

[Souljacked]

Hi Jerry,

All works fine now. Thanks very much for your help.

Souljacked