Using the Cellset Object

Using the Cellset Object

You can use Microsoft® ActiveX® Data Objects (Multidimensional) (ADO MD) to retrieve Multidimensional Expressions (MDX) query results from a local cube using the Cellset object. To retrieve a tabular result set, use the ADO Command and Recordset objects. For more information about the Cellset object, see the ADO MD documentation.

Examples

A. Using the Cellset Object

The following example uses a Connection object to define a connection to the Analysis server. The Source property of the Cellset object is then set to an MDX query that returns all of the measures for product families and promotion media. The ActiveConnection property of the Cellset object is then set to the ActiveConnection property of the Connection object, and the Open method is called to retrieve the actual results.

The Cellset object contains a collection called Axes, which describes each axis returned by the MDX query. There is one Axis object in this collection for each dimension you request. Each Axis object contains a Positions collection, which contains information about the individual rows, columns, pages, and so on of the returned result set. In this example, a Microsoft FlexGrid control is formatted to display the results of the query:

Dim conn As New ADODB.Connection
Dim cst As New ADOMD.Cellset
Dim axs As ADOMD.Axis
Dim pos As ADOMD.Position
Dim iCol As Integer, cCol As Integer
Dim iRow As Integer, cRow As Integer
Dim nFixedCols As Integer, nFixedRows As Integer

    'Set up the connection to the server.
    conn.ConnectionString = "Datasource=LocalHost; Provider=msolap; Initial Catalog=FoodMart 2000;"
    conn.Open
    Set cst.ActiveConnection = conn ' You must use Set. 
    cst.Source = "Select CrossJoin([Product].[Product Family].Members, " & _
        "[Promotion Media].Members)  on rows," & _
        "[Measures].Members on Columns " & _
        "From Sales"
    cst.Open
    
    'Set up the FlexGrid control.
    MSFlexGrid1.Clear
    nFixedCols = 2
    nFixedRows = 1
    cCol = cst.Axes(0).Positions.Count
    MSFlexGrid1.Cols = cCol + nFixedCols
    cRow = cst.Axes(1).Positions.Count
    MSFlexGrid1.Rows = cRow + nFixedRows
    MSFlexGrid1.FixedCols = nFixedCols
    MSFlexGrid1.FixedRows = nFixedRows
        MSFlexGrid1.MergeCol(0) = True
    MSFlexGrid1.MergeCol(1) = True
    
    'Add column headers.
    iCol = 2
    For Each pos In cst.Axes(0).Positions
        'The caption for each member is used as the header.
        MSFlexGrid1.TextMatrix(0, iCol) = pos.Members(0).Caption
        iCol = iCol + 1
    Next
    
    'Add row headers.
    iRow = 1
    For Each pos In cst.Axes(1).Positions
        'The CrossJoin function in MDX indicates that this axis will have two members per position.
        MSFlexGrid1.TextMatrix(iRow, 0) = pos.Members(0).Caption
        MSFlexGrid1.TextMatrix(iRow, 1) = pos.Members(1).Caption
        iRow = iRow + 1
    Next
    
    'Iterate through the cellset array values.
    For iCol = 0 To cCol - 1 
        For iRow = 0 To cRow - 1
            ' Retrieve each value with the default method of the cst object.
            MSFlexGrid1.TextMatrix(iRow + nFixedRows, iCol + nFixedCols) = cst(iCol, iRow).Value
        Next
    Next