PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

COMate: How can i use MDX (ADODB)

https://www.purebasic.fr/english/viewtopic.php?t=37949

Page 3 of 3

Posted: Mon Jun 29, 2009 3:27 pm
by srod
Only 1 cell of data returned!!! Is that right?

Posted: Mon Jun 29, 2009 3:30 pm
by CNESM
Yes

The code had to give back one value!

Posted: Mon Jun 29, 2009 3:31 pm
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).

Posted: Mon Jun 29, 2009 3:38 pm
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

Posted: Mon Jun 29, 2009 3:39 pm
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?

Posted: Mon Jun 29, 2009 3:45 pm
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....

Posted: Mon Jun 29, 2009 3:48 pm
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.

Posted: Mon Jun 29, 2009 3:55 pm
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.

Posted: Mon Jun 29, 2009 4:02 pm
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)

Posted: Mon Jun 29, 2009 4:31 pm
by srod
Solved via ICQ! :)

CNESM, if you could post the working code for others to use then that would be useful. :wink:

Posted: Mon Jun 29, 2009 4:34 pm
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

Posted: Tue Jun 30, 2009 7:00 am
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.

Posted: Tue Jun 30, 2009 10:38 am
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! :)

Posted: Tue Jun 30, 2009 1:22 pm
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
All times are UTC+01:00
Page 3 of 3

Powered by phpBB® Forum Software © phpBB Limited