Retrieving member name by alias
-
- Posts: 13
- Joined: Sun Jun 24, 2018 12:58 am
- OLAP Product: ABM Analytics
- Version: latest
- Excel Version: O365 V1805
Retrieving member name by alias
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!
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!
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Retrieving member name by alias
A dimix enclosed within a dimnm should work across all interfaces, what interface are you using?
TI, rules, perspectives etc?
TI, rules, perspectives etc?
Declan Rodger
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Retrieving member name by alias
Apart from your question, wouldn’t be 5001 the element and salaries overtime the alias ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 13
- Joined: Sun Jun 24, 2018 12:58 am
- OLAP Product: ABM Analytics
- Version: latest
- Excel Version: O365 V1805
Re: Retrieving member name by 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?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 ?
Thanks.
- Attachments
-
- File.PNG (92.35 KiB) Viewed 7388 times
-
- MVP
- Posts: 3657
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Retrieving member name by alias
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.PTSD wrote: ↑Sun Jun 24, 2018 10:38 pmGood 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?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 ?
Thanks.
We can't answer this question. But you can easily just by deselecting all aliases, then the principal name will be displayed.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 13
- Joined: Sun Jun 24, 2018 12:58 am
- OLAP Product: ABM Analytics
- Version: latest
- Excel Version: O365 V1805
Re: Retrieving member name by alias
lotsaram wrote: ↑Mon Jun 25, 2018 7:32 amNo 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.PTSD wrote: ↑Sun Jun 24, 2018 10:38 pmGood 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?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 ?
Thanks.
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.
- gtonkin
- MVP
- Posts: 1202
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Retrieving member name by alias
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
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
-
- Posts: 13
- Joined: Sun Jun 24, 2018 12:58 am
- OLAP Product: ABM Analytics
- Version: latest
- Excel Version: O365 V1805
Re: Retrieving member name by alias
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).
- gtonkin
- MVP
- Posts: 1202
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Retrieving member name by alias
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)
-
- Posts: 13
- Joined: Sun Jun 24, 2018 12:58 am
- OLAP Product: ABM Analytics
- Version: latest
- Excel Version: O365 V1805
Re: Retrieving member name by alias
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. ...
-
- MVP
- Posts: 3657
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Retrieving member name by alias
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.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.