Show All

CalculatedMembers Collection

A collection of all the CalculatedMember objects on the specified PivotTable. Each CalculatedMember object represents a calculated field or calculated item.

Using the CalculatedMembers collection

Use the CalculatedMembers property of the PivotTable object to return a CalculatedMembers collection. The following example adds a set to a PivotTable, assuming a PivotTable exists on the active worksheet.

Sub UseCalculatedMember()

    Dim pvtTable As PivotTable

    Set pvtTable = ActiveSheet.PivotTables(1)

    pvtTable.CalculatedMembers.Add Name:="[Beef]", _
        Formula:="'{[Product].[All Products].Children}'", _
        Type:=xlCalculatedSet

End Sub

Note   For the Add method in the previous example, the Formula argument must have a valid MDX syntax statement. The Name argument has to be acceptable to the Online Analytical Processing (OLAP) provider and the Type argument has to be defined.