VBA code to update pivot filters

[sbmaurer]

I am trying to automatically update multiple pivot tables from one fixed button at the begining of the work book.

I have been able to do it with this code for normal pivot tables. But the pivot tables at work are directly linked to an OLAP query, I have a suspicion that they do not have the same PivotFields.

Do you know how I can assign a pivot field to the OLAP query Pivot table, Is there a way to look up the properties of the OLAP pivot table and then assin the "pviotfield" = to the correct property. ???????????????????



Sub UpDatePivot(Worksht As String, TableName As String)
' CHANGES THE FILTER PIVOT TABLE
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterName As String

Set pvtTable = Worksheets(Worksht).PivotTables(TableName)
Set pvtField = pvtTable.PivotFields("Month") <--------- Unable to get the PivotFields Property Of the Pivot Table Class

filterName = Worksheets("UpdateingInstructions").Range("P10")

For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterName Then
pvtField.CurrentPage = filterName
Exit For
End If
Next pvtItem
End Sub

Sub UpdateAll()
Call UpDatePivot("Top15PL", "15PolymerSales")
Call UpDatePivot("Top15PL", "15PolymerRM")
Call UpDatePivot("Top15PL", "15PolymerCust")
Call UpDatePivot("Top15PL", "15IndustrialSales")
Call UpDatePivot("Top15PL", "15IndustrialRM")
Call UpDatePivot("Top15PL", "15IndustrialCust")
Call UpDatePivot("Top15PL", "15ConsumerSales")
Call UpDatePivot("Top15PL", "15ConsumerRM")
Call UpDatePivot("Top15PL", "15ConsumerCust")
End Sub

 

[Jerry Sullivan]

Hi and Welcome to the Board,

This thread on setting filters for a PivotTable with an OLAP data source might help you.

http://www.mrexcel.com/forum/showthread.php?t=565273

 

[sbmaurer]

Hi Jerry! <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Just wanted to let you know how much this has already helped. Thanks so much. I have been able to change the month filter on the OLAP cube; I have only done so for a single pivot on a single sheet. Like this code, this is essentially yours.

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

Sub Filter_Cube_ItemListInCode()
    Filter_Cube_PivotField _
        pvtField:=ActiveSheet.PivotTables("PivotTable1") _
        .PivotFields("[Date].[Month Filter].[Month Filter]"), _
            varArrIn:=Array("[Date].[Month Filter].&[2011]&[9]&[Sep 2011]")
End Sub

<o></o>
<o> </o>
I am trying to do two things, to further my project.<o></o>
<o></o>
- Create a cell at the beginning of the sheet which will hold the month value or values (Sheet1, A17) or A17:A29<o></o>
- Then pass variables of sheets and pivot names through the function to update multiple pivots on multiple tabs. <o></o>
<o></o>
I am new at this and I feel like I am gaining so much experience every day. I want to thank you so much for directing me to your post.

The code in my original post has the variables that pass through it, I have tried to mess around with the above code to update multiple tabs with no luck.

<o></o>
<o>I am not sure I understand how I can modify your code above to update multiple pivot tables on multiple tabs.</o>
<o></o>
<o>If you have an isight for me, or can direct me where I can learn please do tell. </o>
<o></o>
<o>I have been learning from this web site http://www.jlathamsite.com/LearningPage.htm</o>
<o></o>
<o></o>

 

[Jerry Sullivan]

It's great to hear you're learning so much.
Thanks for sharing the link to Jerry Latham's site - I hadn't visited it until now.

Since you have the code working for one PivotTable on one Worksheet, it shouldn't be too hard to extend that to multiple Worksheets and PivotTables.

Before doing that, a good next step would be to read the month value or values (Sheet1, A17) or A17:A29 and feed them into your function as a variable.

I'm interpreting that you'll put that value or values in this part. Is that right?

varArrIn:=Array("[Date].[Month Filter].&[2011]&[9]&[Sep 2011]")

If you can get that to work for one PivotTable, the step of multiple tables will be easier.

Would you like some help with the date variables, or do you want to puzzle through it?

 

[sbmaurer]

Yes Jerry, That is where I would like to enter the date value.

I was actually tring to do what you speak of earlier, get the date variable to work with one pivot table. I tried a couple different things. If you could provide some help that would be great.

One thing I do not understand, in the Private funtion Filter_Cube_PivotField you wrote to acess the OLAP pivot there are the variables PivotField and VarArrIn, and then in the below Sub Filter_Cube_ItemListInCode() you define those variables.

I guess I don't understand how to make the Pivot Feild a certain pivot table from a certain worksheet and also how to make VarArrIn a certain cell on a certain sheet.

