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

PickList to show Alias but store Element Name: Websheet approach

Post by Elessar »

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 25654 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 25654 times
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Wim Gielis
MVP
Posts: 3103
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: Websheet approach

Post by Wim Gielis »

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

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
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: Websheet approach

Post by Elessar »

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

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Post Reply