use Cellset

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