Code : utility code for converting cellset to a data table

 

Private Function getDataTable(ByVal cs As AdomdClient.CellSet) As DataTable
        'design the datatable
        Dim dt As New DataTable
        Dim dc As DataColumn
        Dim dr As DataRow

        'add the columns
        dt.Columns.Add(New DataColumn("Description")) 'first column
        'get the other columns from axis
        Dim p As AdomdClient.Position
        Dim name As String
        Dim m As AdomdClient.Member
        For Each p In cs.Axes(0).Positions
            dc = New DataColumn
            name = ""
            For Each m In p.Members
                name = name + m.Caption + " "
            Next
            dc.ColumnName = name
            dt.Columns.Add(dc)
        Next

        'add each row, row label first, then data cells
        Dim y As Integer
        Dim py As AdomdClient.Position
        y = 0
        For Each py In cs.Axes(1).Positions
            dr = dt.NewRow 'create new row

            ' Do the row label
            name = ""
            For Each m In py.Members
                name = name & m.Caption & ""

            Next
            dr(0) = name 'first cell in the row

            ' Data cells
            Dim x As Integer
            For x = 0 To cs.Axes(0).Positions.Count - 1
                dr(x + 1) = cs(x, y).FormattedValue 'other cells in the row
            Next

            dt.Rows.Add(dr) 'add the row
            y = y + 1
        Next

        Return dt
    End Function