Cascading Picklists

Post Reply
aa9
Posts: 4
Joined: Thu Jul 08, 2010 7:53 pm
OLAP Product: TM1
Version: 9.5.1 and 9.5.2
Excel Version: 2003 and 2007

Cascading Picklists

Post by aa9 »

Is it possible to "link" two (or more) picklists together? I have an equipment planning cube that requires the input of products in a measures dimension. To do so, my client wants to select a Product Line in picklist 1 and then see a filtered lists of Products in picklist 2 based on their selection in picklist 1.

I wasn't able to find anything on this and I'm beginning to wonder if it's even possible.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Cascading Picklists

Post by Steve Rowe »

You should be able to do this using pick list cube rules.

The details of how to create a picklist cube and the way the rules look are all in the help for picklist.

I'd so something like the following.

Save a subsets containing the lists that are for each product (alternative put the elements in their own dimension, may be more secure). Say the subset for ProductA is called SubProductA.

Your rule is then something like this

[]=S: If ( DB (Ref to where product is picked)@='ProductA', 'subset : SubProductA' ,
ElseIF (DB (Ref to where product is picked)@='ProductB', 'subset : SubProductB' ,

etc);

To make the rule more concise and flexible you could but the subset as an attribute of the product, then your rule is like this.
EDIT : Corrected Attrs reference
[]=S: 'subset:' Attrs ('Product' , DB (Ref to where product is picked), 'SubsetName');

Hope the above is enough to give you a clue, alot of the syntax above is probably wrong though so you won't be able to just copy it exactly.

Cheers,
Technical Director
www.infocat.co.uk
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Cascading Picklists

Post by lotsaram »

Steve Rowe wrote:You should be able to do this using pick list cube rules.
....
Your rule is then something like this

[]=S: If ( DB (Ref to where product is picked)@='ProductA', 'subset : SubProductA' ,
ElseIF (DB (Ref to where product is picked)@='ProductB', 'subset : SubProductB' ,

etc);

To make the rule more concise and flexible you could but the subset as an attribute of the product, then your rule is like this.

[]=S: 'subset:' Attrs ('Product' , !Product , 'SubsetName');
Worthwhile putting in tips & tricks?
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Cascading Picklists

Post by Steve Rowe »

Maybe.....
If someone can verify the approach works?
I just made it up, never actually tried it......
Cheers,
Technical Director
www.infocat.co.uk
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Cascading Picklists

Post by rmackenzie »

Steve Rowe wrote:If someone can verify the approach works?
I just made it up, never actually tried it......
Steve, I did something very similar and it worked - dynamically switching the picklists per pre-defined subsets. This was in 9.5.0.

The only niggle (and ultimately deal-breaker) was that when using this approach there is no way to 'blank' out a selection made in error unless your dimension has a '.' or a '_' in there for that very purpose.
Robin Mackenzie
WouterM
Posts: 6
Joined: Mon Aug 30, 2010 9:44 am
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013
Location: Romania

Re: Cascading Picklists

Post by WouterM »

I had a similar problem and I solved it with the following construction:

Input:
A dimension '210 Fin Account' with a single top level consolidation 'All Accounts' below that a hierarchy with 3 levels. The dimension contains a simplified accounting structure (accounting codes + labels) of about 50 lowest level elements.

In an entry cube I wanted 3 columns each with a pick-list. In column 1 you want to select the first level code (1, 2, 3, 4, ...) in the second column you should only be able to select the children depending on your selection in the first column (for 1; 1x, for 2; 2x, etc. The same for the third column.

After realizing that this would not be possible using rules and MDX (??) I reverted to the TI that updates the dimension. I basically added code there to create from every dimension element a subset (so I also have subsets for the lowest levels, which I don't use). Those subsets are then used in the rules of a picklist cube.

The code in the TI:

Code: Select all

#Create for each element of a dimension a subset with its children, the name of the subset is equal to the (parent) dimension element

#Variables

vDimension = '210 Fin Account';
vLoop = 1;
vLoopStop = DIMSIZ('210 Fin Account');

#Loop for all elements in the dimension
WHILE (vLoop <= vLoopStop );

#Subset name is equal to the parent dimension item name alias
vSubsetName=  ATTRS(vDimension, DIMNM(vDimension,vLoop), 'Display Label');

#Subset definition is equal to the children of the parent dimension item name
vMDXExpression= '{[210 Fin Account].['|DIMNM(vDimension,vLoop)|'].Children}';

#Check if the subset exists, if yes, delete the subset
IF (SubsetExists(vDimension, vSubsetName) = 1) ; 
SubsetDestroy(vDimension, vSubsetName);
EndIF;

#Create the subset (lowest level elements will generate an empty subset)
SubsetCreatebyMDX(vSubsetName, vMDXExpression, vDimension);

#Make subset static by adding and deleting a dummy accounting code
SubsetElementInsert( vDimension, vSubsetName, '99999',1);
SubsetElementDelete( vDimension, vSubsetName, 1);

#Replace the subset name (accounting code) with the alias (accounting code + label)
SubsetAliasSet( vDimension, vSubsetName, 'Display Label');

vLoop = vLoop + 1 ;

END;
The rule in the picklist cube:
(L1 is picklist column 1, etc)

Code: Select all

['L1']  = S: 'subset:210 Fin Account:All Accounts';
['L2']  = S: 'subset:210 Fin Account:'|DB('Cube Name', !Dim1, !Dim2, 'L1');
['L3']  = S: 'subset:210 Fin Account:'|DB('Cube Name', !Dim1, !Dim2, 'L2');
This works fine, it just creates a bit of a mess with all these visible subsets. If I could figure out a neat way to define the current dimension level of an item in the loop I could get rid of all the empty subsets for the leaf elements. It has been a while since I was doing some TM1, and to my feeling this is a bit too much of a workaround for something that seems like a simple thing to be able to do, so comments to improve this are welcome.
Post Reply