5Nov/080
How to add a calculated measure (calculated field) to an Excel 2007 PivotTable with a SSAS data source
As it turns out, you can do it programatically as explained by Allan Folting here:
Microsoft Excel: Common Questions Around Excel 2007 OLAP PivotTables:
http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx
And the parts that I have to keep looking up (I use my blog as a notebook for things I don't want to forget ;-):
Sub AddCalculatedMeasure()
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[Measures].[Internet Sales Amount 25 %]"
strFormula = "[Measures].[Internet Sales Amount]*1.25"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember
End Sub
Sub AddCalculatedMember()
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38 25 %]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38]*1.25"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember
pvt.ViewCalculatedMembers = True
End Sub
Sub AddNamedSet()
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField
Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes")
End Sub
He also mentions that you can expose these members to Excel Services 2007 by creating the new objects and then removing the VBA code - very useful article.