Picklists in Planning Analytics are a great way for you to ensure that users only “enter” (or in this case select from a validated list) a value from a standard dropdown list of options. They are the TM1 equivalent of data validation in Excel and are dead easy to setup.
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! Enter the realm of data validation – or in Planning Analytics, pick lists!
The Solution for Dropdowns: Picklists!
To overcome this problem we use Picklists in TM1, or dropdowns as some people call them. These allow us to define a standard list of possible selections, say Jan to Dec, Yes or No, a list of Profit Centres etc, and force the user to select from it.
Equivalent to Excel Data Validation
In Excel, you would typically use a Data Validation and enter or select the list for validation. In TM1, we do exactly the same thing with a picklist. Also, if you have used Data Validation in the past for a TM1 worksheet, you might like to consider changing them to use pick lists as these can be dynamic against your Planning Analytics data, rather than static against an Excel list.
Picklists in TM1
There are two approaches to Planning Analytics pick lists we will deal with in this post:
- Normal – these are setup against a measure in a cube. For example, selecting a period for Current Period in a System Control cube, or a Yes/No flag.
- Attribute – these are defined against an attribute on a dimension. For example, having a Type attribute on an Account dimension that is used to define if an account is a P&L or Balance Sheet account.
Normal Picklists
These pick lists are typically defined against as a measure in a cube. For example, in a System Control cube where we might want to define a Reporting Period, we would use a pick list against a Parameter called Reporting Period and intersect it with a String measure.
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
How to Create Picklists
The creation of a Planning Analytics picklist is the same, no matter which method you choose. All are entries in an attribute on a specific element.
Simple Dimensional Picklist
To create a standard picklist on a dimension, create a new attribute literally called “Picklist” and then enter one of the methods below in the attribute against the relevant element. This method will typically be used in a control or lookup cube. The advantage of this method is that it is very easy.
The disadvantage is that it will apply to all uses of the element in every cube the dimension is used in. You can’t, for example, limit it to just the String measure. As you can see from the screenshot below, it is available on both the String and Value measures, but clearly you can’t actually enter text against a numeric measure, so we get an error when we try to select it per the screenshot. Now that being said, you would only present the String measure in a view or form, so the problem as illustrated is not likely to happen!
Picklist Cube
The Picklist cube is created against a cube and allows you to define the picklist using the dimensions present in the primary cube. For example, if you have a System Control cube with Parameter and Measure dimensions, and want a picklist for Reporting Month, you would use this method to limit the selection to a specific parameter for the String measure only. This method is still easy and allows refinement to a cell where the selection is available.
To create a Picklist cube, just click on the three vertical dots next to the cube, and select “Edit Picklist cube” (regardless if it exists already or not).
Then open the Control Objects/Cubes and find the new }Picklist cube.
Once there you can either enter the Picklist definitions against the Value element from the new }PickList dimension, or, for more granular control, you can write a rule.
The second line above shows the rule in the green cell. This is just entered in the rules against the }Picklist cube:
Picklist Types
There are three types of picklist that can be created in Planning Analytics: static, using all elements from a dimension or using the elements from a subset from a dimension. All methods explained above can use the three types detailed below.
Static Picklist
A static picklist will use a set of standard values, for example a simple Yes or No. To do this enter:
static:option1:option2:option3
For example: static:Jan:Feb:Mar
Dimension Picklist
A dimension picklist will display all elements from a dimension. To create it, say for the Month dimension enter:
dimension:Dimension_Name
For example: dimension:Month
Subset Picklist
If you need greater control about what elements to include (for example you want to use MDX in a subset), you can use a subset, say from the Month dimension with an “nLevel” subset, you would enter:
subset:Dimension_Name:Subset_Name
For example: subset:Month:nLevel
Pick Lists on Attributes (using Control Cubes)
Method for Control Cube Picklists in Planning Analytics
Control cubes need to have a slightly different method done to them to make pick lists work against them.
The method involves the following:
- Create an attribute on the primary dimension and give it a name that means something (probably the name of the dimension you want to use as a picklist!).
- If you don’t have one already, create the dimension you want to use for the values in the picklist.
- Locate the Control Cube for the attributes of the primary dimension.
- Create a Picklist cube for this control cube.
- Locate the Picklist control cube created on the previous step.
- Create rules on the Picklist control cube.
- Edit the rules to define how you want the picklist to work. This is in the form [‘AttributeName’] = S: ‘subset:targetdimension:targetsubset’ ;
- Save the rules and you’re finished!
Example of a Picklist over a Control Cube
Take for example an “Customer” dimension. Against that dimension we have a “Location” attribute defined that allows us to map from a customer to a location. We also have a “Location” dimension, which lists the available categories. We want to use Location as a drop down, or pick list, in the Customer dimension to limit the selection of locations in the customer dimension to only those elements in the location dimension.
We want it to look like this:
To make this work we need to do the following:
- Create an attribute on the Customer dimension called “Location”.
- Create a dimension called “Location” and a dynamic subset in it called “BaseLevel”, with all the level 0 elements.
- Locate the element attributes control (curly bracket) cube (not dimension) for the target dimension. So the control cube for the Customer dimension will be “}ElementAttributes_Customer”.
- Right click on this cube and choose Edit Pick List Cube. This will create the picklist cube if it doesn’t exist, or edit if it does and it will open.
- Locate the new pick list cube, in our example “}PickList_}ElementAttributes_Customer”.
- 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:
['Location'] = S: 'subset:Location: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.
Security
Finally, make sure your Planning Analytics 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.
IBM’s Notes on Picklists
Here is a link to IBM’s notes on picklists. I don’t think there is much added by them, but it can be useful to have the link for you anyway!
Do You Want to Use Picklists, but Can’t Make them Work?
If you need any help with Picklists, even if it is just a quick question, hit us up on the form below.
