Can the Pivot Cache "Drill Down" feature be used in other cells?

[SakiSam]

Good Day,
For my first post I'd like to know if it is possible to use the "drill down" feature of a pivot table within a cell out side of that pivot table.


I am using Excel 2010, on Windows 7. The end user(s) will have these (at a minimum) or newer.


I have a report that tracks inventory a number of different ways using pivot tables. Because of the design requirements for the report, I cannot use the pivot tables directly in the report. As a result I use the "GETPIVOTDATA" function a lot.


The end user(s) need the ability to drill down to the details from the formatted report and not the pivot tables. Currently this is done by capturing the parameters associated with the cell and looping through the source data to find the fields to filter on. For this function, I am maintaining the source data tables/worksheets within the workbook. This procedure works really well but having the source data stored on a worksheet and in the pivot cache is a redundancy. Is there a way to recall the contents of the pivot cache and run the filter function or "drill down" on it instead?


Without any luck, I've searched for ways to recall the PivotCache without the use of any add-ins.


Thank you for assistance

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

The code below should work for simple GETPIVOTDATA formulas that have the syntax:
=GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

...with none of those arguments having nested commas.

Paste this code into the Sheet Code Module of the sheet that you want to take this action when the user double-clicks a cell with a simple GETPIVOTDATA formula. To get to the Sheet Code Module right-click the sheet's tab > View Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'--if dbl-clicked cell contains simple getPivotData formula, this sub
'  attempts to execute showdetails method to display drill down data in pivot cache
'  if sub fails to find valid pivotcell, normal dbl-click behavior occurs.
'--works by parsing formula by commas then passing arguments to getPivotData method
'  current version of code will not handle arguments that have nested commmas.

 Dim lNdx As Long
 Dim pvt As PivotTable
 Dim rDataCell As Range
 Dim sFormula As String, sWksArgs As String
 Dim sArgs() As String
 Dim vArgs As Variant
  
 '--validate double-clicked range is a single cell with a simple getpivotdata formula
 If Target.CountLarge > 1 Then GoTo ExitProc
 sFormula = Target.Formula
 If LCase$(Left(sFormula, 14)) <> "=getpivotdata(" Then GoTo ExitProc
 
 '--get simple worksheet formula arguments
 sWksArgs = Mid(sFormula, 15, Len(sFormula) - 15)

 '--parse arguments into array
 vArgs = Split(sWksArgs, ",")
 
 '--test that 2nd argument is valid pivotcell reference
 On Error Resume Next
 Set pvt = Me.Evaluate(vArgs(1)).PivotTable
 On Error GoTo 0
 If pvt Is Nothing Then GoTo ExitProc
 
 '--limit this sub to handle 14 field-item pairs
 '  to make compatible with xl2007 getpivotdata method
 If UBound(vArgs) > 28 Then GoTo ExitProc
 If UBound(vArgs) = 1 Then
   '--grand total with no field-item pairs
    On Error Resume Next
   Set rDataCell = pvt.GetPivotData(CStr(Evaluate(vArgs(0))))
 Else
   '--evaluate arguments and cast as strings into array
   ReDim sArgs(0 To 28)
   '--0 index is datafield
   sArgs(0) = Me.Evaluate(vArgs(0))
   '--remaining indicies in vArgs are pairs of fields-items
   For lNdx = 1 To UBound(vArgs) - 1
     sArgs(lNdx) = Me.Evaluate(vArgs(lNdx + 1))
   Next lNdx
   
   '--fill remaining indicies in sArgs with 1st fields-items pair
   For lNdx = UBound(vArgs) To 28
     If lNdx Mod 2 Then
        '--odd indicies get default field name
        sArgs(lNdx) = sArgs(1)
     Else
        '--even indicies get default item name
        sArgs(lNdx) = sArgs(2)
     End If
   Next lNdx
   On Error Resume Next
   Set rDataCell = pvt.GetPivotData(sArgs(0), sArgs(1), sArgs(2), sArgs(3), sArgs(4), _
     sArgs(5), sArgs(6), sArgs(7), sArgs(8), sArgs(9), sArgs(10), sArgs(11), sArgs(12), _
     sArgs(13), sArgs(14), sArgs(15), sArgs(16), sArgs(17), sArgs(18), sArgs(19), sArgs(20), _
     sArgs(21), sArgs(22), sArgs(23), sArgs(24), sArgs(25), sArgs(26), sArgs(27), sArgs(28))
   On Error GoTo 0
 End If
 
 If Not rDataCell Is Nothing Then
   '--cancel default dbl-click behavior
   Cancel = True
   rDataCell.ShowDetail = True
 End If

ExitProc:
   
End Sub

This code handles 14 field-item pairs which is the limit for the GetPivotData method in xl2007 and should be enough pairs for most users of any version. For xl2010 and later, you could modify the code to handle up to 126 field-item pairs.

I tried a number of ways to try to pass a variable number of arguments to GetPivotData such as ....

Set rDataCell = pvt.GetPivotData(sArgs)

None of those worked and I'd be interested if anyone knows of a way to do that.
Lacking a cleaner way to do that, this code makes a call to pvt.GetPivotData with a fixed number of arguments (29). The excess arguments are populated with the first field-item pair and this returns the same result as if those arguments were omitted.

 

[SakiSam]

Thank you, this works great.

 

[SakiSam]

Please advise if there is anyway to amend the provide code to handle a range of data? Such as in the case of the following?
IE. the array calculation:

{=SUM(IFERROR(GETPIVOTDATA("Count of ID",MasterPivoted!$BA$3,"StatusCodes",C$3:N$3,"Year",TRIM($B19)),0))}

