Cascading Picklists in TM1 – How to Create in 4 Steps
Have you ever wanted a user to select one item from a picklist (or dropdown) and then have a subsequent picklist update to only show valid selections based upon what was chosen in the first picklist? Cascading picklists are the answer. For example, you might have a dimension with a Business Unit level and beneath that, Cost Centres. You want users to first choose a Business Unit and then choose a Cost Centre from the children of the Business Unit. In other words, you have cascading dropdown built right inside TM1.
Use Case for Cascading Picklists
We had a need to create cascading pick lists recently for a client. The situation was that we needed a form that allowed the selection of a source and target Program and then if desired, a source and target Profit Centres within a Program.
In the screenshots below you can see that if PP0544 is chosen as a source Program, that the Profit Centres displayed are are one set. However if I choose PP0672, then the Profit Centres are different. Cascading dropdown are used to create this.
How to Create Cascading Picklists
Creating Cascading Picklists in TM1
- Define Subsets for the First Level of the Cascade
The first step is to define a set of subsets. We created a subset for the Programs and called the subset “Program List”.
- Create Subsets for the Second Level of the Cascade
Then we needed subsets for each Program that included the Profit Centres that are directly children of the parent. This would be created by TI normally, but because we were short of time, we just created them manually. So we ended up with about 15 manually created subsets, one for each Program.
Note that we named them all with the syntax “zPC-ProgramCode”, so “zPC-PP0544” for example. This is important because we will later create rules in a Picklist cube that use that syntax to create the cascade.
Also note that all these subsets need to be dynamic so that when new children are added, they automatically get included in the set.
- Create Picklist Cube
This is where it gets a little bit clever. We need to create a Picklist cube against the cube we want the picklists to appear. In our case, that cube is called “Program Allocation Definition”. When the picklist cube is created, it adds an additional dimension called }Picklist to the cube. The contents of this cube are then used to populate the picklists in the primary cube.
To create a Picklist cube, just right click on the primary cube and select “Edit Picklist cube”. It will then appear in you Control Objects list in PAW.
- Add Rules to Picklist Cube
The final step is to add rules to the Picklist cube. In our instance we added rules that said the following:
['Source Program'] = S: 'subset:Cost Centre:Program List';The first two rules here refer to the Program List subset from step 1.
['Target Program'] = S: 'subset:Cost Centre:Program List';
['Source Node'] = S: 'subset:Cost Centre:zPC-' | DB('Program Allocation Definition',!Year,!Scenario,!Allocation List,'Source Program') ;
['Target Node'] = S: 'subset:Cost Centre:zPC-' | DB('Program Allocation Definition',!Year,!Scenario,!Allocation List,'Target Program') ;
The second two rules construct the name of the required Profit Centre list from the group of subsets created in step 2 by using the syntax ‘zPC-‘ | DB(‘Program Allocation Definition’,!Year,!Scenario,!Allocation List,’Target Program’). This concatenates the string ‘zPC-‘ with the name of the Target Program chosen to create a string that would be read by the rule as ‘zPC-P0544’ and thus will return the contents of that subset for the cascading picklists.
More Information
If you would like to know more about Picklists, how to create them in a normal situation, for example, please check out this post. Or reach out to us at ExplroingTM1 and we’ll help you along the way. Picklists, dropdown, or whatever you want to call them are not difficult in TM1 and create a much more rigorous model.
Exploring TM1 – Contact Us
