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 dropdown 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”. If you then had Rules that are driven off those values, looking for “Jul”, rather than “July”, 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 "subset:Month:nLevel".
Control cubes need to have a slightly different method done to them to make pick lists work against them.
Take for example an “Account” dimension. Against that dimension we have a “Category” attribute defined that allows us to map from an account to a category. We also have a “Category” dimension, which lists the available categories. We want to use Category as a drop down, or pick list, in the Account dimension to limit the selection of categories in the accounts dimension to only those elements in the category dimension.
To make this work we need to do the following:
- Create an attribute on the Account dimension called “Category”.
- Create a dimension called “Category” and a dynamic subset in it called “BaseLevel”, with all the level 0 elements.
- Find to the element attributes control (curly bracket) cube for the target dimension – in this case the Account dimension, so it will be “}ElementAttributes_Chart of Accounts”.
- Right click on this dimension and choose Create Pick List Cube.
- Find the new pick list cube, in our example “}PickList_}ElementAttributes_Account”.
- Create Rules on this picklist cube.
- Enter a rule that is in the syntax:
['AttributeName'] = S: 'subset:targetdimension:targetsubset' ;
- So for our example, it will be:
['Category'] = S: 'subset:Category:BaseLevel' ;
- Save the rules and go back to the control cube and the pick list should be active.
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.