thank you,

CLOSE TABLES all
 
 CREATE TABLE sampledata (party_name c(20), prod_name c(20), yearname n(4), sal_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 A',2012,80)
 INSERT INTO sampledata (party_name,prod_name,yearname,sal_qty) VALUES ('PARTY A','PRODUCT B',2012,15)
 USE
 
*#include excel.h

 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
 
 
 * Calculate % change
 oChangeField=oPivotTable.AddDataField(oPivotTable.PivotFields("sal_qty"), "avg4")
 oChangeField.Calculation= 3  && xlPercentOf
 oChangeField.BaseField="yearname"
 oChangeField.BaseItem="(previous)"
 
*oPivotTable.Format(xlTable4)

first column of "avg4" showing 100% and last column blank "Total avg4"

is this possible to hide this 2 columns

warm regards,
mk.