PickList to show Alias but store Element Name

Suggest and discuss enhancements for TM1
Post Reply
User avatar
paulsimon
MVP
Posts: 808
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 »

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: 351
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 »

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: 3651
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 »

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 »

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: 3651
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 »

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: 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: PickList to show Alias but store Element Name

Post by gtonkin »

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: 2
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 »

Try: subset:DIMENSION:SUBSET<StoreAlias=F> as picklist definition.
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: PickList to show Alias but store Element Name

Post by Adam »

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: 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: PickList to show Alias but store Element Name

Post by Wim Gielis »

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

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
Adam
Posts: 94
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Microsoft 365 x64

Re: PickList to show Alias but store Element Name

Post by Adam »

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: 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: PickList to show Alias but store Element Name

Post by gtonkin »

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: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: PickList to show Alias but store Element Name

Post by Elessar »

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

This should be copied to useful code, tips & tricks forum
Last edited by Elessar on Tue Mar 05, 2024 1:19 pm, edited 1 time in total.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
User avatar
ykud
MVP
Posts: 148
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: PickList to show Alias but store Element Name

Post by ykud »

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: 3651
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 »

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.
User avatar
scrumthing
Posts: 81
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: PickList to show Alias but store Element Name

Post by scrumthing »

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: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: PickList to show Alias but store Element Name

Post by Elessar »

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

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
EP_explorer
Regular Participant
Posts: 200
Joined: Sat Dec 04, 2010 2:35 pm
OLAP Product: PAL
Version: 2.0.9
Excel Version: 2016

Re: PickList to show Alias but store Element Name

Post by EP_explorer »

ppniederbracht wrote: Fri Feb 05, 2021 9:37 pm Try: subset:DIMENSION:SUBSET<StoreAlias=F> as picklist definition.
Found this way in TM1 quiz, tried, but don't understand delight

i.e. user put alias name from PickList, after it refresh and see code instead of alias name
and ask you - where is my name which i have inputed?

if user see alias name and this alias name changing after changing in attribute (based on saved code in cube) - it would be delight.

May be I do something wrong or don't understand something?
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: PickList to show Alias but store Element Name

Post by gtonkin »

The joy manifests when someone updates the master data rendering the values you stored in the cube invalid requiring you to find and fix them.
e.g. 1234-Desciption vs 1234-Description
A simple correction to spelling will cause untold issues where the value in the picklist is relied on elsewhere in the model.

Saving back as the principal name avoids this.
You could always add in a column to show the alias where needed.
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: PickList to show Alias but store Element Name

Post by Elessar »

gtonkin wrote: Tue Mar 05, 2024 12:36 pm 1234-Desciption vs 1234-Description
It took about 2 minutes for me to try to find difference and give up. I've asked Excel and it said that names are really different. Only after I've decomposed them to letters I've found this "r" :)
I'm getting old.

Regarding EP_explorer's question: Yes, it's not a panacea. And there is dangerous thing here having the fact that this is not documented. So IBM can change or remove it, and it will stop working. But agree with George, leaving things "as is" here is more dangerous.
My solution with DBSS is better ;)
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
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: PickList to show Alias but store Element Name

Post by gtonkin »

That missing "r" is literally something I encountered previously and no one could work out why the rule had broken.
Previously input selection was incorrectly spelled and then corrected causing lots of head scratching as everyone's auto-correct just looked right over the issue. So now we store principal names...
Post Reply