Retrieving member name by alias

Post Reply
PTSD
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

Post 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!
declanr
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

Post by declanr »

A dimix enclosed within a dimnm should work across all interfaces, what interface are you using?
TI, rules, perspectives etc?
Declan Rodger
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
PTSD
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

Post 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.
Attachments
File.PNG
File.PNG (92.35 KiB) Viewed 7199 times
lotsaram
MVP
Posts: 3651
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

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
PTSD
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

Post 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.
User avatar
gtonkin
MVP
Posts: 1192
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

Post 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
PTSD
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

Post 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).
User avatar
gtonkin
MVP
Posts: 1192
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

Post 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)
PTSD
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

Post 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. ...
lotsaram
MVP
Posts: 3651
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

Post 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.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply