Picklists in TM1 are a great way for you to ensure that users only “enter” (or in this case select) a value from a standard list of options. For example, you might want a user to enter a three character month like Jan, Feb, Mar etc. You could design your cube to have a text element and then ask users to enter periods in that format. This will work perfectly until someone enters “July” rather than “Jul”. It you then have Rules that are driven off those values, then you’re screwed!
The Solution for Dropdowns: Picklists!
To overcome this problem we use Pick lists, or dropdowns as some people call them. These allow us to define a standard list of possible selections, say Jan to Dec, and force the user to select from it.
Methods of using Pick lists
There are a few methods to choose from to create dropdowns in TM1, namely:
- Static Picklist – hard coded values
- Dimension Picklist – all values from a dimension are available in the picklist
- Subset Picklist – only elements in a subset are available in the picklist
Examples of Picklists to Create in cell Drop Downs
The creation of a Picklist is the same, no matter which method you choose. All are entries in an attribute on a specific element. What is entered though, is different:
- Static: enter “static:option1:option2:option3”, eg “static:Jan:Feb:Mar”
- Dimension: define a dimension for use, say the Month dimension and then enter “dimension:Dimension_Name”, eg “dimension:Month”.
- Subset: define a dimension for use and add a subset, say the Month dimension with an “nLevel” subset and then enter “subset:Dimension_Name:Subset_Name”, eg “dimension:Month:nLevel”.
Why would you use each method?
That’s easy. If it a list that will never change, like months of the year, just go with Static. If it might change and your are ok with all elements being available for selection, go with Dimension. If you want a limited group, use the Subset option.
If your list will be dynamic, use a Subset with MDX in it.
Further, if you want to display an Alias rather than the element ID in the pick list, then use a Subset and enable the Alias in the subset.
Use of Null (or Blank) Elements
In TM1 we can’t have an element that is blank. So how do we make a blank option available in a pick list?
Well for the static option, it’s easy. Just insert an additional : before the first option. So in our example above, we would enter “static::Jan:Feb:Mar”.
For dimension or subset based pick lists it is harder. Here we need to create an element with something like “—-” entered and then manage the use of it with downstream rules or TI statements.
Updating Picklist Dimensions
If your TI that updates the source dimension for your picklist deletes and recreates, rather than appends, you could run into data problems. Just be aware of it.
Finally, make sure your users can see the dimension that is used for the pick list values! Otherwise (assuming they have rights to the cube) the report or view will display but the dropdown will not display.