Getting Pivot Table Value Field Characteristics

Getting Pivot Table Value Field Characteristics

This post is about a bit of code that answered somebody’s Stack Overflow question, was fun to write, and taught me a few more things about the pivot table object model, which is my favorite object model. So neat, so tidy, so logical – just like pivot tables themselves.

What, you ask, do I mean by Value Field Characteristics? I mean the page, row and column fields and items that the value field is summing, counting or otherwise valuing. So in the picture below the selected value field has three row items: one each for Continent, Country and State.

value field

The Code

Here’s the code to print that same information to the Immediate window. Like the Excel tooltip in the picture it also lists the Value field name, e.g., Sum of Population, and its source field, e.g., Population. (This can be handy when you’ve modified the value field name to something like else, like “Residents.”):

Sub GetValueFieldStuff()
Dim pvtCell As Excel.PivotCell
Dim pvtTable As Excel.PivotTable
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Dim pvtParentField As Excel.PivotField
Dim i As Long

On Error Resume Next
Set pvtCell = ActiveCell.PivotCell
If Err.Number <> 0 Then
    MsgBox "The cursor needs to be in a pivot table"
    Exit Sub
End If
On Error GoTo 0

If pvtCell.PivotCellType <> xlPivotCellValue Then
    MsgBox "The cursor needs to be in a Value field cell"
    Exit Sub
End If

Set pvtTable = pvtCell.PivotTable
For Each pvtField In pvtTable.PageFields
    i = 0
    For Each pvtItem In pvtField.PivotItems
        If pvtItem.Visible Then
            i = i + 1
            Debug.Print "PageField " & pvtField.Name & " - Pivot Item " & i & " is " & pvtItem.Name
        End If
    Next pvtItem
Next pvtField

Debug.Print "Value Field Name is " & pvtCell.PivotField.Name
Debug.Print "Value Field Source is " & pvtCell.PivotField.SourceName

For i = 1 To pvtCell.RowItems.Count
    Set pvtParentField = pvtCell.RowItems(i).Parent
    Debug.Print "Row Item " & i & " is " & pvtCell.RowItems(i).Name & ". It's parent Row Field is: " & pvtParentField.Name
Next i

For i = 1 To pvtCell.ColumnItems.Count
    Set pvtParentField = pvtCell.ColumnItems(i).Parent
    Debug.Print "Column Item " & i & " is " & pvtCell.ColumnItems(i).Name; ". It's parent Column Field is: " & pvtParentField.Name
Next i
End Sub

Before answering this question I didn’t know about the PivotCell.RowItems and PivotCell.ColumnItems properties. They’re pretty cool.

The person who posted this on Stack Overflow was looking to create an “actual” drilldown, which I think meant using the output to write a SQL query. You could modify the output of this routine to do so, as in this pseudocode:

SELECT * FROM qryContinentCountryState
WHERE pvtParentItem.Name = pvtCell.RowItems(i).Name
AND …

Alrighty then. Thanks for dropping by!

2 thoughts on “Getting Pivot Table Value Field Characteristics

  1. Hello there Doug,

    I have a related follow up question to this post and one that you made back in 2012 with the same data set.

    The question I have is about the code you wrote to hide Single-Item pivot totals. I’ve used this code on a sheet and it works, but with one unintended consequence that I hope you might be able to help with.

    Suppose that Algeria or Benin or any of the single-line Countries had a State/Province listed. When the code is executed that single State/Province is suppressed. I’m not savvy enough to figure out which part of your code is causing this, or if this is the result of the comment you made about collapsing February in one place but not in another.

    My specific issue from my job is this: I work for an apparel manufacturer, we have “Styles”, example “1001” (a t shirt for example) and items like “1001-BLK S, 1001-BLK M, 1001-BLK L” (items being the style/color/size combination), etc. Some Styles only have a single Item, e.g. 1001-BLK OS (one size). When I use the code on a pivot table it works exactly as it should on the “Style” field, but hides the value in the single Item field which is not good. I can replicate this with your sample file – If I fill in all of the blank provinces on the data tab, create a new pivot, and run the code to remove the redundant totals, the Provinces on the single-entry Countries become hidden.

    Do you have any ideas on how to resolve this?

    Any help would be greatly appreciated, thank you. And if you would prefer that I post to StackOverflow – no problem, let me know.

    VvM

    • Hi Varius,

      You’ve hit on (one of) the weak spots of this method. I mention this limitation in that post, along with another one. It didn’t occur to me until I wrote the post because the list I used it for only had blanks in the single-item rows.

      So, sorry, I don’t have anything to offer. If you do find out a way please let me know.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>