I tried a bunch today like setting new variables in the Sub Filter_Cube_ItemListInCode() such as worksheet and table name, but had no luck.

Please pardon me if I am not using completely correct terminaology.

The main problem I think I see is that I have to call out a certain pivot table on a certain sheet and then make the function update the Cube Filter to a set cell or multiple cells.

Really I believe I am stuck, I had tried a lot today to do what you spoke of above, make at least one pivot recognize that the

varArrIn:=Array("[Date].[Month Filter].&[2011]&[9]&[Sep 2011]")

could end up being something like

varArrIn:=worksheet(sheet1).range("A17"))]")

but I could not figure it out.

Thanks for your help Jerry, I am not sure if I told you this, but I am still a college stident and a financial analyst intern, If I figure out how to automatically update these monthly reports with the click of a button people are going to love me ! LOL

 

[Jerry Sullivan]

...I am not sure if I told you this, but I am still a college stident and a financial analyst intern, If I figure out how to automatically update these monthly reports with the click of a button people are going to love me ! LOL

No doubt, you'll succeed and people will love you!

...I guess I don't understand how to make the Pivot Feild a certain pivot table from a certain worksheet and also how to make VarArrIn a certain cell on a certain sheet.

Let's do the VarArrIn part with 1 Pivot Table/1 date first, then 1 Pivot Table/more than 1 date, and once those parts are working,
we'll take on multiple PivotTables.

To pass the date reference we can make a helper function to convert a date variable into a string that has the OLAP item formatting.
Something like...

Function OLAP_Format_Date(dtIn As Date) As String
'---builds string to reference OLAP item
'---example: "[Date].[Month Filter].&[2011]&[9]&[Sep 2011]"
    OLAP_Format_Date = "[Date].[Month Filter].&[" & _
        Year(dtIn) & "]&[" & Month(dtIn) & "]&[" & _
        Format(dtIn, "Mmm YYYY") & "]"
End Function

Place a Date value in Cell A17 and test the result of calling this macro:

Sub Test_OLAP_Format()
    MsgBox OLAP_Format_Date(dtIn:=Range("A17").Value)
End Sub

Finally for this step, try to get this to filter your OLAP PivotTable.

Sub Filter_Cube_One_Item()
    Dim sItems As String
    sItems = OLAP_Format_Date(dtIn:=Range("A17").Value)
    
    Filter_Cube_PivotField _
        pvtField:=ActiveSheet.PivotTables("PivotTable1") _
        .PivotFields("[Date].[Month Filter].[Month Filter]"), _
            varArrIn:=Array(sItems)
End Sub

I'm not able to test this as I don't have access to an OLAP Cube data source - but hopefully we can figure this out together.

 

[sbmaurer]

Hi Jerry,

I am pleased to inform you that today has brough much success. !

You had indicated that you wanted me to try

"Let's do the VarArrIn part with 1 Pivot Table/1 date first, then 1 Pivot Table/more than 1 date, and once those parts are working,
we'll take on multiple PivotTables."

I was able to update VarArrIn with one date but not with more than one date But I did update multiple pivots on multipe tabs! with that one date!!

Here is my total code that I have entered, including the helper and the format verifier sub.

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
Sub Filter_Cube_ItemListInCode(Worksht As String, TableName As String)
    Dim sItems As String
    sItems = OLAP_Format_Date(dtIn:=Worksheets("UpdatingInstructions").Range("O9").Value)
    
    Filter_Cube_PivotField _
        pvtField:=Worksheets(Worksht).PivotTables(TableName) _
        .PivotFields("[Date].[Month Filter].[Month Filter]"), _
            varArrIn:=Array(sItems)
End Sub
Function OLAP_Format_Date(dtIn As Date) As String
'---builds string to reference OLAP item
'---example: "[Date].[Month Filter].&[2011]&[9]&[Sep 2011]"
    OLAP_Format_Date = "[Date].[Month Filter].&[" & _
        Year(dtIn) & "]&[" & Month(dtIn) & "]&[" & _
        Format(dtIn, "Mmm YYYY") & "]"
End Function
Sub Test_OLAP_Format()
    MsgBox OLAP_Format_Date(dtIn:=Worksheets("UpdatingInstructions").Range("O9").Value)
