PivotTable Markup Language

How do you communicate a pivot table to someone? I have a huge table of payroll transactions and I created a pivot table for my worker’s compensation premium audit. Someone else who has Quickbooks could use this pivot table for their own purposes, but I don’t know how to tell them how to build it. I have two basic problems: There are a lot of seemingly irrelevant options when creating a pivot table and when I create a pivot table there’s a lot of trial and error.

If I want to tell someone to create a table in an Access database, for example, I could send them a CREATE TABLE sql statement, like this one from w3schools:

LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)

It doesn’t tell them the steps to create the table in the Access UI, but it gives them all the information they need to create the table (not to mention they could just execute the sql).

Do we need SPL (Structured PivotTable Language)? Does anyone else have trouble explaining the properties of a pivot table or is it just me?

Here’s what I get when I record a macro to create the aforementioned PT:

Sub Macro1()

‘ Macro1 Macro
‘ Macro recorded 5/21/2008 by Dick Kusleika

   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        “Data!R1C1:R16497C81”).CreatePivotTable TableDestination:=“”, TableName:= _
        “PivotTable1”, DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Source Name”)
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“State Worked In”)
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“State Worked In”). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Payroll Item”)
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Payroll Item”)
        .PivotItems(“Federal Withholding”).Visible = False
        .PivotItems(“Social Security Company”).Visible = False
        .PivotItems(“Social Security Employee”).Visible = False
        .PivotItems(“Medicare Company”).Visible = False
        .PivotItems(“Medicare Employee”).Visible = False
        .PivotItems(“Admin – Flash – Exclusive to”).Visible = False
        .PivotItems(“Admin Fee”).Visible = False
        .PivotItems(“Administrative Salary”).Visible = False
        .PivotItems(“AZ – Job Training Tax”).Visible = False
        .PivotItems(“AZ – Unemployment Company”).Visible = False
        .PivotItems(“AZ – Withholding”).Visible = False
        .PivotItems(“Bonus”).Visible = False
        .PivotItems(“CO – Unemployment Company”).Visible = False
        .PivotItems(“CO – Withholding”).Visible = False
        .PivotItems(“Commission_AIM_Ops”).Visible = False
        .PivotItems(“Commission_AIM_SE”).Visible = False
        .PivotItems(“Commission_PFC_SE”).Visible = False
        .PivotItems(“Education”).Visible = False
        .PivotItems(“Eng – Application”).Visible = False
        .PivotItems(“Eng – Hardware Design”).Visible = False
        .PivotItems(“Eng – Software Design”).Visible = False
        .PivotItems(“ER – Dental, Life, ST & LT Ins.”).Visible = False
        .PivotItems(“ER Share of Health Insurance”).Visible = False
    End With
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Payroll Item”)
        .PivotItems(“Flex Plan”).Visible = False
        .PivotItems(“Floating_Holiday_Hourly”).Visible = False
        .PivotItems(“Floating_Holiday_Salaried”).Visible = False
        .PivotItems(“GAP Plan”).Visible = False
        .PivotItems(“Holiday Salary”).Visible = False
        .PivotItems(“Hourly – Admin”).Visible = False
        .PivotItems(“Hourly – Admin – Overtime”).Visible = False
        .PivotItems(“Hourly – Application Eng OT”).Visible = False
        .PivotItems(“Hourly – ApplicationEngineering”).Visible = False
        .PivotItems(“Hourly – Eng Hardware Design”).Visible = False
        .PivotItems(“Hourly – Eng Software Design”).Visible = False
        .PivotItems(“Hourly – Holiday”).Visible = False
        .PivotItems(“Hourly – Manfactur’g Inspection”).Visible = False
        .PivotItems(“Hourly – Manfacturing Tech”).Visible = False
        .PivotItems(“Hourly – Manfacturing Tech – OT”).Visible = False
        .PivotItems(“Insurance Costs”).Visible = False
        .PivotItems(“MA – Unemployment Company”).Visible = False
        .PivotItems(“MA – Withholding”).Visible = False
        .PivotItems(“MA – Workforce Training Fund”).Visible = False
        .PivotItems(“Marketing”).Visible = False
        .PivotItems(“Misc Deduction”).Visible = False
        .PivotItems(“NE – State Unemp. Ins. Tax”).Visible = False
    End With
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Payroll Item”)
        .PivotItems(“NE – Unemployment Company”).Visible = False
        .PivotItems(“NE – Withholding”).Visible = False
        .PivotItems(“NH – Admin. Contribution”).Visible = False
        .PivotItems(“NH – Unemployment Company”).Visible = False
        .PivotItems(“OH – School District”).Visible = False
        .PivotItems(“OH – Unemployment Company”).Visible = False
        .PivotItems(“OH – Withholding”).Visible = False
        .PivotItems(“Production”).Visible = False
        .PivotItems(“Quality Assurance”).Visible = False
        .PivotItems(“Sales Commission AIM_Ops_Mgr”).Visible = False
        .PivotItems(“Sales Commission AIM_SE”).Visible = False
        .PivotItems(“Sales Commission PFC_SE”).Visible = False
        .PivotItems(“Sales Salary”).Visible = False
        .PivotItems(“Severance”).Visible = False
        .PivotItems(“Sick Salary”).Visible = False
        .PivotItems(“SickHourly_Rate”).Visible = False
        .PivotItems(“Simple IRA – Employer Match”).Visible = False
        .PivotItems(“Simple Plan”).Visible = False
        .PivotItems(“Vacation Salary”).Visible = False
        .PivotItems(“VacationHourly Rate”).Visible = False
    End With
    ActiveSheet.PivotTables(“PivotTable1”).AddDataField ActiveSheet.PivotTables( _
        “PivotTable1”).PivotFields(“Income Subject To Tax”), _
        “Sum of Income Subject To Tax”, xlSum
    ActiveSheet.PivotTables(“PivotTable1”).RowGrand = False
End Sub

Complete but not concise. Maybe I just list out the relevant properties:

  Source: Data!R1C1:R16497C81
  RowField “Source Name”
    Position: 2
  RowField “State Worked In”
    Position: 1
    RowTotals: Off
  ColumnField “Payroll Item”
      Visible: False
    Item “Federal Unemployment”
      Visible: True
  DataField “Income Subject To Tax”
    Aggregator: Sum
  RowGrand: False

Can you create my exact pivot table with just that?

Posted in Uncategorized

13 thoughts on “PivotTable Markup Language

  1. You may be interested on the reasoning behind the R “reshape” package. Although intended for the R statistical language, it provides an interesting general framework for pivot tables. See the papers on http://had.co.nz/reshape/

  2. Hi Dick,

    Your summary contains enough information to re-create the PivotTable. The VBA property “Orientation” indicates whether a field’s values appear in the Column, Row, Data, or Report (filter) area. Row = 1, Column = 2, Report = 3, and Data = 0. I use these properties to record PivotTable configurations in my Excel Presentation Kit.


  3. I see now that you had the Orientation property covered in your write-up.

    I found that I had to record rule-based and selected-item Excel 2007 PivotTable filters differently. When you apply a selection filter in VBA, you should clear any existing filters and then set the PivotItem’s “Hidden” property to True. In this example, the cities of Elko and Harrisonburg don’t appear in the Center field.

    PositionWorksheetPTName Field Item
    FilterCheckSheet4 CallsPivotCenter Elko
    FilterCheckSheet4 CallsPivotCenter Harrisonburg

    For a rule-based PivotTable filter, you record the field (user entry is the easiest way to do it), criteria type, Value1, and (optional) Value2. Here’s what my recorder’s output looks like.


    You can then use VBA code to reconstruct the filters from the recorded position.

    If you like, I’d be happy to send you an updated copy of my Excel Presentation Kit, which works with Excel 2007.


  4. Some would say you’ve gone over to the dark side, Dick. Put in the correct combinations of < , > , and / > and you would have the XML statements required to (re)create the PT! {gdr}

  5. Rob good point
    MDX (basically SQL for OLAP) probably contains most of what you need:

    FROM Some cube or other data source say a range
    WHERE Some page field

    (where a, b etc are field names)

    MDX is in Analysis services and recent versions of Essbase, and lots of other tools so is maybe near enough a ‘standard’?
    If ADOMD worked against Excel ranges (does it??) that might give you what you need.

  6. You can do:

    ActiveSheet.PivotTables(“PivotTable1?).PivotFields(“Payroll Item”).ShowAllItems = False
    ActiveSheet.PivotTables(“PivotTable1?).PivotFields(“Payroll Item”).PivotItems(“Federal Unemployment”).Visible = True

  7. If the above doesn’t work, you can try this:

    set pvtTable = activesheet.pivottables(“PivotTable1?)

    for each pvtItem in pvtTable.pivotfields(“Payroll Item”).PivotItems
    If pvtItem.name “Federal Unemployment” then
    pvtItem.visible = false
    end if
    next pvtItem

  8. This totally didn’t work… this “Joice Fung” (as if that’s her real name) is wrong. Any smart people out there have a solution??

  9. Vladamar: There should be a “not equal” sign between pvtItem.name and “Federal”. The HTML parser assumes the less than sign is an html tag so it’s not shown.

    If pftItem.name <> “Federal Unemployment” Then

  10. I came by this thread when trying to figure out how to make VBA show all pivotitems EXCEPT blanks. This didn’t fix it for me, but gave me a clue. The way to do THAT, I finally figured out is

    Public Function Pivot_AllPivotItems_NoBlanks()

    With ActiveSheet.PivotTables(“PivotTable7?).PivotFields(“Device Type”)
    For x = 1 To .PivotItems.Count
    .PivotItems(x).Visible = True
    .PivotItems(“(blank)”).Visible = False
    End With

    End Function

  11. It depends on the Pivottable.version

    PivotTable Versioning
    In Excel 12, the PivotTable version is associated with Compatibility Mode.
    If your current workbook is in compatibility mode, new PivotTables created in that workbook
    will be version 10 PivotTables.

    You can test for a Page field’s PivotItems with a xlPivotTableVersion12 Pivot Table but not in a xlPivotTableVersion10 Pivot Table.

    .ShowAllItems = True
    does NOT work, even in Excel 2007, if the pivottable is in 2003 compatibility mode

    Neither does this.
    Select one item in a list from the UI and then run this
    For i = 1 To .PivotItems.Count
    .PivotItems(i).Visible = True
    Debug.Print i, .PivotItems(i).Name, .PivotItems(i).Visible

    You can set
    which works when English is the locale

    The only way I found to work is to hide the pagefield and restore it, which resets it to (All)
    I hope that works for (Toutes) or (Alles) etc

    .Orientation = xlHidden
    .Orientation = xlPageField

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.