PickList to show Alias but store Element Name

Suggest and discuss enhancements for TM1
Post Reply
User avatar
paulsimon
MVP
Posts: 790
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

PickList to show Alias but store Element Name

Post by paulsimon » Thu Feb 12, 2015 10:13 pm

Hi

I tried submitting this as an RFE but IBM's site did something to prevent pasting text so after trying IE and Chrome and getting the same problem I gave up.

I believe that there should be an option when specifying a TM1 PickList to make it return the Element Name when the developer has specified the list of valid values as a Subset with an Alias.

The PickList is very useful for allowing a user to pick from a list of valid values. However, most dimensions have a Code as the Element Name, eg Account Number 10001, and then an Alias which may be the Description, eg 10001 - Sales excluding Tax.

An Account dimension will typically have hundreds if not thousands of codes. Setting up the Picklist Subset to display the list of Codes ie the Element Names, without the description will not be much use as the user probably doesn't remember what a 1000 different codes mean. Therefore the developer will probably draw the PickList from a Subset with the Description Alias.

The problem that this then creates is two fold:

a) What will get stored in the cell will be the Description rather than the Element Name, eg 10001 - Sales excluding Tax. If the Description for Account Number 10001 changes, then the value in the cell will be invalid.

b) Storing the code and description rather than just the code (element name) takes up more space.

The ability to make the PickList return the Element Name into the Cube Cell but to display a list from a Subset showing an Alias would make the PickList far more useable for real world applications.

If a developer wanted to display the Description then they could organise this by adding a measure alongside the one holding the code, and use a simple rule such as ['Explanation'] = S: attrs( 'MyDim', DB( Cube, ... , 'Code'),'Description') ;
That is relatively easy to do, and the key thing then, is that if the Description changes, then it will automatically show the latest Description and the system will still work, because the underlying code ie the Element Name was stored.

Regards

Paul Simon

Andy Key
MVP
Posts: 347
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: PickList to show Alias but store Element Name

Post by Andy Key » Fri Feb 13, 2015 7:04 am

Let me know if you ever do get this into the system as I'd certainly vote for it.

It's something that they already do via the Metrics setup interface in PM, but would be useful in standard picklists.
Andy Key

lotsaram
MVP
Posts: 3489
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: PickList to show Alias but store Element Name

Post by lotsaram » Fri Feb 13, 2015 11:28 am

I would also definitely vote for it. I usually have an additional measure in the background to get the principal name but it would be great for this workaround to be able to go away.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
Glass
Posts: 5
Joined: Fri Aug 28, 2015 4:13 pm
OLAP Product: TM1
Version: 1022
Excel Version: 2013

Re: PickList to show Alias but store Element Name

Post by Glass » Tue Nov 29, 2016 12:12 pm

I know this one has been lingering for a while, but having been bitten by it recently I'm giving it a +1000.

Picklist should be able to show alias for user-friendliness, but store the principal name for developer sanity. I recently had a project where they changed their naming convention for project names and applied it retrospectively, so any stored references to the old aliases died.

