Show All

AddSet Method

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.

expression   Required. An expression that returns one of the objects in the Applies To list.

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.

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