Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables - Editing the data source shared by multiple pivot ta | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
build a pivot table from multiple other pivot tables. | Excel Discussion (Misc queries) | |||
how do I consolidate multiple pivot tables into one pivot table? | Excel Discussion (Misc queries) | |||
Creating multiple pivot tables from same cache | Excel Programming |