Page 1 of 1
Retrieving member name by alias
Posted: Sun Jun 24, 2018 5:00 pm
by PTSD
Sorry I’m completely new to IBM Planning and Analytics. I’m looking for a simple function that will retrieve full member name based on alias. Basically the equivalent of EVDES() in SAP.
I found some examples with DIMIX and DIMNM, but can’t get those arguments to work.
For example, say I have 5001 as a member of my natural accounts dimensions. What for formula would retrieve the account name, in this case ‘salaries overtime’?
Thanks for your help!
Re: Retrieving member name by alias
Posted: Sun Jun 24, 2018 5:53 pm
by declanr
A dimix enclosed within a dimnm should work across all interfaces, what interface are you using?
TI, rules, perspectives etc?
Re: Retrieving member name by alias
Posted: Sun Jun 24, 2018 8:32 pm
by Wim Gielis
Apart from your question, wouldn’t be 5001 the element and salaries overtime the alias ?
Re: Retrieving member name by alias
Posted: Sun Jun 24, 2018 10:38 pm
by PTSD
Wim Gielis wrote: ↑Sun Jun 24, 2018 8:32 pm
Apart from your question, wouldn’t be 5001 the element and salaries overtime the alias ?
Good point, shows my lack of understand TM1 infrastructure. So, based on attached, my alias ID is "5010" and alias description is "Compensation - Salary? Am I looking at this right? And would would be the function do display the description based on ID?
Thanks.
Re: Retrieving member name by alias
Posted: Mon Jun 25, 2018 7:32 am
by lotsaram
PTSD wrote: ↑Sun Jun 24, 2018 10:38 pm
Wim Gielis wrote: ↑Sun Jun 24, 2018 8:32 pm
Apart from your question, wouldn’t be 5001 the element and salaries overtime the alias ?
Good point, shows my lack of understand TM1 infrastructure. So, based on attached, my alias ID is "5010" and alias description is "Compensation - Salary? Am I looking at this right? And would would be the function do display the description based on ID?
Thanks.
No one is going to be able to tell you how
your system is set up based on what you provided. All that can be deduced with absolute certainty from the screenshot you attached is that the "Account Description" alias in the account dimension for the selected element is "5010 - COMPENSATION - SALARY". What the element's principal name is would be a
guess. Based on how systems are usually set up it is likelly "5010" but many systems have fixed length account numbers padded with leading 0s so is could also be "00005010" or it might be something else entirely.
We can't answer this question. But you can easily just by deselecting all aliases, then the principal name will be displayed.
Re: Retrieving member name by alias
Posted: Mon Jun 25, 2018 5:55 pm
by PTSD
lotsaram wrote: ↑Mon Jun 25, 2018 7:32 am
PTSD wrote: ↑Sun Jun 24, 2018 10:38 pm
Wim Gielis wrote: ↑Sun Jun 24, 2018 8:32 pm
Apart from your question, wouldn’t be 5001 the element and salaries overtime the alias ?
Good point, shows my lack of understand TM1 infrastructure. So, based on attached, my alias ID is "5010" and alias description is "Compensation - Salary? Am I looking at this right? And would would be the function do display the description based on ID?
Thanks.
No one is going to be able to tell you how
your system is set up based on what you provided. All that can be deduced with absolute certainty from the screenshot you attached is that the "Account Description" alias in the account dimension for the selected element is "5010 - COMPENSATION - SALARY". What the element's principal name is would be a
guess. Based on how systems are usually set up it is likelly "5010" but many systems have fixed length account numbers padded with leading 0s so is could also be "00005010" or it might be something else entirely.
We can't answer this question. But you can easily just by deselecting all aliases, then the principal name will be displayed.
Sorry, I got zero IT background, hence not asking the right question (for this group). I'm on the finance/account side, just trying to understand the tool we are about to implement...
Looks I am getting close to articulating the equation though...
So, if I DIMIX(Cube:Account","5010"), it returns a 4 digit number - 3659 (assuming this is the index number). If I DIMNM("Cube:Account","3659") it returns 5010.
What I'm trying to get to is returning just the description "Compensation - Salary" based on "5010"(alias) or "3659" (index number).
Is "Compensation - Salary" and attribute of "5010" alias? Is there a function to return just the attribute?
Thanks.
Re: Retrieving member name by alias
Posted: Mon Jun 25, 2018 6:11 pm
by gtonkin
You can try using:
DBRA(<dimension name>,<element>,<alias>) e.g DBRA('Account','5010','Account Description') if in Perspectives (hoping PAX too)
or in rules, use ATTRS(<dimension name>,<element>,<alias>)
HTH
Re: Retrieving member name by alias
Posted: Mon Jun 25, 2018 7:48 pm
by PTSD
gtonkin wrote: ↑Mon Jun 25, 2018 6:11 pm
You can try using:
DBRA(<dimension name>,<element>,<alias>) e.g DBRA('Account','5010','Account Description') if in Perspectives (hoping PAX too)
or in rules, use ATTRS(<dimension name>,<element>,<alias>)
HTH
Thank you!!! Exactly what I was looking for.
The result I'm getting now is "5010 - Compensation - Salary". I'd rather only see "Compensation Salary", but I guess thats what we loaded into the attribute field (concatenated ID and description).
Re: Retrieving member name by alias
Posted: Mon Jun 25, 2018 7:56 pm
by gtonkin
Try using a scan and subst function to find the hyphen between the code and description then subst to chop from the right to the end. If your codes are all uniform in length, you can subst from character 8 onwards (or wherever the description starts)
Re: Retrieving member name by alias
Posted: Mon Jun 25, 2018 8:08 pm
by PTSD
gtonkin wrote: ↑Mon Jun 25, 2018 7:56 pm
Try using a scan and subst function to find the hyphen between the code and description then subst to chop from the right to the end. If your codes are all uniform in length, you can subst from character 8 onwards (or wherever the description starts)
Thanks again, I check out the syntax for subset. For now, since I'm working in excel, I'm just using excel formula to truncate the leading 8 characters... but not sure if it makes in clunkier/slower. ...
Re: Retrieving member name by alias
Posted: Mon Jun 25, 2018 8:58 pm
by lotsaram
PTSD wrote: ↑Mon Jun 25, 2018 8:08 pm
Thanks again, I check out the syntax for subset. For now, since I'm working in excel, I'm just using excel formula to truncate the leading 8 characters... but not sure if it makes in clunkier/slower. ...
You could truncate (e.g use a LEFT or MID formula) but it isn't going to work for all cases. But the universal way to get the principal name from an alias name in TM1/Planning Analytics is the one you already deduced (wrap a DIMIX formula inside a DIMNM formula.)