VBA OLAP Pivot Filter for Array

[dversloot1]

Hello,

I've managed to filter OLAP Pivots before if the CurrentFieldName was one option. Now, I have a range of dates that cannot be selected using the between function because the OLAP Cube isn't set up that way. I've set up a date table in my workbook that has all the possible dates between a selected range from a combox in my userform.
The trick is that not all dates will be in the Cube. So I'm hoping to skip the ones that aren't in the pivot until the macro loops through all of my array.

Here is a recorded macro of what I would like to do:
Sub Macro4()
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[PO Details].[PO Receipt].[PO Receipt]").VisibleItemsList = Array( _
"[PO Details].[PO Receipt].&[2015-01-06T00:00:00]", _
"[PO Details].[PO Receipt].&[2015-01-07T00:00:00]", _
"[PO Details].[PO Receipt].&[2015-01-08T00:00:00]", _
"[PO Details].[PO Receipt].&[2015-01-09T00:00:00]", _
"[PO Details].[PO Receipt].&[2015-01-10T00:00:00]", _
"[PO Details].[PO Receipt].&[2015-01-12T00:00:00]", _
"[PO Details].[PO Receipt].&[2015-01-13T00:00:00]")
End Sub

My attempt is the following but it doesn't handle errors (Date not in cube)

Sub Macro3()


Dim a, b, c, d, e, f, g As String


a = "[PO Details].[PO Receipt].&[" & Format(Application.WorksheetFunction.VLookup(UserForm1.CBDate.Value, Sheets("Lookup").Range("D:K"), 2, False), "yyyy-mm-dd") & "T00:00:00]"
b = "[PO Details].[PO Receipt].&[" & Format(Application.WorksheetFunction.VLookup(UserForm1.CBDate.Value, Sheets("Lookup").Range("D:K"), 3, False), "yyyy-mm-dd") & "T00:00:00]"
c = "[PO Details].[PO Receipt].&[" & Format(Application.WorksheetFunction.VLookup(UserForm1.CBDate.Value, Sheets("Lookup").Range("D:K"), 4, False), "yyyy-mm-dd") & "T00:00:00]"
d = "[PO Details].[PO Receipt].&[" & Format(Application.WorksheetFunction.VLookup(UserForm1.CBDate.Value, Sheets("Lookup").Range("D:K"), 5, False), "yyyy-mm-dd") & "T00:00:00]"
e = "[PO Details].[PO Receipt].&[" & Format(Application.WorksheetFunction.VLookup(UserForm1.CBDate.Value, Sheets("Lookup").Range("D:K"), 6, False), "yyyy-mm-dd") & "T00:00:00]"
f = "[PO Details].[PO Receipt].&[" & Format(Application.WorksheetFunction.VLookup(UserForm1.CBDate.Value, Sheets("Lookup").Range("D:K"), 7, False), "yyyy-mm-dd") & "T00:00:00]"
g = "[PO Details].[PO Receipt].&[" & Format(Application.WorksheetFunction.VLookup(UserForm1.CBDate.Value, Sheets("Lookup").Range("D:K"), 8, False), "yyyy-mm-dd") & "T00:00:00]"

ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[PO Details].[PO Receipt].[PO Receipt]").VisibleItemsList = Array(a, b, c, d, e, f, g)
End Sub

Can someone help me out?

Dan

 

[dversloot1]

Jerry!

I've found the issue with the code. One small change and it worked fine.

I changed:
sDateString = "[PO Details].[PO Receipt].&[" & _
Format(vDateList(lNdx, 2), "yyyy-mm-dd") & "T00:00:00]"

To this:


sDateString = "[PO Details].[PO Receipt].&[" & _
Format(vDateList(1,lNdx), "yyyy-mm-dd") & "T00:00:00]"</pre>

 

[Jerry Sullivan]

Good sleuthing Dan!

I was puzzled why we were getting different results and now I realize why.

Since I didn't have access to your cube, I was testing this on some other cube data I had and swapping in the statements with the different field names. I fixed the bug you found so it worked on my mockup data on before my first post of the suggested code, but failed to notice I hadn't done the same fix to the statements for your data.

 

[dversloot1]

All good!

I'm happy it's working now.

Thank you so much Jerry!

 

[ScanGuard]

Hi Jerry,

I came across this post of yours - its exactly what i am looking for - almost

I am not a great at vba, but learning all the time.

The code appears to be using a form.

I am not sure what this line is doing.
"sPO = UserForm1.CBDate.Value"

and this has me stumped as well
"ReDim vFilterArray(1 To 7) '--check if pivotitem exists in cube for each date.
' build array of pivotitem that do exist
For lNdx = 1 To 7"

I have a list of items that appear in a catalogue.

I want to filter my pivottable to show just those items.

the dimension to filter is called "[Products].[Item Hierarchy].[Item]"

the list of items appears in a named range called "FilterList"

could you show me how to modify your code accordingly ?

The dimension to filter is in the page area.

I also wish to filter the same pivottable on a dimension in the column area. Does the code need to be changed for the different area ?

Thanks in advance.