TM1 Forum

Hi Experts,

I have a report where the user will choose an entity SUBNM( 'ENTITY', 'SUBALL' , 1)

the second choice, is to choose a branch from that specific entity. SUBNM ('branch', Sub-chosen-ENTITY, 1)

it works perfectly the first choice only.
Say the user chooses entity 1, and then the 5th branch of entity 1 (the system will keep index 5 as part of the formula)

So, when the user chooses entity 2, instead of the system displaying the FIRST branch of entity 2, it displays, the 5th branch of entity 2.
The users are using TM1WEB.

I dont know if it is a bug or a feature!

You need to take care when using indexes and be clear what it is referring to. In this case SUBNM ('branch', Sub-chosen-ENTITY, 1) will display the 1st element of the subset "Sub-chosen-Entity". Depending on how the subset is ordered this may not be the first child of the sub-entity.

So check how the subsets are set up.

Hi

I think what you are actually trying to do is some sort of cascading selection, so the user looks up the Entity, in the Entity dimension and then looks up Branches related to that Entity in the Branch dimension and picks a Branch. Is that you are trying to do?

The problem that you are seeing is a feature not a bug. When you use the Index version of SUBNM, and you pick the first Branch at position 5, the formula is actually modified to change the index to 5. Therefore when you select the next Entity, the Branch SUBNM will still show index 5. It won't revert back to Index 1. If the second Entity does not have 5 Branches, you may see a blank. It will still be possible to double click and select a Branch from the subset of Branches that you have presumably created for each Entity in the Branch dimension.

You can work around this with VBA. Detect the change of Cell Value on the Entity SUBNM and then reset the formula on the Branch SUBNM to have index 1. I would also take a look at the SUBPICK Macro.

However, if Entity is a parent of Branch then I would question why you have two separate dimensions. The simple thing to do would be to combine both into one dimension so the user just expands Entity to select the Branch that they want. That will be quicker for the user.

Regards

Paul SImon

Thank you Paul and Steve,

and Paul you are absolutely right. This is exactly what I was trying to do and what was happening. I dont think I like the 'feature'. But I, now, understand the why? Lets say, they want to pick element 9, 10, 11 successively, with my way, they will have to always scroll down instead of starting from the 9th element and picking the next one.

thank you for providing the workaround. I am not good in vba but I will do my research.

And no, unfortunately, Entity is not a parent of Branch per say (not a real cascade). Some branches belongs to multiple entities.

Hi

So there is a M:N between Entity and Branch. However, you could still create an Entity_Branch dimension. That would then allow you have a dimension where a Branch could be linked to more than one Entity and you could then use the expand approach.

If you are more familiar with TI than VBA that might be simpler for you.

Regards

Paul Simon

Here are some articles which might be helpful for cascading
https://everanalytics.wordpress.com/201 ... ognos-tm1/
https://www.linkedin.com/pulse/infocat- ... -joe-white

And if the enhancement to be able to drive the picklist via MDX ever gets developed, cascading picklists will be even easier.