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

 

[sbmaurer]

I have managed to update the 12 month and the 2 month as long as what I am updateing is the "Month Filter" or "Extended Month Filter"

On one of my sheets what I need to update is actually a "Colum Label" the extended month filter is actually sittling down in the pivot as a colum heading.

I'm not sure where to start on that.


Also when building the string for the filter, I need to change a filter now called "extended date filter"

The macro for this is

   ActiveSheet.PivotTables("PivotTable7").PivotFields( _
        "[Extended Date].[Extended Month Filter].[Extended Month Filter]"). _
        VisibleItemsList = Array( _
        "[Extended Date].[Extended Month Filter].&[2010]&[12]&[Dec 2010]", "", _
        "[Extended Date].[Extended Month Filter].&[2011]&[12]&[Dec 2011]")

Does our original format builder fit with this style.

Our original was

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

It seems like it should but I am having some issues....

Hmmm

 

[Jerry Sullivan]

The date portion is unchanged, but the return string included a bit more than just the date. Try making a couple of additional functions...

Function OLAP_Format_Extended_Date(dtIn As Date) As String
'---builds string to reference OLAP item
'---example: "[Extended Date].[Extended Month Filter].&[2011]&[9]&[Sep 2011]"

    OLAP_Format_Date = "[Extended Date].[Extended Month Filter].&[" & _
        Year(dtIn) & "]&[" & Month(dtIn) & "]&[" & _
        Format(dtIn, "Mmm YYYY") & "]"
End Function

    Filter_Cube_PivotField_Extended _
        pvtField:=Worksheets(Worksht).PivotTables(TableName) _
        .PivotFields("[Extended Date].[Extended Month Filter]"), _
            varArrIn:=Array(sItems)

 

[sbmaurer]

Whats awesome is I realized this right after I sent it, just fixed it!

Thanks, I'll keep you posted as thigs move on.

One thing I was wondering is, is it possible to select lets say "Month Filter" if it is actually down in a colum label in the pivot, not up in the filter area.?

I think we woud have to modify the very very biging code to talk to the pivot differently if it is in the colum label. is that right ?

 

[Jerry Sullivan]

Whats awesome is I realized this right after I sent it, just fixed it! ?

I'm delighted to hear that!

One thing I was wondering is, is it possible to select lets say "Month Filter" if it is actually down in a colum label in the pivot, not up in the filter area.?

I think we woud have to modify the very very biging code to talk to the pivot differently if it is in the colum label. is that right ?

For ordinary PivotTables (not from OLAP sources), the core part of the code works on PivotTable fields regardless of whether they are PageFields, RowFields or ColumnFields.

I don't think that should be affected by the fact that you are using an OLAP source with slightly different referencing of the items- but you'll need to tell me that for sure!

 

[sbmaurer]

Hey!! Things are rolling, I have automated around 9 spreadsheests containing more than 30+ pivots.

Quick question, we used a format builder funtion to get the date format for the cube, now I want to filter by the Quarter. Also one by the year.

The CUBE sees the Quarter is

"[Date].[Quarter Filter].&[2011]&[4]&[Q4 2011]"

It sees the year as

"[Date].[Year Filter].&[2010]"

I am trying to play with the function

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

To figure out how to easily make reference cells to create theses formats.

Any suggestions?

 

[Jerry Sullivan]

Hopefully you can figure out the second one:
"[Date].[Year Filter].&[2010]"

As just a variation of the OLAP_Format_Date function

The first item is a little trickier, because you need to take your dtIn parameter and convert it to a quarter.

There might be an easier way to do that but using a Select Case statement for the month would be one way to convert month into quarter. This would vary by Fiscal Quarter by company. You might try searching on this because it's probably been a asked and answered by the experts).

 

[sbmaurer]

Ahh yess, jumped the gun on the second one. Very easy. LOL, sorry bout that.

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

I'll let you knwo what happens, thank you for your every reaching wisdom !!

 

[Jerry Sullivan]

Make sure to give this function a different name and to update the reminder string that tells you the purpose of the function. It will help you keep your sanity if your project continues to grow.

 

[sbmaurer]

So I just thought that I can type Q4 2011 in cell E6 on "UpdatingIntstructions"

I can get it to return a correct format with the below

Function OLAP_Format_Quarter(qtIn As String)
Dim phrase As String
phrase = qtIn
    OLAP_Format_Date = "[Date].[Month Filter].&[" & _
        Right(phrase, 4) & "]&[" & Mid(phrase, 2, 1) & "]&[" & _
        Left(phrase, 7) & "]"
End Function

Sub Test_OLAP_Format()
    MsgBox OLAP_Format_Quarter(qtIn:=Worksheets("UpdatingInstructions").Range("E6").Value)
End Sub

Thank you !!

 

[sbmaurer]

well actually you will notice that I called it qtIn and dtIn, those need to be the same, lol, also I need to be renameing the function to something different not format date, format quarter would be better,

I was just super excited to show you. LOL


AND I JUST REALIZED YOU CAN EDIT POSTS! HAHA ------Message board rookie !!