CalculatedMembers Object (Excel)

A collection of all the CalculatedMember objects on the specified PivotTable.

Each CalculatedMember object represents a calculated member or calculated measure.

Use the CalculatedMembers property of the PivotTable object to return a CalculatedMembers collection.

There are three supported types of calculated members: Named Sets, Calculated Measures, and Calculated Members. Object model support has been available for all three of these types since Excel 2010. User interface support was made available for Named Sets in Excel 2010. In Excel 2013, the OLAP Calculated Members and Calculated Measures feature was created to build a user interface for the calculated members and measures object model.

Named Sets are used exactly the same as in Excel 2010. Named Sets should continue to use the method CalculatedMembers.CalculatedMembers.Add Method (Excel) and the type XlCalculatedMemberType Enumeration (Excel).

Calculated Members have the following changes for Excel 2013:

Calculated Measures have the following changes for Excel 2013:

The following example adds a set to a PivotTable, assuming a PivotTable from the FoodMart SQL database 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 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.

Show:
© 2014 Microsoft