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:
CREATE TABLE Person
(
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:
'
' 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
Range("A4").Select
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
Range("C9").Select
ActiveSheet.PivotTables("PivotTable1").RowGrand = False
End Sub
Complete but not concise. Maybe I just list out the relevant properties:
PivotTable
Source: Data!R1C1:R16497C81
RowField "Source Name"
Position: 2
RowField "State Worked In"
Position: 1
RowTotals: Off
ColumnField "Payroll Item"
AllItems
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?
Enrique:
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/
21 May 2008, 10:44 amCurt Frye:
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
21 May 2008, 1:34 pmCurt Frye:
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.
Position Worksheet PTName Field Item
FilterCheck Sheet4 CallsPivot Center Elko
FilterCheck Sheet4 CallsPivot Center 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.
Position Sheet PivotTable Field Type Value1 Value2
FilterCheck Sheet4 CallsPivot Year 23 2007
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
21 May 2008, 1:54 pmTushar Mehta:
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}
21 May 2008, 6:50 pmRob van Gelder:
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.
22 May 2008, 1:06 amSimon Murphy:
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'?
22 May 2008, 4:20 amIf ADOMD worked against Excel ranges (does it??) that might give you what you need.
Ola:
A few links:
4 June 2008, 4:17 pmhttp://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
Joice fung:
You can do:
ActiveSheet.PivotTables("PivotTable1").PivotFields("Payroll Item").ShowAllItems = False
9 June 2008, 11:26 amActiveSheet.PivotTables("PivotTable1").PivotFields("Payroll Item").PivotItems("Federal Unemployment").Visible = True
Joice fung:
If the above doesn't work, you can try this:
set pvtTable = activesheet.pivottables("PivotTable1")
for each pvtItem in pvtTable.pivotfields("Payroll Item").PivotItems
9 June 2008, 11:36 amIf pvtItem.name "Federal Unemployment" then
pvtItem.visible = false
end if
next pvtItem