PivotTable.DrillDown and filtering the output

[Bacchus]

Hello all,

I'm hoping for some assistance with my 2007 pivot table.

I would like the output of my drill down to be filtered to only show records where the drill down field value is 1. For example, I drill down into column field metric1 and all records for metric1 are filtered to only show values equal to 1. Since there are multiple columns (and potentially future additional columns) it would be helpful if I could specify which pivot columns are subjected to this filtering.

Appreciate any direction or code you might share.
Cheers!

[p45cal]

I think you might be talking about an unexpected behaviour of Excel's pivot tables where if you have filtered a field in the Labels area by using Value filters, when you drill down on say the Grand Total, you get all the data, not the filtered data. You may have to add the same field you've got in the Values area to one of the Labels area and filter on that using Label filters.
Otherwise, it's best to supply a file and clearly explain what you want versus what you're getting.

[Bacchus]

You are exactly right. I am encountering a known issue with 2007 drill down functionality. I've tried researching this, exhaustively, and could not find much more than the solution posted on Contextures. That solution is referencing 2010 though and since 2007 doesn't have slicers it wasn't much of a work around. Even adding fields I want to drill into to the label area doesn't quite do the job.

After almost giving up it occurred to me that I could probably achieve the result I want using VBA which has led me here.

I've attached an example 2007 xlsb file. What I'm hoping for is some code that will filter the drill detail so that only values equal to 1 show. For example, if I drill into the Count of Metric2 for TEAM_MEMBER ASHLEY and Product C, the 32 records that populate the new drill detail tab will auto filter on Metric2 to only show values = 1 (which would be 12, the same count that shows in the pivot table).

Ideally, it would be helpful if I could specify which field is subjected to this autofilter rule. It will always filter to show a value of 1 but the fields may change. Using the example file again, Metric1, Metric2, Metric4, Metric5, Metric6, and Metric7 would need to follow the rule. If I add additional metric fields to the base data I would need to determine if this rule also applies to them and update the code accordingly.

Hope this helps. Appreciate any direction on this! Cheers!

[p45cal]

Let's get started. The quickest way I found manually to get what you want is (using your Metric2 example) to add Metric2 to the RowLabels area and use the Labels Filter to filter for =1. Now when you double-click any cell in the Values area you get your filtered drilldown. This is what I was suggesting in my previous msg.
See attached.

[Bacchus]

I understand where you're going but adding all of the metrics to the rowlabel tab that need to have accurate drill down detail presented is not a viable option for me in this scenario.

The example file I presented is just a facsimile of a much larger report that will be distributed to end users who are not terribly savvy with excel which is where the pivot table comes into play. Unfortunately we are stuck using 2007. The initial approach was to provide further instructions to end users that filtering the drill down detail of the metric they want to view would be necessary but then I thought there must be a way to do that using VB.

[p45cal]

Just to keep you abreast of things, I'm currently exploring (when I get the time) doing the following in vba:
When you double-click a cell in the Values area of a pivot table…

interrupt the normal DrillDown creation of a new sheet (done)
add a new row label corresponding to the header of the double-clicked cell (done)
filter that new row label for just 1 (done)
programmatically double-click the equivalent cell to that which you originally double-clicked (to get a new drilldown sheet properly filtered for 1s) (not yet done)
remove the added row label to restore the pivot to how it was (done)

That's all.

[Bacchus]

That is great news. I really appreciate the assistance!

[p45cal]

While I haven't yet found a robust way there's this solution which does things in a different way. Put the following code in the pivot sheet's code module:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim PTCll As PivotCell

On Error Resume Next
Set PTCll = Target.PivotCell
On Error GoTo 0

If Not PTCll Is Nothing Then
If PTCll.PivotCellType = xlPivotCellValue Then
Cancel = True
Target.ShowDetail = True
With ActiveSheet
FieldNo = Application.Match(PTCll.DataField.SourceName, .Rows(1), 0)
If Not IsError(FieldNo) Then
With .ListObjects(1)
.Range.AutoFilter Field:=FieldNo, Criteria1:="<>1" ', Operator:=xlAnd
.DataBodyRange.EntireRow.Delete
.Range.AutoFilter Field:=FieldNo
End With
.Range("A1").Select
End If
End With
End If
End If
End Sub
Note that it effectively disables normal drilling down - it will always filter results according to the column header of the double-clicked-on cell. Double-clicking on a cell in the Count of NUM column usually leaves you with an empty table.
This can be tweaked to act only on a number of headers if you want:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim PTCll As PivotCell

On Error Resume Next
Set PTCll = Target.PivotCell
On Error GoTo 0

