TM1 Forum

Hi All,

Has anyone tried creating a grid in asp.net using TM1? Obviously you can use tm1 web to create this sort of thing but I wanted to create this in a seperate webpage with my other flash graphs that I have working. It doesn't look like management is going to fork out for EV so I need to come up with a scorecard of sorts that can hold my flash graphs and grids that would accompany that data. I have seen this example on the net like this in c#:

http://www.codeproject.com/KB/database/ ... _grid.aspx

Does anyone have anything similar but applicable to TM1 and in VB.net? I am sure with much pain i would be able to create something similar ( and if I do I will post it here, this should give me somehting to do for the next few days ))

Cheers

Shock

write your own web front end linked to TM1? you're a braver man than any of us!!

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