Claymationator
Well-known Member
- Joined
- Sep 26, 2006
- Messages
- 705
I have an OLAP cube that is tied to tables in our data warehouse. I am trying to create some reports that will allow the user to select certain variables in the summary sheet and manipulate the data that is shown. Because of the large amount of data I am not able to show all of the variables in the actual pivot table (Excel doesn't have enough resources to show the data.) So, what I have is data validation on the summary page where the user can select the store #, and I want that to update the OLAP cube.
I know that there is code out there to update regular pivot tables in Excel, but that seems to only work when the source data is housed in Excel, but not for OLAP cubes. The data that I have seen (http://www.contextures.com/excelfiles.html#Pivot) is like this:
I have tried this, but it doesn't update anything (hence the post here.) I went in and manually recorded me making a change, but I don't follow the syntax very well when it is dealing with the OLAP cubes, so I am not sure how I would change this to be similar to the code posted above:
Recorded Code:
The variable that I changed is the "Store Number", "5295" in this case. So, I have a list of all of the stores and I want the pivot to update based on the selection that the user makes in that dropdown.
Any help is appreciated.
I know that there is code out there to update regular pivot tables in Excel, but that seems to only work when the source data is housed in Excel, but not for OLAP cubes. The data that I have seen (http://www.contextures.com/excelfiles.html#Pivot) is like this:
Rich (BB code):
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Store Club Number"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("B2").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End SubI have tried this, but it doesn't update anything (hence the post here.) I went in and manually recorded me making a change, but I don't follow the syntax very well when it is dealing with the OLAP cubes, so I am not sure how I would change this to be similar to the code posted above:
Recorded Code:
Rich (BB code):
Sub Testing()
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
".[Store Number].[Store Number]").VisibleItemsList = _
Array(".[Store Number].&[5295]")
End SubThe variable that I changed is the "Store Number", "5295" in this case. So, I have a list of all of the stores and I want the pivot to update based on the selection that the user makes in that dropdown.
Any help is appreciated.
