Share via

CubeFields.AddSet Method

  • Article
Excel Developer Reference

Adds a new CubeField object to the CubeFields collection. The CubeField object corresponds to a set defined on the Online Analytical Processing (OLAP) provider for the cube.

Syntax

expression.AddSet(Name, Caption)

expression   A variable that represents a CubeFields object.

Parameters

Name Required/Optional Data Type Description
Name Required String A valid name in the SETS schema rowset.
Caption Required String A string representing the field that will be displayed in the PivotTable view.

Return Value
CubeField

Remarks

If a set with the name given in the argument Name does not exist, the AddSet method will return a run-time error.

Example

In this example, Microsoft Excel adds a set titled "My Set" to the CubeField object. This example assumes an OLAP PivotTable report exists on the active worksheet. Also, this example assumes a field titled "Product" exists.

Visual Basic for Applications 
  Sub UseAddSet()

Dim pvtOne As PivotTable
Dim strAdd As String
Dim strFormula As String
Dim cbfOne As CubeField

Set pvtOne = Sheet1.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.<strong>AddSet</strong>(Name:="[MySet]", _
    Caption:="My Set")



End Sub

See Also