I've already adjusted it so that the vArgs() = "Count of ID","MasterPivotedNo3","StatusCodes",C$3:N$3,"Year","2014". and is within the 28 argument limit. But I am getting a run-time error '13: "Type mismatch". I know this is due to the range set for the statuscodes.

As usual I appreciate any feedback.

FYI: I'm using the formula as a subtotal of the individual StatusCodes so I can use this "drill" code.

 

[Jerry Sullivan]

Hi Sam,

Because the code I suggested replicates the drill down action that occurs when a single cell is double-clicked, I don't think it's feasible to modify it to work with multiple cell references in an array formula.

It's possible to do separate drill downs using the value of each cell in any multi-cell range references. The drilldown tables could then be consolidated into one table.
While that's possible, it's quite a bit more complicated- especially handling the possibility of multiple formula arguments each referencing multi-cell ranges.

Given that, I'd probably recommend a different completely approach like:
1. Filtering the data source (as you were previously doing)
2. Doing a drill down of the entire PivotCache then filtering that output.
3. Making a temporary copy of the PivotTable > filtering the Pivot to using the parameters of the GetPivotData formula then doing a drill down on all the visible data.

The last option is kind of interesting. I'll give that a try sometime this weekend.

 

[SakiSam]

Jerry,
I got a solution and a new questions.

Solution:
First the "Year" range was given a grouping in the original table. ie, Future, past, present, and Dead. This category was added to the pivot table.

Next I modified the following code.

If UCase$(Left(sFormula, 22)) = "=IFERROR(GETPIVOTDATA(" Then GoTo Normal 
If UCase$(Left(sFormula, 26)) = "=SUM(IFERROR(GETPIVOTDATA(" Then GoTo LTotal
If UCase$(Left(sFormula, 10)) = "=SUBTOTAL(" Then GoTo Subtotals
GoTo ExitProc

'--get simple worksheet formula arguments
Normal:
  sWksArgs = Mid(sFormula, 23, Len(sFormula) - (23 + 3))  'the +3 is for the iferror arguments.  
  GoTo continue
LTotal:  'Line Totals
  sWksArgs = Mid(sFormula, 27, Len(sFormula) - (27 + 3 + 1)) 'the +3 is for the iferror, +1 for Sum arguments.  
  GoTo continue
Subtotals:
  CatSubTotals 'New Sub CatSubTotals() to build sWksArgs.
  GoTo continue
 
 
 '--parse arguments into array
continue:
 vArgs = Split(sWksArgs, ",")

Then I added a separate Sub to the module that assembled the sWksArgs for the subtotals.
resulting in the following string that is passed back to original Sub.

sWksArgs = CountOfXs & ",""Calc""," & TagVal & ",""Category""," & MCat & BNind

CountOfX's: uses the first two arguments of the formula in the previous row. This assumes that the subtotals are below the groupings.

CountOfXs = Mid(Cells(rng0.Offset(-1, 0).Row, 3).Formula, Application.Find("""Count of ", Cells(rng0.Offset(-1, 0).Row, 3).Formula), (Application.Find("""Calc", Cells(rng0.Offset(-1, 0).Row, 3).Formula) - Application.Find("""Count of ", Cells(rng0.Offset(-1, 0).Row, 3).Formula)) - 1)

The rest are the remaining arguments.

Finally I created a Private Dim at the top of the module, allowing the calculated variable in the new sub to be returned to the original Sub:

Private sWksArgs As String

I hope I've explained this well enough for others that have a similar issue. I couldn't figure out how to include images.

Question:
The source data table for the pivot tables include some items that are filtered out with a slicer. Is there a method to exclude these items from the resulting detail? If not I suppose I will have to remove them from the source data table.

 

[Jerry Sullivan]

I'm glad to hear that you found a workaround that gives you the information you want.

I'm not following how your solution applies to your example in post #4.

{=SUM(IFERROR(GETPIVOTDATA("Count of ID",MasterPivoted!$BA$3,"StatusCodes",C$3:N$3,"Year",TRIM($B19)),0))}

I see how you find the arguments by adjusting for the additional characters in the formula. The challenge with the formula above is that it is an array formula so the drill down needs to be the combination of the drill down ranges returned from showing details when StatusCodes=C3, D3, E3...N3. Am I correctly understanding that your solution still just gets the drilldown of one cell? Did you solve the array formula problem through grouping the records associated with C$3:N$3 into one cell of the Pivot?

Regarding your question on the Slicer, to what area of the PivotTable is the Slicer applied (Report Filter, Row Labels, Column Labels)?
Could you provide an example- typically items filtered out by a Slicer don't have their records appear in the drill down details.

 

[barrysnashall]

Fantastic piece of code that has just helped me, just a quick addition i am getting #Ref where there is no result and i know i can use iferror but then the drill down doesnt work, is there anything i can add to the VBA code that will put 0 if there is no answer.

many thanks

 

[Chengo]

Hello Jerry,

Sorry for resurrecting this old thread. My apologies if I should have created new one instead.

I like you solution posted in the first reply however wonder if it is possible to amend it to rename these automatically created Drilldown sheets. My ultimate goal is to be able to delete these sheets with the Workbook_BeforeClose event so that the original Workbook stays clutter free.

Thank you in advance!

 

[Jerry Sullivan]

Hi Chengo,

You could add code to rename the sheet at the location shown below...

 If Not rDataCell Is Nothing Then
   '--cancel default dbl-click behavior
   Cancel = True
   rDataCell.ShowDetail = True
[COLOR="#0000CD"][B]   ActiveSheet.Name="MyTempSheet"[/B][/COLOR]
 End If

You'll probably need to add code to ensure you don't attempt to rename a sheet to a sheet name that already exists in your workbook.