Hey guys!
Excel 2007
Tm1 9.4.1 FP003.
I am trying to use the function ELPARN in a rule to determine the number of parents an element has. Well strangely enough it returns 1 for all elements except the highest level elements which it returns 0(which is correct).
Has anyone seen this before? I have worked with ELPAR before and sometimes noticed it doesn't give me the correct answers. Is it just me or has anyone else noticed these functions not working properly all the time?
Cheerio!
Using Elparn
-
Alan Kirk
- Site Admin
- Posts: 6667
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Using Elparn
I know that this is an obvious question, but just for certainty's sake... is it possible that the elements do have only one parent? (Bearing in mind that ElParN returns the number of immediate parents of an element only, not the total number of ancestors which may be more than one level above it.)par3 wrote: Excel 2007
Tm1 9.4.1 FP003.
I am trying to use the function ELPARN in a rule to determine the number of parents an element has. Well strangely enough it returns 1 for all elements except the highest level elements which it returns 0(which is correct).
Has anyone seen this before? I have worked with ELPAR before and sometimes noticed it doesn't give me the correct answers. Is it just me or has anyone else noticed these functions not working properly all the time?
The only way that you'll get a number >1 is in a dimension with multiple hierarchies.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Re: Using Elparn
Allan,
I have to differ with you there. This is the explanation form the reference guide.
"ELPARN returns the number of parents of an element in a specified dimension. "
"ELPARN('Model','Wagon 4WD')
In the Model dimension, the element Wagon 4WD is a child of both Total Wagons and Total 4WD. Therefore, both Total Wagons and Total 4WD are parents of Wagon 4WD. The function returns 2."
Now unless Total Wagons and Total 4WD are to different roll-ups within the same dimension I understand.
Anyway if this is the case my rule won't work. I need to determine the highest level parent for an element. Any idea how I can do that?
I have to differ with you there. This is the explanation form the reference guide.
"ELPARN returns the number of parents of an element in a specified dimension. "
"ELPARN('Model','Wagon 4WD')
In the Model dimension, the element Wagon 4WD is a child of both Total Wagons and Total 4WD. Therefore, both Total Wagons and Total 4WD are parents of Wagon 4WD. The function returns 2."
Now unless Total Wagons and Total 4WD are to different roll-ups within the same dimension I understand.
Anyway if this is the case my rule won't work. I need to determine the highest level parent for an element. Any idea how I can do that?
-
Michel Zijlema
- Site Admin
- Posts: 713
- Joined: Wed May 14, 2008 5:22 am
- OLAP Product: TM1, PALO
- Version: both 2.5 and higher
- Excel Version: 2003-2007-2010
- Location: Netherlands
- Contact:
Re: Using Elparn
Hi,
ELPARN returns the total number of direct parents an element has. Most likely these parents will be in different (parallel) hierarchies, but it is possible tht these are in a single hierarchy (but this is not really relevant in relation to what the function does).
There is no rules function that determines the highest level ancestor of an element, but you maybe could do a check the other way around and use the ELISANC function to check whether an element is somewhere underneath a node in the hierarchy.
Michel
ELPARN returns the total number of direct parents an element has. Most likely these parents will be in different (parallel) hierarchies, but it is possible tht these are in a single hierarchy (but this is not really relevant in relation to what the function does).
There is no rules function that determines the highest level ancestor of an element, but you maybe could do a check the other way around and use the ELISANC function to check whether an element is somewhere underneath a node in the hierarchy.
Michel
Re: Using Elparn
Thanks Michel.
Can't really use ELISANC because I first need to determine what the highest level parent is before I can test if the element is a child of it.
Can't really use ELISANC because I first need to determine what the highest level parent is before I can test if the element is a child of it.
-
jstrygner
- MVP
- Posts: 195
- Joined: Wed Jul 22, 2009 10:35 pm
- OLAP Product: TM1
- Version: 9.5.2 FP3
- Excel Version: 2010
Re: Using Elparn
To be honest I do not understand where you see a difference between reference guide and Alans explanation.par3 wrote:I have to differ with you there. This is the explanation form the reference guide.
I think it will become easier if we use the same terminology. Otherwise it gets people confused. Following TM1 logic I suggest using:par3 wrote:what the highest level parent is before I can test if the element is a child of it.
- parent (for immediate parents only),
- child (for immediate children only),
- ancestor (both for immediate parents and parents of their parents and all the way to the top (root) element),
- descendant (both for immediate children and children of their children and all the way to level 0 elements).
Coming back to your problem:
The way I understand from your posts, you are trying to count a number of ancestors for an element you write a rule for. You have only one root element in your dimension (you wrote only one element returns 0 for ELPARN) but there are of course many elements, some of them are more deep in the hierarchy, some more shallow. What you also have is every element (except root one) has only one parent, so only one number of ancestors.
If that is the real situation:
1. I am affraid there is no nice way to do it via rules, so consider if TI is not an option.
2. If not, you can try to use the ELPAR as many times as many levels your dimension has (in your case dimension has as many levels as the single root element's level + 1) and combining it with IF function and checking, if the parent name equals root's name - yes, this will look terrible, especially when you have many levels, but it may do the work (it would be nice if you knew the potentially maximum level your dimension may ever have in the future).
HTH