From pivot view to filtered datasheet view (1 Viewer)

Ipem · Nov 15, 2013

Hello all,

I think this should be a quite common request, yet I have find no help on this.

I want to "zoom" to the underliyng data from pivot view. When in excel someone doubleclicks a field in a pivot table, it automatically creates a table containing all the lines that field were made up from. I want to achieve the same behaviour in Access.
I started to think towards a VBA coda, that could be initiated from the form's double click event. It should go to datasheet view with the prpoer filter criterias.
Any ideas?

Ipem · Nov 15, 2013

Hello, I have managed to create a filter string according to the current selection. (On what cell the user douuble-clilck) Previously I knew nothing about pivot related VBA, but a MSDN example script helped a lot.
Here is my subroutine so far:

Code:

Private Sub Form_DblClick(Cancel As Integer)
If Not Me.CurrentView = acCurViewPivotTable Then Exit Sub
 Dim sel As Object
 Dim pivotagg As Variant
 Dim sColMems As String
 Dim sRowMems As String
 Dim sFilters As String
 Dim colfilters() As String
 Dim rowfilters() As String
 Dim i As Integer
 Dim a As Integer
  
 Set sel = Me.PivotTable.Selection
 If TypeName(sel) = "PivotAggregates" Then
    Set pivotagg = sel.Item(0)
    sColMems = BuildFullName(pivotagg.Cell.ColumnMember)
    sRowMems = BuildFullName(pivotagg.Cell.RowMember)
 End If
 colfilters = Split(sColMems, "-")
 rowfilters = Split(sRowMems, "-")
    
 For i = 1 To UBound(colfilters)
     a = InStr(1, colfilters(i), "]")
     sFilters = sFilters & " And " & Mid(colfilters(i), 1, a) & " = " & _
     Mid(colfilters(i), a + 1, Len(colfilters(i)) - a)
 Next
 For i = 1 To UBound(rowfilters)
     a = InStr(1, rowfilters(i), "]")
     sFilters = sFilters & " And " & Mid(rowfilters(i), 1, a) & " = " & _
     Mid(rowfilters(i), a + 1, Len(rowfilters(i)) - a)
 Next
 sFilters = Right(sFilters, Len(sFilters) - 4)
End Sub
Function BuildFullName(PivotMem)
 Dim pmTemp As Variant
 Dim sFullName As String
 sFullName = Split(PivotMem.UniqueName, ".")(0) & PivotMem.Caption

 Set pmTemp = PivotMem
 While Not (pmTemp.ParentMember Is Nothing)
    Set pmTemp = pmTemp.ParentMember
    sFullName = Split(pmTemp.UniqueName, ".")(0) & pmTemp.Caption & "-" & sFullName
 Wend
  BuildFullName = sFullName
End Function

So now I have the filter string, altough it does not handles the case, when the user clicks on a total field.
What is really left, is to go to datasheet view, and filter according to the created filter string.
I'll do it next week, because it's work over in my timezone. After I finish it, I'll post it to the code repository.

Nocies and comments on my approach are appreciated!

From pivot view to filtered datasheet view (1 Viewer)

Ipem

Registered User.

Ipem

Registered User.

Similar threads

Users who are viewing this thread