This topic has not yet been rated - Rate this topic

CalculatedMembers.Add Method (Excel)

Adds a calculated field or calculated item to a PivotTable. Returns a CalculatedMember object.

expression .Add(Name, Formula, SolveOrder, Type)

expression A variable that represents a CalculatedMembers object.

Parameters

Name

Required/Optional

Data Type

Description

Name

Required

String

The name of the calculated member.

Formula

Required

String

The formula of the calculated member.

SolveOrder

Optional

Variant

The solve order for the calculated member.

Type

Optional

Variant

The type of calculated member.

Dynamic

Optional

Boolean

Specifies if the calculated member is recalculated with every update.

DisplayFolder

Optional

String

The name of the display folder for the calculated member.

HierarchizeDistinct

Optional

Boolean

Specifies whether to order and remove duplicates when displaying the hierarchy of the calculated member in a PivotTable report based on an OLAP cube.

Return Value

A CalculatedMember object that represents the new calculated field or calculated item.

The Formula argument must contain a valid MDX (Multidimensional Expression) syntax statement. The Name argument has to be acceptable to the Online Analytical Processing (OLAP) provider and the Type argument has to be defined.

If you set the Type argument of this method to xlCalculatedSet, then you must call the AddSet method to make the new field set visible in the PivotTable.

The following example adds a set to a PivotTable.

Note Note

Connection to the cube and existing pivot table is necessary for the sample to run.

Sub UseAddSet() 
 
 Dim pvtOne As PivotTable 
 Dim strAdd As String 
 Dim strFormula As String 
 Dim cbfOne As CubeField 
 
 Set pvtOne = ActiveSheet.PivotTables(1) 
 
 strAdd = "[MySet]" 
 strFormula = "'{[Product].[All Products].[Food].children}'" 
 
 ' Establish connection with data source if necessary. 
 If Not pvtOne.PivotCache.IsConnected Then pvtOne.PivotCache.MakeConnection 
 
 ' Add a calculated member titled "[MySet]" 
 pvtOne.CalculatedMembers.Add Name:=strAdd, _ 
 Formula:=strFormula, Type:=xlCalculatedSet 
 
 ' Add a set to the CubeField object. 
 Set cbfOne = pvtOne.CubeFields.AddSet(Name:="[MySet]", _ 
 Caption:="My Set") 
 
End Sub

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.