** mztolo 2006 sample pivot table, copy paste dari e-book
RELEASE ALL LIKE o*
PUBLIC oExcel, oBook
OPEN DATABASE (_SAMPLES + "\TasTrade\Data\TasTrade")

SELECT Customer.Company_Name, ;
Customer.Country, ;
CMONTH(Orders.Order_Date) AS Order_Month, ;
YEAR(Orders.Order_Date) AS Order_Year, ;
Category.Category_Name,;
Products.Product_Name, ;
Order_Line_Items.Quantity, ;
Order_Line_Items.Unit_Price * Order_Line_Items.Quantity ;
AS Total_Price;
FROM Orders, Order_Line_Items, Customer, Products, Category ;
WHERE Order_Line_Items.Order_ID = Orders.Order_ID ;
AND Orders.Customer_ID = Customer.Customer_ID ;
AND Order_Line_Items.Product_ID = Products.Product_ID ;
AND Products.Category_ID = Category.Category_ID ;
INTO CURSOR Pivot
LastLine = ALLTRIM(STR(_TALLY + 1 ))
COPY TO (CURDIR() + "Pivot") TYPE XL5
oBook = GETOBJECT(CURDIR() + "Pivot.XLS")
WITH oBook
oExcel = .Application
.Application.Visible = .T.
.Windows[1].Activate()
.Sheets[1].Range("A1:H" + LastLine).Columns.AutoFit()
ENDWITH

#DEFINE xlDatabase 1
oSourceData = oBook.Sheets[1].Range("A1:H2822")
oDestination = oBook.Sheets[1].Range("J1")
oPivotTable = oBook.Sheets[1].PivotTableWizard(xlDatabase, oSourceData, ;
oDestination, "SalesAnalysis", .T., .T.)

oPivotTable.AddFields("country", "category_name")
#DEFINE xlDataField 4
oPivotTable.PivotFields["Quantity"].Orientation = xlDataField

WITH oPivotTable.PivotFields("category_name")
.PivotItems("Condiments").Visible = .F.
.PivotItems("Dairy Products").Visible = .F.
.PivotItems("Grains/Cereals").Visible = .F.
.PivotItems("Meat/Poultry").Visible = .F.
.PivotItems("Produce").Visible = .F.
.PivotItems("Seafood").Visible = .F.
ENDWITH
WITH oPivotTable.PivotFields("country")
.PivotItems("Argentina").Visible = .F.
.PivotItems("Austria").Visible = .F.
.PivotItems("Belgium").Visible = .F.
.PivotItems("Brazil").Visible = .F.
.PivotItems("Denmark").Visible = .F.
.PivotItems("Finland").Visible = .F.
.PivotItems("France").Visible = .F.
.PivotItems("Germany").Visible = .F.
.PivotItems("Ireland").Visible = .F.
.PivotItems("Italy").Visible = .F.
.PivotItems("Norway").Visible = .F.
.PivotItems("Poland").Visible = .F.
.PivotItems("Portugal").Visible = .F.
.PivotItems("Spain").Visible = .F.
.PivotItems("Sweden").Visible = .F.
.PivotItems("Switzerland").Visible = .F.
.PivotItems("UK").Visible = .F.
.PivotItems("Venezuela").Visible = .F.
ENDWITH