CubeFields.AddSet Method (Excel)
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.
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.AddSet(Name:="[MySet]", _
Caption:="My Set")
End Sub