As promised here is some working code. Thankfully the chap that wrote up the article at the last link I posted was quite helpful. This the link he provided:
http://www.aspfree.com/c/a/MS-SQL-Serve ... dot-NET/3/
And here is working code. This probably could be written better, still a noob when it comes to vb.net (basically the code creates a table, based on this you could take it further). Any comments are welcomed. Hoepfully this encourages others to add more asp.net code and share there thoughts in this thread.
imports ADOMD
Imports System.Data
Imports System.Data.OleDb
Partial Class addtabletest
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Build the MDX statement(Query)
Dim szMDX As String
szMDX = "SELECT {[accounts].[AAPT_REV]} ON COLUMNS,"
szMDX = szMDX & "{[Month].[Jul],[Month].[Aug],[Month].[Sep],[Month].[Oct],[Month].[Nov],[Month].[Dec],[Month].[Jan],[Month].[Feb],[Month].[Mar],[Month].[Apr],[Month].[May],[Month].[Jun]} ON ROWS"
szMDX = szMDX & " FROM [Reportingaapt]"
szMDX = szMDX & " WHERE ([Company].[TPWT],[transindicator].[All Indicators],[tradingindicator].[All tradingpartners],"
szMDX = szMDX & "[accessmethod].[ALLAccessMethods],[coo_ausext].[Au_ext],[measures].[Financial],[Year].[2008],[Version].[Reported],[product].[TOT_PRODUCT])"
' Connect to the OLAP server
Dim cn
cn = Server.CreateObject("ADODB.Connection")
cn.Mode = 3
cn.CursorLocation = 2
cn.IsolationLevel = 4096
cn.Open("DATA SOURCE=tm1_aapt;PROVIDER=TM1OLAP;Location=Aunswa155", "user name", "password")
'-----------------------------------------------------------------------------------
' Create a cellset
Dim cs
cs = Server.CreateObject("ADOMD.Cellset")
cs.ActiveConnection = cn
cs.Open(szMDX)
'display the cellset
Me.DataGrid1.DataSource = New DataView(getDataTable(cs))
Me.DataGrid1.DataBind()
'clear the resources
cs.Close()
cn.Close()
End Sub
Private Function getDataTable(ByRef cs As 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 Position
Dim name As String
Dim m As 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 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 ' + "<BR>"
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
End Class
))