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.