Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: mk sharma
  Where is mk sharma?
 mumbai
 India
 mk sharma
 Tags
Subject: pivot for target and actual
Thread ID: 310895 Message ID: 310895 # Views: 47 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Microsoft Office Automation
Date: Monday, June 27, 2011 4:18:07 AM         
   


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)"


Showing Wrong Grand Total for "Avg_diff"


warm regards,
mk.

ENTIRE THREAD

pivot for target and actual Posted by mk sharma @ 6/27/2011 4:18:07 AM
RE: pivot for target and actual Posted by Cetin Basoz @ 6/27/2011 10:02:36 AM
RE: pivot for target and actual Posted by sri kishan @ 6/27/2011 2:23:32 PM