CLOSE TABLES ALL

CREATE TABLE sampledata (party_name c(20), prod_name c(20), yearname N(4), sal_qty N(6), target_qty N(6))

INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT A',2010,45)
INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT B',2010,40)
INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT A',2011,30)
INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT B',2011,25)
INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT B',2011,25)

INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT A',2010,50)
INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT B',2010,45)

INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT A',2011,40)
INSERT INTO sampledata (party_name,prod_name,yearname,target_qty) VALUES ('PARTY A','PRODUCT B',2011,45)
USE

LOCAL oexcel AS Excel.APPLICATION
LOCAL oWorkbook AS Excel.WorkBook
LOCAL oPivotCache AS Excel.PivotCache
LOCAL oPivotTable AS Excel.PivotTable


DATAPATH=SYS(5)+CURDIR()
oexcel = CREATEOBJECT("excel.application")
oexcel.APPLICATION.VISIBLE = .T.
oWorkbook = oexcel.Workbooks.ADD()
oTargetSheet = oWorkbook.Sheets.ADD()
oTargetRange = oTargetSheet.RANGE("A2")
oPivotCache = oWorkbook.PivotCaches.ADD( 2 ) && external data
oPivotCache.CONNECTION = "OLEDB;Provider=vfpoledb.1;data source=" + DATAPATH
oPivotCache.Commandtext = "select * from sampledata"
oPivotTable = oPivotCache.CreatePivotTable( oTargetRange, "PivotTable" )
oPivotTable.PivotFields("party_name").ORIENTATION = 1 && row
oPivotTable.PivotFields("prod_name").ORIENTATION = 1 && row
oPivotTable.PivotFields("yearname").ORIENTATION = 2 && column
oPivotTable.PivotFields("sal_qty").ORIENTATION = 4 && data
oPivotTable.PivotFields("target_qty").ORIENTATION = 4 && data

* Calculate % change
oChangeField=oPivotTable.AddDataField(oPivotTable.PivotFields("sal_qty"), "year_avg")
oChangeField.Calculation= 3  && xlPercentOf
oChangeField.BaseField="yearname"
oChangeField.BaseItem="(previous)"

oPivotTable.CalculatedFields.ADD("avg_diff", "=sal_qty/target_qty*100")
oPivotTable.PivotFields("avg_diff").ORIENTATION = 4 && data

* Calculate % change
oChangeField=oPivotTable.AddDataField(oPivotTable.PivotFields("avg_diff"), "avg6")
oChangeField.Calculation= 3  && xlPercentOf
oChangeField.BaseField="yearname"
oChangeField.BaseItem="(previous)"