ELISANC Worksheet Function

[mikegrain]

A quick and dirty example of how it can be done, more a proof of concept than anything else.

But... Maybe somebody will find it useful

Returns a TRUE or FALSE as opposed to the super user friendly 1 or O of the equivalent rules function, could be enhanced in numerous ways and even re-written using the API (though I am not sure how much faster it would be in an interpreted environment like VBA).

Feel free to do with it what you will.

Cheers
Mike

ELISANC.zip

(9.9 KiB) Downloaded 1036 times

[mmatt]

This is a kind piece and works! Where to pay donations?

Attila

[TJMurphy]

Sweet, thank you!

Tony

[yyi]

Thank You, and here's some additional vba to list all the parents in between ancestor and descendant:
(so it can paste into subset editor)

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

[BariAbdul]

I couldn't see anything except the blank excel sheet.Thanks

[yyi]

Original author's zip attachment is a blank worksheet with "Elisanc()" function written in the module [Alt-F11]

my additional code can be used by substituting/putting in
1. your dimension name
2. your alias
3. a cell name "n_Level" somewhere on the sheet for the descendant
4. a cell name "v_Consol" somewhere on the sheet for the ancestor

then pick a blank cell and run the sub()
it's useful if you have a big hierarchy with many levels and many consolidations in between

hope that helps