PickList to show Alias but store Element Name: Websheet approach

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Elessar
Community Contributor
Posts: 251
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

PickList to show Alias but store Element Name: Websheet approach

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

There is a great but undocumented solution In this topic, which did not suit one of my customers: they do not want to see huge GUID-like codes in their interface, even with the rule-based "name" measure near them. So I found another way to do this:

Assume we have:
  • Cube "test picklist" with 2 dimensions: list and measures.
  • Measures dimension with 2 elements: "Input measure" with "subset:dim:sub" picklist in it; and "Code from picklist", where we will put the element name and which we will use in rules/processes/etc
  • Dimension "Test dim", used for the picklist. Has "Name" alias used in subset, and "Code" attribute = element name.
Cube for picklist.png
Cube for picklist.png (28.24 KiB) Viewed 2780 times


The websheet will look like this:
  • Input measure - the only visible to user. Users select values using picklist here. =DBRW($B$12;$B17;C$16)
  • Code calc - used to calculate the code of value, selected in "Input measure". Blank is nothing is selected. =IFERROR(DBRA("Demo:Test dim"; C17; "Code"); "")
  • Code from picklist - used to send code to TM1. =IF(OR(E17 <> ""; C17 = ""); DBSS(E17; $B$12;$B17;D$16); "")
    E17 <> "" is used to prevent sending a blank code when the alias changes.
    C17 = "" is used to send a blank code when the "Input measure" value is deleted
The attachment Cube for picklist.png is no longer available
Attachments
Websheet.png
Websheet.png (6.74 KiB) Viewed 2780 times
Best regards, Alexander Dvoynev

Wim Gielis
MVP
Posts: 2712
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: Websheet approach

Post by Wim Gielis » Mon Feb 08, 2021 4:23 pm

Hi Alexander,

Maybe I am missing something here, but can't you just use a rule to retrieve the code name (which could be on a hidden measure) ?
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

User avatar
Elessar
Community Contributor
Posts: 251
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: Websheet approach

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

Hi Wim,

If you are about the whole idea, the problem is to select aliases and store the codes, not aliases; so that when the alias changes, the rule using this codes will still work correctly. This is described by Paul Simon in detail in this topic

If you are about calculation of "Code calc" column: Yep, just another approach (and I think yours is better). I was in Excel, and it was easier to add a column with Worksheet functions rather than go to Architect, create an element, write a rule...
Best regards, Alexander Dvoynev

Post Reply