ElisAnc in Excel

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

ElisAnc in Excel

Post by Martin Ryan »

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.
Attachments
ELISANC.zip
(9.9 KiB) Downloaded 613 times
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Post Reply