TM1 Forum

Code: Select all

Public vDim As String, vAnc As String, vDsc As String, vPar As String, vTree() As String

Sub build_up_single_hierarchy()
  Dim c As Excel.Range, nPar As Integer, i As Integer, r As Integer
  Dim vDepth As Integer
  vDim = "tm1server:Dimension A"
  vAnc = Range("v_Consol").Value
  vDsc = Range("n_Level").Value
  vPar = ""
  
  vDepth = Run("Ellev", vDim, vAnc)
  
  Erase vTree
  ReDim vTree(vDepth)
  
  Set c = ActiveCell
  c.Value = vDsc
  vTree(r) = vDsc
  r = 1
  If ELISANC(vDim, vAnc, vDsc) = False Then Exit Sub
  
  While vPar <> vAnc
      nPar = Run("elparn", vDim, vDsc)
      Debug.Print "Parent #"; nPar
      For i = 1 To nPar
        vPar = Run("ELPAR", vDim, vDsc, i)
        vPar = Run("DBRA", vDim, vPar, "Alias 1")
        If ELISANC(vDim, vAnc, vPar) Then
            c.Offset(r, 0).Value = vPar   'display as it goes
            vTree(r) = vPar               'store elements
            r = r + 1
            Exit For
        End If
      Next i
      
      vDsc = vPar   'level up descendant ~not using 2x parent variables
  Wend
  c.Offset(r, 0).Value = vDsc
  vTree(r) = vDsc
  
  Application.Wait (Now() + TimeSerial(0, 0, 1))        'attempt at dramatic effect
  r = 0
  For i = UBound(vTree) To 0 Step -1
    If vTree(i) <> "" Then
        Debug.Print i; vTree(i)
        c.Offset(r).Value = vTree(i)
        r = r + 1
    End If
  Next
End Sub