Creating calculated members/measures and sets in Excel 2007 OLAP PivotTables

Excel doesn't offer a user interface for creating calculated members/measures and sets in PivotTables connected to OLAP cubes. However, in the object model there is support for creating personal calculations and sets in PivotTables with OLAP data sources.

The CalculatedMembers collection holds calculations and sets private to a PivotTable and it has an Add method for creating new calculations and sets.

Below are examples of creating a calculated measure, a calculated member and a named set using the CalculatedMembers collection of the PivotTable object. All the examples are using the sample Adventure Works database that comes with Analysis Services 2005.

Example of adding a calculated measure in an Excel PivotTable connected to an OLAP data source:

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

Calculated measure "Internet Sales Amount 25%" added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Example of adding a calculated member to the Adventure Works Product level of the Product Categories hierarchy in an Excel PivotTable connected to an OLAP data source:

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

The screenshot below shows the PivotTable with the calculated member ("Mountain-100 Silver, 38 25%") added to the Product level of the Product Categories hierarchy.

Calculated member "Mountain-100 Silver, 38 25%" added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Example of adding a named set in an Excel PivotTable connected to an OLAP data source:

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

The screenshot below shows the PivotTable with the named set ("Mountain Bikes") added.

Named set "Mountain Bikes" added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Note that Excel 2007 only support named sets consisting of members from a single hierarchy.

Also note that even though Excel Services 2007 doesn't support Excel workbooks with macros/code, you can still use code as illustrated here to create calculated members/measures and named sets, remove the code after creating these and then publish the workbook to Excel Services. This way the calculations created in Excel can be exposed through Excel Services.