If Not PTCll Is Nothing Then
If PTCll.PivotCellType = xlPivotCellValue Then
Cancel = True
Target.ShowDetail = True
If Not IsError(Application.Match(PTCll.DataField.SourceName, Array("Metric1", "Metric2", "Metric4", "Metric5", "Metric6", "Metric7"), 0)) Then
With ActiveSheet
FieldNo = Application.Match(PTCll.DataField.SourceName, .Rows(1), 0)
If Not IsError(FieldNo) Then
With .ListObjects(1)
.Range.AutoFilter Field:=FieldNo, Criteria1:="<>1"
.DataBodyRange.EntireRow.Delete
.Range.AutoFilter Field:=FieldNo
End With
.Range("A1").Select
End If
End With
End If
End If
End If
End Sub

Another thing to note in your pivot table, especially for Metric7, but it applies to all metrics which can only have values of 0,1 or blank. It would be more understandable if the Value Field settings for them is SUM rather than COUNT, since the count includes the zeroes as well as the 1s. Then your resultant drill down table will have the number of rows you expect.


I'll keep looking for my original solution.

[Bacchus]

This is grand, thanks!

I'm in and out of the office for the rest of the week so I won't have time to apply this to my file and play around with it until late week/early next week.

Really appreciate the effort!

[Bacchus]

I've finally had a chance to work through the code you provided. Everything works perfectly!

I made some adjustments to my output to use the more generic code which I think will be safer in the long run. This way I won't forget to update the array of fields being filtered on drill down.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim PTCll As PivotCell

On Error Resume Next
Set PTCll = Target.PivotCell
On Error GoTo 0

If Not PTCll Is Nothing Then
If PTCll.PivotCellType = xlPivotCellValue Then
Cancel = True
Target.ShowDetail = True
With ActiveSheet
FieldNo = Application.Match(PTCll.DataField.SourceName, .Rows(1), 0)
If Not IsError(FieldNo) Then
With .ListObjects(1)
.Range.AutoFilter Field:=FieldNo, Criteria1:="<>1" ', Operator:=xlAnd
.DataBodyRange.EntireRow.Delete
.Range.AutoFilter Field:=FieldNo
End With
.Range("A1").Select
End If
End With
End If
End If
End Sub




One think I need to think through are a few fields that are averages. Maybe updating the filter criteria to "<0" or something... Anyway, I greatly appreciate the effort and help!

+1

[Bacchus]

Hey p45cal,

I've been trying to update the filter criteria to contain 2 filters, something like this:


With .ListObjects(1)
.Range.AutoFilter Field:=FieldNo, Criteria1:="<>" & "", Operator:=xlAnd, Criteria2:=">=1"
.DataBodyRange.EntireRow.Delete
.Range.AutoFilter Field:=FieldNo
End With

It seems to only apply the first criteria. Any thoughts on why?

Im thinking if I change the filter criteria to be anything greater than or equal to 1 AND omit blank/null fields, I can use the logic for all fields, including those with averages.

Thoughts?

[p45cal]

Two approaches:
1. Disable the existing code (change the name or comment it out). Do a normal drill-down. On the new sheet, apply filters manually until you see everything you want to DELETE. Remember them. Remove all filters. Switch the macro recorder on. Apply those remembered filters, stop recording. Post the resultant code here (or replace the criteria in your original code with the new criteria).
2. Tell me the sort of values you want to keep, and the sort you want to remove.

[Bacchus]

After more thought and some output adjustments I'm getting the results I want when I filter out just blank cells.

I can update flag fields from 1/0 to 1/null. This will also give me the drill detail I want when I'm looking at fields where the field settings are averages. However, when I try to update the filter criteria in your code to just blanks I'm not getting the drill detail I want; it's showing me just blank cells for that field. It feels like I'm missing something obvious.


EDIT:
After toying with the conditions more I think I've got it.

This seems to work for all fields.

.Range.AutoFilter Field:=FieldNo, Criteria1:="="

[p45cal]

However, when I try to update the filter criteria in your code to just blanks I'm not getting the drill detail I want; it's showing me just blank cells for that field. It feels like I'm missing something obvious.It's impossible for me to guess what you want!
Follow the instructions in approach no.1 in msg#12 above, and come back with what you recorded.

[Bacchus]

Sorry if I was being confusing. I only want to see values for cells that are not missing.

When I follow your instructions and record the filtering I want I get the following, which is what I edited my above post with earlier:


ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=2, Criteria1:="="


I'm gonna consider this solved. Thank you for all the help on this! I greatly appreciate it!


Cheers!
:beerchug: