Using API to get Dimension Attributes

[highlnder8]

I am trying to populate a ComboBox with the element Values and the Name attribute as part of an input form I'm creating for our end users. I've been able to pull the dimension elements by making changes to the sample code provided with the install. However, pull the corresponding Attribute has been stumping me.

We are on TM1 9.4, using Excel 97 and VBA.

Here is my code:

Code: Select all

Sub ElementList()
'***************************************************************
'Description:
'            This procedure retrieves Dimension Elements and Name Attribute
'Parameters:
'            None
'Return:
'            None
'***************************************************************
    Const FIXED_STR_LENGTH = 30
    
    Dim hUser As Long, hServer As Long
    Dim sDimensionName As String, sAttribute As String
   
    Dim voDim As Long
    Dim voDim1 As Long
    Dim voTemp As Long
    Dim voTemp1 As Long
    Dim voTemp2 As Long
    Dim voTemp3 As Long
    Dim voTemp4 As Long
    Dim voEle As Long
    Dim hPool As Long
    Dim ibOK As Integer
    
    Dim viEleCount As Long
    Dim iEleCount As Integer
    Dim iCubeCount As Integer
    Dim pEleHandles As Long
    Dim iEle As Integer
    Dim pEleNames As Long
    Dim pEleAttr As Long
    Dim sEleName As String * FIXED_STR_LENGTH
    Dim iTemp As Integer
    Dim sAttributeName As String * FIXED_STR_LENGTH
    
    
    hUser = TM1_API2HAN
    hServer = TM1SystemServerHandle(hUser, "ph_bpm_test")
    sDimensionName = "PayEmployee"
    sAttribute = "Name"
    
    ' Create General Pool
    hPool = TM1ValPoolCreate(hUser)
    ' Locate Dimension
    voDim = TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerDimensions(), TM1ValString(hPool, sDimensionName, 0))
    
    ' Determine the number of elements in the dimension
    viEleCount = TM1ObjectListCountGet(hPool, voDim, TM1DimensionElements())
        iEleCount = TM1ValIndexGet(hUser, viEleCount)
        
    ' Create a pool for the element handles
    pEleHandles = TM1ValPoolCreate(hUser)
    
    ' Retrieve each element handle in succession, store in value pool
    For iEle = 1 To iEleCount
        voTemp = TM1ObjectListHandleByIndexGet(pEleHandles, voDim, TM1DimensionElements(), TM1ValIndex(hPool, iEle))
    Next iEle

    ' Create pools for element name and attribute
    pEleNames = TM1ValPoolCreate(hUser)
    pEleAttr = TM1ValPoolCreate(hUser)

    ' Look at each element, if there are no read rights skip
    ' otherwise retrieve name and attribute
    For iEle = 0 To iEleCount - 1
        voEle = TM1ValPoolGet(pEleHandles, iEle)
        If TM1ValObjectCanRead(hUser, voEle) <> 0 Then
            voTemp2 = TM1ObjectPropertyGet(pEleNames, voEle, TM1ObjectName())

            voTemp1 = TM1ObjectAttributeValueGet(pEleAttr, voEle, TM1ValString(hPool, sAttribute, 0))

        End If
     Next iEle


    ' Output Dimension Elements and Names
    iTemp = TM1ValPoolCount(pEleNames)
    For iEle = 0 To iTemp - 1
        voTemp3 = TM1ValPoolGet(pEleNames, iEle)
        
        TM1ValStringGet_VB hUser, voTemp3, sEleName, FIXED_STR_LENGTH
        
        voTemp4 = TM1ValPoolGet(pEleAttr, iEle)
        
'Code lockes up at this line right here.********************************************************
        TM1ValStringGet_VB hUser, voTemp4, sAttributeName, FIXED_STR_LENGTH

        Debug.Print "Element: " & sEleName & " Name: " & sAttributeName
        sEleName = ""
    Next iEle

        ' Destroy all pools
    TM1ValPoolDestroy (hPool)
    TM1ValPoolDestroy (pEleHandles)
    TM1ValPoolDestroy (pEleNames)
    TM1ValPoolDestroy (pEleAttr)
    Exit Sub
DONE:
        ' Destroy all pools
    TM1ValPoolDestroy (hPool)
    TM1ValPoolDestroy (pEleHandles)
    TM1ValPoolDestroy (pEleNames)
    TM1ValPoolDestroy (pEleAttr)

End Sub

I'm not sure what I'm doing wrong, I'm guessing it must be something with TM1ObjectAttributeValueGet. I've tried about 8 different variations on this code, changing the functions or parameters related to generating pEleAttr and this is the one that is probably the cleanest. Then main problem is the locking up of the machine at the line indicated. I don't know if the value is wrong or if the attributes line up correctly with the elements because I can't produce any output.

Thanks for your help everyone!

[Martin Ryan]

Rather than use the API you could just use the normal Excel functions, e.g. subnm, dimnm, dimsiz, and dbra. You just need to use the run command

for i=1 to run("dimsiz", "server:dimension")
elem=run("dimnm", "server:dimension", i)
next i

Cheers,
Martin

[highlnder8]

Simple, brilliant and it works beautifully!

Thanks for the fast reply, too.

I'm not sure why I didn't think about just using run statements, I had a "D'oh" moment when I read your suggestion.

Thanks!!