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)
 USE
 
 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
 oTargetSheet.range("c3").select
 
 **** calcuteditems
 
 oPivotTable.PivotFields("yearname").CalculatedItems.Add("avg4", "='2011'/'2010'*100")
 
 ****  added this 2 lines

oPivotTable.PivotFields("yearname").CalculatedItems.Add("Total", "='2011'+'2010'")
oPivotTable.RowGrand = .F.

above is the sample data of my Pivot Table

grand total of "AVG4" column should be 64.70 (55/85*100)
but it is showing 129.1666

warm regards,
mk.