End Sub
Sub UpdateAll()
Call Filter_Cube_ItemListInCode("Top15PL", "15PolymerSales")
Call Filter_Cube_ItemListInCode("Top15PL", "15PolymerRM")
Call Filter_Cube_ItemListInCode("Top15PL", "15PolymerCust")
Call Filter_Cube_ItemListInCode("Top15PL", "15IndustrialSales")
Call Filter_Cube_ItemListInCode("Top15PL", "15IndustrialRM")
Call Filter_Cube_ItemListInCode("Top15PL", "15IndustrialCust")
Call Filter_Cube_ItemListInCode("Top15PL", "15ConsumerSales")
Call Filter_Cube_ItemListInCode("Top15PL", "15ConsumerRM")
Call Filter_Cube_ItemListInCode("Top15PL", "15ConsumerCust")
Call Filter_Cube_ItemListInCode("RMTrends", "PolymerTrendRM")
Call Filter_Cube_ItemListInCode("RMTrends", "IndustrialTrendRM")
Call Filter_Cube_ItemListInCode("RMTrends", "ConsumerTrendRM")
End Sub

I passed the work sheet vaiable and table name variabe through Filter_Cube_ItemListInCode.

Now trying to get the date to read multiple dates is something that I don't get.

There are 2 main things I need to be able to do. one is select the ast 12months every month. I am ok with having 12 reference cells at the begining of the work sheet, and another is to have 2 dates, a month a year ago and the current month. ie. Nov 2010 and Nov2011. Again I am ok with having 2 reference cells on a page.

I tried things like

Sub Filter_Cube_ItemListInCode(Worksht As String, TableName As String)
    Dim sItems As String
    sItems = OLAP_Format_Date(dtIn:=Worksheets("UpdatingInstructions").Range("O9:O21").Value)

and

Sub Filter_Cube_ItemListInCode(Worksht As String, TableName As String)
    Dim sItems As String
    sItems = OLAP_Format_Date(dtIn:=Worksheets("UpdatingInstructions").Range("O9" & "O21").Value)

But I really didn't try very hard I just went with what I could.

If you and your almight wisodom could provide some direction as to how to refereence multiple cells for the 12 moth and the 2 month, that would be awesome !

Thanks you so much.
<!-- / sig --><!-- edit note -->

 

[Jerry Sullivan]

Hi sbmaurer,

That's great progress - I wasn't expecting you to try to go beyond the step of:
"Let's do the VarArrIn part with 1 Pivot Table/1 date first,..."
I just outlined the other 2 steps so you would see some light at the end of the VBA tunnel.

Nice going getting the multiple pivots, multiple sheets to work!!

If the two processes you describe are all you need the macros to do, then there's no need to have your worksheet list all 12 months or both year over year dates.

You could just have one date and two buttons for:
1. Past 12 months
2. 12 Months Ago / Current Month

If that would work for you, I can take a look at that later tonight.

 

[sbmaurer]

Yea, that would work, we essentially only do the Rolling past 12 months and the month from the year previous vs the current month.

I am still curious as to how I can get the (dtIn) to read multiple cells.

Thanks, for all of your help. You are like an angel!

 

[Jerry Sullivan]

I am still curious as to how I can get the (dtIn) to read multiple cells.

I started with this one because it will give you the most flexibility if you decide later on that you want different date combinations.

If you place dates in Cell A17 and below, it will build a Variant Array that can be fed into the Filter_Cube_PivotField.

The temporary test code will display the OLAP formatted date items in the Immediate Window, so you can get a feel for how that works.

Sub Filter_Multiple_Items()
    Dim vDateItems() As Variant, c As Range
    Dim dtOldest As Date, lCount As Long
    dtOldest = #1/1/1970# 'used to detect blanks, incorrect dates
    
    '---build Variant of OLAP formatted date items
    If Range("A17") = vbNullString Then Exit Sub
    For Each c In Range("A17:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        If (Not IsDate(c)) Or c < dtOldest Then
            MsgBox "Invalid Date found in: " & c.Address
            Exit Sub
        End If
        lCount = lCount + 1
        ReDim Preserve vDateItems(lCount)
        vDateItems(lCount) = OLAP_Format_Date(dtIn:=c.Value)
    Next c
 
'-------------------
'For testing only: displays array items in VBA Editor Immediate Window
    Dim i As Long
    For i = LBound(vDateItems) To UBound(vDateItems)
        Debug.Print vDateItems(i)
    Next i
'-------------------
    Filter_Cube_PivotField _
        pvtField:=ActiveSheet.PivotTables("PivotTable1") _
        .PivotFields("[Date].[Month Filter].[Month Filter]"), _
            varArrIn:=vDateItems
End Sub

Do you want to try modifying this for your Current Month plus One Year Ago Month macro?

You just need to modify it so that instead of building the array with A17 to LastData in Col A,
you build the array with two elements: A17 and A17 minus one year.