Subnm index change

Post Reply
mnasra
Posts: 106
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Subnm index change

Post by mnasra » Tue Jul 28, 2020 9:50 pm

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!
Thanks
Micheline

User avatar
Steve Rowe
Site Admin
Posts: 2062
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Subnm index change

Post by Steve Rowe » Wed Jul 29, 2020 7:46 am

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.

User avatar
paulsimon
MVP
Posts: 760
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Subnm index change

Post by paulsimon » Wed Jul 29, 2020 5:29 pm

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

mnasra
Posts: 106
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: Subnm index change

Post by mnasra » Wed Jul 29, 2020 7:48 pm

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.
Thanks
Micheline

User avatar
paulsimon
MVP
Posts: 760
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Subnm index change

Post by paulsimon » Wed Jul 29, 2020 8:42 pm

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

User avatar
macsir
Community Contributor
Posts: 710
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365
Contact:

Re: Subnm index change

Post by macsir » Wed Jul 29, 2020 8:52 pm

In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
gtonkin
MVP
Posts: 807
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Subnm index change

Post by gtonkin » Fri Jul 31, 2020 11:32 am

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

Post Reply