multiple pivot tables from 1 pivot cache
 

I'm building a pivot chart that plots performance metrics over time
(SV, CV, cpi, spi, etc).

I want 2 charts: cpi vs spi and cv vs sv.

I've created a pivot cache and the first table for cv vs sv. I
thought I'd just add another sheet and add a table for cpi/spi. When
I do this the changes to 2nd pivot table flow back to 1st. Now both
tables have cv, sv, cpi, and spi.
Here's the code:

*--make pivot cache
ptCache = loToGoWkbk.PivotCaches.Add(xlDatabase,
loDataSheet.Range("A1").CurrentRegion.Address)

*--add sheet for cv sv table
loCvSvSheet = loToGoWkbk.Worksheets.Add
loCvSvSheet.Name = "CV vs SV"

*--create pivot table
loCvSvT =
ptCache.CreatePivotTable(loCvSvSheet.Range("A1")," PivotTable1")

WITH loCvSvT
*--add fields
.PivotFields("sub1").Orientation = xlPageField
.PivotFields("costset").Orientation = xlColumnField
.PivotFields("PERIOD").Orientation = xlRowField
.PivotFields("value").Orientation = xlDataField
.PivotFields("costset").CalculatedItems.Add("SV", "=BCWP-BCWS", .T.)
.PivotFields("costset").CalculatedItems.Add("CV", "=BCWP-ACWP", .T.)
WITH .PivotFields("Sum of value")
.Calculation = xlRunningTotal
.BaseField = "PERIOD"
ENDWITH

*--hide all columns but cv, sv
WITH .PivotFields("costset")
.PivotItems("ACWP").Visible = .F.
.PivotItems("BCWP").Visible = .F.
.PivotItems("BCWS").Visible = .F.
.PivotItems("EAC").Visible = .F.
.PivotItems("LRE").Visible = .F.
ENDWITH

ENDWITH

*--create the chart
loCVSVChart = loCvSvSheet.ChartObjects.Add(1,1,700,550).Chart
WITH loCVSVChart
.SetSourceData(loCvSvSheet.Range("C6"))
.HasDataTable = .T.
.ChartType = xlLine
.DataTable.ShowLegendKey = .T.
.HasTitle = .T.
.ChartTitle.Characters.Text = RTRIM( THISFORM.txtProgDesc.Value )
+ CRLF + "CV/SV Trend Data"
ENDWITH


*--add sheet for cpi spi table
loCpiSpiSheet = loToGoWkbk.Worksheets.Add
loCpiSpiSheet.Name = "CPI vs SPI"

*--create pivot table
loCpiSpiT =
ptCache.CreatePivotTable(loCpiSpiSheet.Range("A1") ,"PivotTable2")

WITH loCpiSpiT
*--add fields
.PivotFields("sub1").Orientation = xlPageField
.PivotFields("costset").Orientation = xlColumnField
.PivotFields("PERIOD").Orientation = xlRowField
.PivotFields("value").Orientation = xlDataField
.PivotFields("costset").CalculatedItems.Add("SPI", "=BCWP/BCWS", .T.)
.PivotFields("costset").CalculatedItems.Add("CPI", "=BCWP/ACWP", .T.)
WITH .PivotFields("Sum of value")
.Calculation = xlRunningTotal
.BaseField = "PERIOD"
ENDWITH

*--hide all columns but cpi, spi
WITH .PivotFields("costset")
.PivotItems("ACWP").Visible = .F.
.PivotItems("BCWP").Visible = .F.
.PivotItems("BCWS").Visible = .F.
.PivotItems("EAC").Visible = .F.
.PivotItems("LRE").Visible = .F.
ENDWITH

ENDWITH

*--create the chart
loCpiSpiChart = loCpiSpiSheet.ChartObjects.Add(1,1,700,550).Chart
WITH loCpiSpiChart
.SetSourceData(loCpiSpiSheet.Range("C6"))
.HasDataTable = .T.
.ChartType = xlLine
.DataTable.ShowLegendKey = .T.
.HasTitle = .T.
.ChartTitle.Characters.Text = RTRIM( THISFORM.txtProgDesc.Value )
+ CRLF + "CPI/SPI Trend Data"
ENDWITH





Thanks
Woody