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

4 comments:

rossale said...

Hi,
thanks for this very interesting script !!!
But, I think that there's an error: the "Cell Collection" it doesn't seem to be multidimensional (cs(x, y).FormattedValue ???).


' 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

How can I do?

Thank's
Alessandro

michelle said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.


Elaina

http://www.freearticletrove.com/

Anonymous said...

Can anyone recommend the top performing Software Deployment software for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: [url=http://www.n-able.com] N-able N-central remote support manager
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

Sam Kane said...

Here are this and some other articles on Displaying OLAP Data: http://ssas-wiki.com/w/Articles#Displaying_OLAP_Data

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll