TM1 Forum

Posted: **Thu Aug 28, 2008 3:11 pm**

Steve Vincent wrote:TM1, and TI in particular has quite a lot of inbuilt functions but occasionally they miss a few (ELISANC in Excel for one).

A workaround for the missing Elisanc function in Excel is to do this
- Build a two dimensional cube called zElisAnc with dim1 called 'ElisancArgs' and dim2 'StringVal'
- Populate 'ElisancArgs' with the N elements Child, Ancestor, Dimension, Result
- Populate 'StringVal' with the S element 'String'
- Create a rule that says

Code: Select all

['Result', 'String'] = S:
if(elisanc(
DB('zElisAnc', 'Dimension', 'String'), 
DB('zElisAnc', 'Ancestor', 'String'),
DB('zElisAnc', 'Child', 'String'))=1, 'True', 'False');

Now you can set up a VBA function like so

Code: Select all

Function elisanc(child As String, ancestor As String, dimension As String) as Boolean
Dim cube As String
cube = "server:zElisanc"
Run "dbsw", child, cube, "Child", "String"
Run "dbsw", ancestor, cube, "Ancestor", "String"
Run "dbsw", dimension, cube, "Dimension", "String"
elisanc = Run("dbrw", cube, "Result", "String")="True"
End Function

Calling the function in Excel/VBA will now return the result True or False.

(Hat-tip, Steve Rowe)

EDIT: Also attached Mike Grain's method for doing this. He uses an iterative process solely in VB.

TM1 Forum

ElisAnc in Excel

ElisAnc in Excel