i (want) to use COMate to work with MDX. This Analysis Service based on a simple ADODB connection with a near sql syntacs. I create a runable sourcecode in VBA, now i want to translate the code to PB but i have some problem with the code. I dont know where the problem is. Maybe the objADOMD open call can be the source of trouble (I dont think that it is right to consign the MDX object as normal string, but i dont find another way).
Enclosed the runable VBA sourcecode:
Code: Select all
Public Const sSERVER = "DB.server.com"
Public Const sPROVIDER = "MSOLAP.3"
Public Const sINITIALCATALOG = "FIBI"
Public oCompCn As New ADODB.Connection 'Global Connection
Dim cst As ADOMD.Cellset
Dim mdx As String
Dim i As Long
Dim k As Long
Dim cl As String
Dim rows As Long
Dim cols As Long
vCube = "zxCube"
vCompany = "[Company].[Company 200].[Level 1].&[200]"
vCurrency = "[Currency].[Currencies].[EUR]"
vCustomer = "[Customer].[Customers].[All]"
vDatasource = "[DataSource].[DataSources].[DMC]"
vGCN = "NET"
vMEALTYPE = "[METALTYPE].[METALTYPES].[ALL]"
vProdLine = "[PRODLINE].[ProdLines].[Level 3].&[3]"
vPeriod = "P1"
vYear = "2009"
mdx = "with "
mdx = mdx + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[" & vPeriod & " " & vYear & "]) "
mdx = mdx + "select [Act1] on columns, [Account].[Accounts].[304] on rows from "
mdx = mdx + "( select {" & vCompany & "} on columns from "
mdx = mdx + "( select {" & vProdLine & "} on columns from "
mdx = mdx + "( select {" & vCustomer & "} on columns from "
mdx = mdx + "( select {" & vMEALTYPE & "} on columns from "
mdx = mdx + "( select " & vDatasource & " on columns from "
mdx = mdx + "( select " & vCurrency & " on columns from "
mdx = mdx + "( select [GCN].[GCN].[" & vGCN & "] on columns from "
mdx = mdx + "( select [TCurrency].[Switch].&[TCurrency] on columns from "
mdx = mdx + "[" & vCube & "] )))))))) cell properties value "
If oCompCn.State = ADODB.ObjectStateEnum.adStateClosed Then
oCompCn.Open ("Provider=" & sPROVIDER & ";Data Source=" & sSERVER & ";Initial Catalog=" & sINITIALCATALOG & ";")
End If
Set cst = New ADOMD.Cellset
cst.Source = mdx
cst.ActiveConnection = oCompCn
cst.Open
'cst.Open mdx, oCompCn 'This works too
MsgBox cst(0, 0)
Code: Select all
objMDX=COMate_CreateObject("ADODB.Connection")
If objMDX
objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog
If objADOMD
vCube.s = "zxCube"
vCompany.s = "[Company].[Company 200].[Level 1].&[200]"
vCurrency.s = "[Currency].[Currencies].[EUR]"
vCustomer.s = "[Customer].[Customers].[All]"
vDatasource.s = "[DataSource].[DataSources].[DMC]"
vGCN.s = "NET"
vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]"
vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]"
vPeriod.s = "P4"
vYear.s = "2009"
vForeCastType.s = "1"
mdx.s = "with "
mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) "
mdx.s = mdx.s + "select [Act1] on columns, [Account].[Accounts].[304] on rows from "
mdx.s = mdx.s + "( select {" + vCompany + "} on columns from "
mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from "
mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from "
mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from "
mdx.s = mdx.s + "( select " + vDatasource + " on columns from "
mdx.s = mdx.s + "( select " + vCurrency + " on columns from "
mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from "
mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from "
mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value "
objMDX\SetProperty("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") ;invoke?
If objMDX
objADOMD\SetProperty("Source = "+mdx.s)
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX))
objADOMD\SetProperty("Open")
Debug objADOMD\GetIntegerProperty("(0,0)")
Else
Debug "error3"
EndIf
Else
Debug "error2"
EndIf
Else
Debug "error1"
EndIf
objADOMD\Release()
objMDX\Release()
Florian
