Olap pivot filter set using cell value on multiple sheets

[LuluBelle]

Hi
I've read a number of posts and I've got part way through on this problem but not all the way.

I have a spreadsheet which has a menu page where the user selects a business unit by inputting a value into a cell (using validation dropdown so spelling is correct)
I want this cell selection to drive a number of pivot page filters that sit on several other sheets.

I think my issue is that the pivots all come out of out Data warehouse so they have an olap format.
When I do a macro record I get this format for the field filter I want to change:

ActiveSheet.PivotTables("PivotTable8").PivotFields("[Cost Centre].[Business Unit].[Business Unit]").ClearAllFilters​

Here's what I have so far as sheet code on the sheet with the cell selection.
It's clears all the filters but it won't set them to the cell value. (the cell is called rBusiness_Unit_Change)
No error, it just doesn't do it.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Const BUField As String = "[Cost Centre].[Business Unit].[Business Unit]"
Dim NewBusinessUnit As String
NewBusinessUnit = Range("rBusiness_Unit_Change").Value​

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("rBusiness_Unit_Change").Address Then
​

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(BUField)​

.ClearAllFilters
.CurrentPage = Array("[Cost Centre].[Business Unit].&[NewBusinessUnit]")​

End With​

Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True​

End Sub

Assistance appreciated.
FYI: Using Excel 2010.

 

[LuluBelle]

I've tried these variations to no avail

.CurrentPage = Array("[Cost Centre].[Business Unit].&[" & NewBusinessUnit & "]")
.CurrentPage = Array("[Cost Centre].&[" & NewBusinessUnit & "].&[" & NewBusinessUnit & "]")
.CurrentPage = Array("[Cost Centre].&[" & NewBusinessUnit & "].&[Business Unit]")
.CurrentPage = "[Cost Centre].&[" & NewBusinessUnit & "].&[" & NewBusinessUnit & "]"
.CurrentPage = "[Cost Centre].&[" & NewBusinessUnit & "].&[Business Unit]"

I've got a lot of these stupid pivots & I don't want to have to save multiple versions for each regions and updated the pivots manually.

Any suggestions most welcome.

 

[Jerry Sullivan]

Hi LuLuBelle, You used the macro recorder to ClearAllFilters and posted the result in your OP.

If you can do the same for the action of setting your CurrentPageName, it will be easier to help you with the correct syntax.

 

[LuluBelle]

Hi Jerry
Thanks for the sugestion.
weirdly I can't get the macro I record to run. I get an error on the second instruction.

what it records:
Sub test()
ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"[Cost Centre].[Business Unit].[Business Unit]").ClearAllFilters

ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"[Cost Centre].[Business Unit].[Business Unit]").CurrentPage = _
"[Cost Centre].[Business Unit].&[Corporate]"
End Sub

 

[Jerry Sullivan]

Try CurrentPageName instead of CurrentPage

If that works in replaying your recorded macro, then substitute your variable.

 

[Jerry Sullivan]

Here's some a modified version of your procedure....

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim ws As Worksheet
 Dim pt As PivotTable
 Dim NewBusinessUnit As String
 
 Const BUField As String = "[Cost Centre].[Business Unit].[Business Unit]"
 
 If Target.Address = Range("rBusiness_Unit_Change").Address Then
 
   Application.EnableEvents = False
   Application.ScreenUpdating = False

   On Error Resume Next
   
   NewBusinessUnit = Range("rBusiness_Unit_Change").Value

   For Each ws In ThisWorkbook.Worksheets
     For Each pt In ws.PivotTables
        With pt.PageFields(BUField)
           .ClearAllFilters
           .CurrentPageName = "[Cost Centre].[Business Unit].&[" _
               & NewBusinessUnit & "]"
        End With
     Next pt
   Next ws
 
   Application.EnableEvents = True
   Application.ScreenUpdating = True
   
 End If
 
End Sub

Since you have Excel 2010, have you considered using Slicers to sync together all the PivotTables in your workbook that share the same PivotCache? It would be simpler and more efficient that stepping through each pivot in each worksheet.

 

[LuluBelle]

Thanks again for your help Jerry.
I thought of slicers but sadly not all my pivots use the same cube. I might do it any way & just have several slicers.

I think I've worked out why the code won't work.
It's because it's an OLAP and becuse what I'm trying to change is a page fiter (i.e. the data isn't visisble if it isn't being used.).

After messing around with the code at lot, and searchign the internet, I finally found this:
So for anyone else who's interested you can find more here.
https://support.microsoft.com/en-nz/kb/234700
excerpt below
[h=3]Data Retrieval and Refresh Differences[/h]OLAP databases are organized to facilitate the retrieval and analysis of large amounts of data. Before Excel displays summarized data in a PivotTable report, an OLAP server performs calculations to summarize the data. Only the summarized data is returned to Excel, on an as-needed basis. With non-OLAP external databases, all the individual source records are returned, and then Excel does the summarizing. Consequently, OLAP databases can provide Excel with the ability to analyze much larger amounts of external data.

An OLAP server returns new data to Excel every time you change the view or layout of the PivotTable or PivotChart report. When you use non-OLAP source data, the data is refreshed differently, and various refresh options are available in the PivotTable Options dialog box.

 

[Jerry Sullivan]

The fact that your Pivots use an OLAP source does not prevent you from using a worksheet cell to trigger a VBA event that changes the filters.

The code that I suggested uses .CurrentPageName which only works for OLAP sources. I tested that code on an OLAP source (using different field and pivotitem names) and it worked fine.

What happened when you tried to run the code I suggested?

Put a ' in front of this statement so the error handler doesn't keep the error message from displaying.

' On Error Resume Next

 

[LuluBelle]

Okay, total face palm moment.
I tried your code yesterday (don't know what your time zone is, it was yesterday for me) and it didn't work.
I just tried it again it it worked! Yesterday I'd forgotten the "&" before .&[" (doh)
So you totally rock!
Thanks, so much you've made my life sooo much easier

 

[Jerry Sullivan]

I'm happy to hear that worked for you!