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.

   Curt

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.

   PositionSheetPivotTableFieldTypeValue1Value2
   FilterCheckSheet4CallsPivotYear232007

   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.

   Curt

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. There is a language called MDX (Multidimensional Expressions)
   Wikipedia on MDX
   It’s a property of the PivotTable class, but it errors if accessed for non OLAP types.

   A quick Internet search reveals some efforts to translate between Pivottables and MDX.

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

   SELECT (a, b, c) ON COLUMNS, NON EMPTY (d) ON ROWS
   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.

7. A few links:
   http://www.mydatabasesupport.com/forums/olap/162003-how-use-mdx-query-excel.html
   http://sqlblog.com/blogs/marco_russo/archive/2007/01/18/display-the-mdx-query-of-an-excel-2007-pivottable.aspx
   http://www.manageability.org/blog/stuff/open-source-java-business-intelligence

8. You can do:

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

9. 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

10. 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??

11. 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

12. 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
    Next
    .PivotItems(“(blank)”).Visible = False
    End With

    End Function

13. It depends on the Pivottable.version

    http://blogs.msdn.com/excel/archive/2006/08/30/730796.aspx
    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.

    http://www.experts-exchange.com/Microsoft/Applications/Q_24185001.html
    You can test for a Page field’s PivotItems with a xlPivotTableVersion12 Pivot Table but not in a xlPivotTableVersion10 Pivot Table.

    IOW
    .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
    Next

    You can set
    …pagefields(x).currentpage=”(All)”
    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.