Required a fairly substantive exercise to ensure all TIs converted elements to their principal name (although I'm guessing from a best practice perspective I should have already done this - self slap on the wrist), and updating reports which stored user selections via DBSS to do a DIMNM(DIMIX) conversion to store the principal rather than the alias.

Having a display alias but storing the principal would be a great addition, particularly when you don't have control over when the alias may change on a whim.
============================================
"You rush a miracle man, you get rotten miracles."
- Miracle Max

lotsaram
MVP
Posts: 3489
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: PickList to show Alias but store Element Name

Post by lotsaram » Tue Nov 29, 2016 12:37 pm

Pretty sure I saw this one on a fix or new feature list. Not sure if it was for PAL or 10.2.2 FP6.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
gtonkin
MVP
Posts: 910
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PickList to show Alias but store Element Name

Post by gtonkin » Wed Nov 30, 2016 5:03 am

Just checked on the RFE that I logged based on this related thread - there was an update on 9 Nov 2016, but still open. I have not had a chance to play with 10.2.2. FP6 so cannot confirm if they have addressed but not communicated this functionality.

ppniederbracht
Posts: 1
Joined: Wed Nov 28, 2018 1:10 pm
OLAP Product: TM1 / PA
Version: Latest
Excel Version: Office 365

Re: PickList to show Alias but store Element Name

Post by ppniederbracht » Fri Feb 05, 2021 9:37 pm

Try: subset:DIMENSION:SUBSET<StoreAlias=F> as picklist definition.

Adam
Posts: 47
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.4
Excel Version: Office 365 x32

Re: PickList to show Alias but store Element Name

Post by Adam » Fri Feb 05, 2021 10:14 pm

ppniederbracht wrote:
Fri Feb 05, 2021 9:37 pm
Try: subset:DIMENSION:SUBSET<StoreAlias=F> as picklist definition.
This does actually work. Did a search and it's not in the IBM documentation, and Google search shows a single result from 2016. Thanks for the tip.
Take care.
Adam

Wim Gielis
MVP
Posts: 2684
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PickList to show Alias but store Element Name

Post by Wim Gielis » Fri Feb 05, 2021 10:36 pm

ppniederbracht wrote:
Fri Feb 05, 2021 9:37 pm
Try: subset:DIMENSION:SUBSET<StoreAlias=F> as picklist definition.
Thanks, nice first post I would say :-) This will be new information to many of us.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Adam
Posts: 47
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.4
Excel Version: Office 365 x32

Re: PickList to show Alias but store Element Name

Post by Adam » Fri Feb 05, 2021 11:00 pm

Wim Gielis wrote:
Fri Feb 05, 2021 10:36 pm
ppniederbracht wrote:
Fri Feb 05, 2021 9:37 pm
Try: subset:DIMENSION:SUBSET<StoreAlias=F> as picklist definition.
Thanks, nice first post I would say :-) This will be new information to many of us.
I've raised a support ticket w/ IBM to see why this is undocumented.
Take care.
Adam

User avatar
gtonkin
MVP
Posts: 910
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PickList to show Alias but store Element Name

Post by gtonkin » Sat Feb 06, 2021 6:55 am

ppniederbracht wrote:
Fri Feb 05, 2021 9:37 pm
Try: subset:DIMENSION:SUBSET<StoreAlias=F> as picklist definition.
Thanks Paul Peter - great tip!!

Does not look like there are any other modifiers for the PicklistParser.

User avatar
Elessar
Community Contributor
Posts: 246
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

Re: PickList to show Alias but store Element Name

Post by Elessar » Sat Feb 06, 2021 7:59 am

This! Really! Works! Thank you! How did you know about it?

This should me copied to useful code, tips & tricks forum
Best regards, Alexander Dvoynev

User avatar
ykud
MVP
Posts: 138
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: PickList to show Alias but store Element Name

Post by ykud » Sun Feb 07, 2021 9:26 pm

The best 'new' feature of 2020 from my point of view!
Amazing how is it not documented, I can only find one post mentioning it was added in 10.2.2 FP5.

lotsaram
MVP
Posts: 3489
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: PickList to show Alias but store Element Name

Post by lotsaram » Mon Feb 08, 2021 7:43 am

I would also really like to know how Paul knew about this!

I remember seeing it listed as a new feature in the 10.2.2 FPsomething release notes. But as there was nothing documented on how to use it I figured it was a documentation error, maybe somethgin they planned but never got to. And then never followed up to see if it worked or how it worked. But this is a great feature! We can shelve current workarounds like additinal helper measures and TIs to replace alias values with principal names.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

scrumthing
Posts: 71
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: PickList to show Alias but store Element Name

Post by scrumthing » Mon Feb 08, 2021 9:30 am

Very useful function. The only thing I found so far is that it has problems if you have a numeric value (i.e. 1 & 0) as element names and strings as alias (i.e. TRUE & FALSE). Those values cannot be written via that hidden feature. It seems TM1 is writing the value and afterwards changing it.
Maybe that is why it is undocumented.
There is no OLAP database besides TM1!

User avatar
Elessar
Community Contributor
Posts: 246
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

Re: PickList to show Alias but store Element Name

Post by Elessar » Mon Feb 08, 2021 4:11 pm

Described another way to do this here
Best regards, Alexander Dvoynev

Post Reply