COMate: How can i use MDX (ADODB)

Windows specific forum
Post Reply
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

Only 1 cell of data returned!!! Is that right?
I may look like a mule, but I'm not a complete ass.
Top
CNESM
User
User
Posts: 54
Joined: Sat Jun 21, 2003 11:15 pm
Contact:
Contact CNESM

Post by CNESM »

Yes

The code had to give back one value!
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

It is saying that there is only one item of data retrieved from the data-source!

Oh well, in which case 2 simple alterations should do it!

Change the lines

Code: Select all

row = 1
column = 1
to

Code: Select all

row = 0
column = 0
(towards the top of the code).
I may look like a mule, but I'm not a complete ass.
Top
CNESM
User
User
Posts: 54
Joined: Sat Jun 21, 2003 11:15 pm
Contact:
Contact CNESM

Post by CNESM »

This doesn't help, srod!

The error is the same .... :?

i tested:

Code: Select all

Cell (1, 1) as string : " + obCell\GetStringProperty("FormattedValue")
to

Code: Select all

Cell (0, 0) as string : " + obCell\GetStringProperty("FormattedValue")
and/or

Code: Select all

    index = 0 
    column = 1
    SafeArrayPutElement_(*safeArray, @index, @column) 
    index = 1 
    row = 1
to

Code: Select all

    index = 0 
    column = 0
    SafeArrayPutElement_(*safeArray, @index, @column) 
    index = 1 
    row = 0
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

Think I give up at this point. There really is only so much I can do without being able to run the code. We must be very close though.

So why is the SQL statement only retrieving 1 cell of data?
I may look like a mule, but I'm not a complete ass.
Top
CNESM
User
User
Posts: 54
Joined: Sat Jun 21, 2003 11:15 pm
Contact:
Contact CNESM

Post by CNESM »

Srod, i understand you. Thanks for you help! But its hard to give support, when you don't can test the code ....

In normal situation the sql statement retrieve hundreds of data but for testing i only use one data call.

I will test it further. Thnaks srod for you create support. Maybe the other poeple can help? But i think so....
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

Must admit that I am a bit sketchy about the actual contents of the safearray because I can find very little documentation on exactly how it is supposed to be formatted in this case.

If you can switch to MSN or ICQ we should be able to get it working with a bit of tinkering.
I may look like a mule, but I'm not a complete ass.
Top
CNESM
User
User
Posts: 54
Joined: Sat Jun 21, 2003 11:15 pm
Contact:
Contact CNESM

Post by CNESM »

BTW: Enclosed a docu about the Analysis Service:

http://msdn.microsoft.com/en-us/library ... lient.aspx

ICQ would be great: 585314593, Name: xcEr. I only can use the icq2go because i am in the office.
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

Immediately before the line

Code: Select all

obCell = objADOMD\GetObjectProperty("Item(" + Str(var) + " as VARIANT)")
can you place the following lines and tell me what is debugged :

Code: Select all

column = objADOMD\GetIntegerProperty("Axes(0)\Positions(0)\Ordinal")       
row = objADOMD\GetIntegerProperty("Axes(1)\Positions(0)\Ordinal")
Debug "Column = " + Str(column)
Debug "Row = " + Str(row)
I may look like a mule, but I'm not a complete ass.
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

Solved via ICQ! :)

CNESM, if you could post the working code for others to use then that would be useful. :wink:
I may look like a mule, but I'm not a complete ass.
Top
CNESM
User
User
Posts: 54
Joined: Sat Jun 21, 2003 11:15 pm
Contact:
Contact CNESM

Post by CNESM »

Srod, the master of codes :D

Enclosed the runable code WITHOUT an array:

Code: Select all

Define.COMateObject objMDX, objADOMD, obAxis, obCell 
Define.COMateEnumObject enAxes 


objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog 
  If objADOMD 
  
    mdx.s = "with "
    mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 2009])"
    mdx.s = mdx.s + "select [Act1] on columns,  [Account].[Accounts].[304] on rows from"
    mdx.s = mdx.s + "(select [Company].[Company 200].[Level 1].&[200] on columns from"
    mdx.s = mdx.s + "(select [PRODLINE].[ProdLines].[Level 3].&[3] on columns from"
    mdx.s = mdx.s + "(select [Customer].[Customers].[All] on columns from"
    mdx.s = mdx.s + "(select [METALTYPE].[METALTYPES].[ALL] on columns from"
    mdx.s = mdx.s + "(select [DataSource].[DataSources].[DMC] on columns from"
    mdx.s = mdx.s + "(select [Currency].[Currencies].[EUR] on columns from"
    mdx.s = mdx.s + "(select [GCN].[GCN].[NET] on columns from"
    mdx.s = mdx.s + "(select [TCurrency].[Switch].[TCurrency] on columns from"
    mdx.s = mdx.s + "[FIBI])))))))) cell properties value"

    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK 
      objADOMD\SetProperty("Source = '"+mdx+"'") 
      objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") 
      If objADOMD\Invoke("Open(#Optional, #Optional)")  = #S_OK 
        numCells = 1 
        enAxes = objADOMD\CreateEnumeration("Axes") 
        If enAxes 
          obAxis = enAxes\GetNextObject() 
          While obAxis 
            numCells * obAxis\GetIntegerProperty("Positions\Count") 
            Debug obAxis\GetStringProperty("Name") 
            Debug "====" 
            obAxis\Release() 
            obAxis = enAxes\GetNextObject() 
          Wend 
          enAxes \Release() 
          Debug "There were " + Str(numCells) + " cell(s) retrieved." 
          Debug "====" 
          ;Now get some cell data! We use cell (1, 1). 
            obCell = objADOMD\GetObjectProperty("Item(0,0)")
            If obCell 
              Debug "Cell (0, 0) as string : " + obCell\GetStringProperty("Value") 
            Else 
              Debug "Problem getting cell object!" 
              Debug COMate_GetLastErrorDescription() 
            EndIf 
        EndIf 
      EndIf    
    Else 
      Debug COMate_GetLastErrorDescription() 
    EndIf 
    objADOMD\Release()
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf
Top
CNESM
User
User
Posts: 54
Joined: Sat Jun 21, 2003 11:15 pm
Contact:
Contact CNESM

Post by CNESM »

I cleaned up the code, so all users can better understand the code:

Code: Select all

Define.COMateObject objMDX, objADOMD, obCell 

objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset")
  If objADOMD 
  
    mdx.s = "with "
    mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 2009])"
    mdx.s = mdx.s + "select [Act1] on columns,  [Account].[Accounts].[304] on rows from"
    mdx.s = mdx.s + "(select [Company].[Company 200].[Level 1].&[200] on columns from"
    mdx.s = mdx.s + "(select [PRODLINE].[ProdLines].[Level 3].&[3] on columns from"
    mdx.s = mdx.s + "(select [Customer].[Customers].[All] on columns from"
    mdx.s = mdx.s + "(select [METALTYPE].[METALTYPES].[ALL] on columns from"
    mdx.s = mdx.s + "(select [DataSource].[DataSources].[DMC] on columns from"
    mdx.s = mdx.s + "(select [Currency].[Currencies].[EUR] on columns from"
    mdx.s = mdx.s + "(select [GCN].[GCN].[NET] on columns from"
    mdx.s = mdx.s + "(select [TCurrency].[Switch].[TCurrency] on columns from"
    mdx.s = mdx.s + "[FIBI])))))))) cell properties value"

    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')")=#S_OK 
      objADOMD\SetProperty("Source = '"+mdx+"'")
      objADOMD\SetProperty("ActiveConnection="+Str(objMDX)+" as COMateObject")
      If objADOMD\Invoke("Open")=#S_OK 
        obCell=objADOMD\GetObjectProperty("Item(0,0)")
        If obCell 
          Debug obCell\GetStringProperty("Value") 
        Else
          Debug COMate_GetLastErrorDescription() 
        EndIf 
      EndIf    
    Else 
      Debug COMate_GetLastErrorDescription() 
    EndIf 
    objADOMD\Release()
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf
What did srod change in the extrem code updating procedure :-) ?

The problem was to catch the return value. We had to change the GetIntergerProperty from:

Code: Select all

objADOMD\GetIntegerProperty("(0,0)")
to the GetObjectProperty:

Code: Select all

objADOMD\GetObjectProperty("Item(0,0)")
For more informationen take a look on msdn:
http://msdn.microsoft.com/en-us/library ... .item.aspx

And after this step we can catch the value that we want but also in a different way:

Code: Select all

obCell\GetStringProperty("Value")
This little changes make the code of my first post runable. Something the little things decide about win and defeat and srod are a big winner :-)

For mor information about the Microsoft Analysis Services take a look on msdn:

http://msdn.microsoft.com/en-us/library ... lient.aspx

The big problem is to translate code from vba to pb. Scrod said it wright with the statement that vba makes a lot of things in the background. So sometimes its not easy to translate vba code to pb.
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

The big problem is to translate code from vba to pb. Scrod said it wright with the statement that vba makes a lot of things in the background. So sometimes its not easy to translate vba code to pb.
Actually, in this case, we made it far harder than it actually was. Had we had our clear heads on I think we would have nailed this a lot sooner. You will see that the PB code is a very simple translation of the VBA code. It was all made harder by the conflicting information on the web and the fact that my OLE viewer reported different parameter types than those we have ended up using. My OLE viewer reported the use of a safearray (which was also implied by various code snippets I found) and yet no such thing was required or even possible in this case! I wonder why the appropriate type-library would report that we needed a safe-array in the first place? Then again, some c# code I found made clear use of a safe-array!!!! Very confusing! :)
I may look like a mule, but I'm not a complete ass.
Top
CNESM
User
User
Posts: 54
Joined: Sat Jun 21, 2003 11:15 pm
Contact:
Contact CNESM

Post by CNESM »

Hey srod,

after my break i worked on the code again and i tested to get more than one value from the server within only one call and i think its work. I get all neccessary values and the captions of the columns and rows and this without using an pb made array:

Code: Select all

Define.COMateObject objMDX, objADOMD, obCell ,obAxis1 ,obAxis2, Spreadsheet, obCell
Define.COMateEnumObject enAxes 

objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset")
  If objADOMD 
  
    mdx.s = "with "
    mdx.s = mdx.s + "member [ytd_Act_2006] as ([Measures].[YTD],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P13 2006]) "
    mdx.s = mdx.s + "member [ytd_Act_2007] as ([Measures].[YTD],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P13 2007]) "
    mdx.s = mdx.s + "member [ytd_Act_2008] as ([Measures].[YTD],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P13 2008]) "
    mdx.s = mdx.s + "member [01_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 2009]) "
    mdx.s = mdx.s + "member [02_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P2 2009]) "
    mdx.s = mdx.s + "member [03_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P3 2009]) "
    mdx.s = mdx.s + "member [04_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P4 2009]) "
    mdx.s = mdx.s + "member [05_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P5 2009]) "
    mdx.s = mdx.s + "member [06_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P6 2009]) "
    mdx.s = mdx.s + "member [07_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P7 2009]) "
    mdx.s = mdx.s + "member [08_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P8 2009]) "
    mdx.s = mdx.s + "member [09_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P9 2009]) "
    mdx.s = mdx.s + "member [10_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P10 2009]) "
    mdx.s = mdx.s + "member [11_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P11 2009]) "
    mdx.s = mdx.s + "member [12_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P12 2009]) "
    mdx.s = mdx.s + "member [13_Act_2009] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P13 2009]) "
    mdx.s = mdx.s + "member [MTP_2010] as ([Measures].[Periodic],[Scenario].[Scenarios].[MTP1],[Time].[YM].[Year].[2010]) "
    mdx.s = mdx.s + "member [MTP_2011] as ([Measures].[Periodic],[Scenario].[Scenarios].[MTP2],[Time].[YM].[Year].[2011]) "
    mdx.s = mdx.s + "set [mths] as {[ytd_Act_2006],[ytd_Act_2007],[ytd_Act_2008],[01_Act_2009],[02_Act_2009],[03_Act_2009],[04_Act_2009],[05_Act_2009],[06_Act_2009],[07_Act_2009],[08_Act_2009],[09_Act_2009],[10_Act_2009],[11_Act_2009],[12_Act_2009],[13_Act_2009],[MTP_2010],[MTP_2011]}"
    mdx.s = mdx.s + "select [mths] on columns, {[Account].[Accounts].[304],[Account].[Accounts].[K04]} on rows from"
    mdx.s = mdx.s + "(select [Company].[Company 200].[Level 1].&[200] on columns from"
    mdx.s = mdx.s + "(select [PRODLINE].[ProdLines].[Level 3].&[3] on columns from"
    mdx.s = mdx.s + "(select [Customer].[Customers].[All] on columns from"
    mdx.s = mdx.s + "(select [METALTYPE].[METALTYPES].[ALL] on columns from"
    mdx.s = mdx.s + "(select [DataSource].[DataSources].[DMC] on columns from"
    mdx.s = mdx.s + "(select [Currency].[Currencies].[EUR] on columns from"
    mdx.s = mdx.s + "(select [GCN].[GCN].[NET] on columns from"
    mdx.s = mdx.s + "(select [TCurrency].[Switch].&[TCurrency] on columns from"
    mdx.s = mdx.s + "[FIBI])))))))) cell properties value"

    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')")=#S_OK 
      objADOMD\SetProperty("Source = '"+mdx+"'")
      objADOMD\SetProperty("ActiveConnection="+Str(objMDX)+" as COMateObject")
      If objADOMD\Invoke("Open")=#S_OK 
        enAxes=objADOMD\CreateEnumeration("Axes") 
        If enAxes 
        
          obAxis1=enAxes\GetNextObject()
          obAxis2=enAxes\GetNextObject()
          
          cols = obAxis1\GetIntegerProperty("Positions\Count")-1
          rows = obAxis2\GetIntegerProperty("Positions\Count")-1
          
          If OpenWindow(0,0,0,1200,600,"COMate: OWC11.Spreadsheet-Demo",#PB_Window_SystemMenu)
              
            Spreadsheet=COMate_CreateActiveXControl(20,20,1145,350,"OWC11.Spreadsheet")
            If Spreadsheet
              Spreadsheet\GetObjectProperty("Sheets('3')\Delete")
              Spreadsheet\GetObjectProperty("Sheets('2')\Delete")
              Spreadsheet\SetProperty("DisplayToolbar = #False")
              Spreadsheet\SetProperty("DisplayOfficeLogo = #False")
              Spreadsheet\SetProperty("DisplayTitleBar = #False")
              Spreadsheet\SetProperty("ActiveWindow\DisplayWorkbookTabs = #False")
              For i = 0 To rows
                For k = 0 To cols
                  obCell=objADOMD\GetObjectProperty("Item("+Str(k)+","+Str(i)+")")
                  strvalue.s=ReplaceString(obCell\GetStringProperty("Value"),",",".") 
                  Spreadsheet\SetProperty("Cells("+Str(i+2)+","+Str(k+2)+") = '"+strvalue+"'")
                Next
              Next
              
              For i = 0 To rows
                Spreadsheet\SetProperty("Cells("+Str(i+2)+",1) = '"+objADOMD\GetstringProperty("Axes(1)\Positions("+Str(i)+")\Members(0)\Caption")+"'")
              Next
              
              For k = 0 To cols
                Spreadsheet\SetProperty("Cells(1,"+Str(k+2)+") = '"+objADOMD\GetstringProperty("Axes(0)\Positions("+Str(k)+")\Members(0)\Caption")+"'")
              Next

              Spreadsheet\SetProperty("Range('B3:S3')\NumberFormat = '0.0%'")
              Spreadsheet\SetProperty("Range('B2:S2')\NumberFormat = '#,##0'")
              Spreadsheet\invoke("Columns('A:S')\Columns\Autofit")

            Else
               Debug COMate_GetLastErrorDescription() 
            EndIf
            
            Repeat
            Until WindowEvent()=#PB_Event_CloseWindow
            Spreadsheet\Release()
          EndIf
          CloseWindow(0)  
        EndIf

      EndIf    
    Else 
      Debug COMate_GetLastErrorDescription() 
    EndIf 
    objADOMD\Release()
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf
Top
Post Reply

Return to “Windows”