TM1 Workbook Dynamic drop down list

[Mems]

Hey,

I am using Tm1 10.1.1 Excel 2010

I was hoping to get some assistance.
I have a 4 tier Cost Element hierarchy dimension.

I have a created a planning input sheet that the user log in to to do planning.

Lev 3 All Cost Element
Lev 2 Cost Element Activity
Lev 1 Cost Element Function
Lev 0 Specific Cost Element.

On a 'Parameter' sheet in the workbook the user chooses the CE Activity.

On the input sheet this dynamically generates a drop down list for CE function using elcomp and index.

Now I want to generate a dynamic drop down list from function to populate Specific CE.

It seems like it should be possible, but have spend 3 days on it and could not find a solution any advice would be greatly appreciated.

To put it easier cell b4 should have a dynamic drop down list off the children of cell b3.

Thank You

[declanr]

To put it easier cell b4 should have a dynamic drop down list off the children of cell b3.

Just create a picklist cube and create a rule on the picklist cube that references what has been typed against the other element in the actual cube.

[PlanningDev]

There is a way to do this all in excel. You need to get familiar with the offset formula and using hidden sheets with active forms. You can use an excel drop down list that has a dynamic range as specified by your offset formula that references the hidden active forms. Users will pick the function which will drive the mdx for the hidden active form. This in turn populates the drop down list with filtered values based on your selection. It takes a little work but it's doable. The upside to this approach is you don't need a picklist cube which can contain a value for every user, or cost center, etc. The downside is performance can take a hit and its a little complicated to get going.

[lotsaram]

There is a way to do this all in excel. You need to get familiar with the offset formula and using hidden sheets with active forms. You can use an excel drop down list that has a dynamic range as specified by your offset formula that references the hidden active forms. Users will pick the function which will drive the mdx for the hidden active form. This in turn populates the drop down list with filtered values based on your selection. It takes a little work but it's doable. The upside to this approach is you don't need a picklist cube which can contain a value for every user, or cost center, etc. The downside is performance can take a hit and its a little complicated to get going.

I would prefer as Declan suggested to have subsets in the cost element dimension for
1/ all activities
2/ all functions per activity
3/ all cost elements per function
and then a string parameter input cube with cascading input for activity > function > cost element where input would be driven by picklist cube.
It could possibly amount to a few thousand subsets depending on the size of the cost element dimension but no big deal to have this all automatically maintained via nightly TI.

Even for a relative planning novice the picklist rule is a simple lookup based on naming convention and much easier to implement than dynamic excel named ranges. Note that if this needed to be dynamic in Excel then the hidden active form would need to be refreshed on each selection so some VBA would be needed with the OnChange event which would rule out the solution working in web (I believe having a hidden sheet would also not work in web since sheets only get calculated in web when active).

All round I'd say the picklist solution is better on the criteria of ease of implementing, performance and able to work in any native TM1 interface. So wins hands down.

[ardi]

We do this by using SUNM formulas with MDX subsets populating them. We have created a 2-dimensional cube called User_Selections ( }Clients and User_Selections_m ). In the dimension User_Selections_m we have one element for each SUBNM Selection that we want to use to filter the next level elements ( for examle Level4_Selection, Level3_Selection, Level2_Selection, Level1_Selection, Level0_Selection). Then in dimension we have one MDX for each Level, and in Level3_MDX subset we show only children on element Level4_Selection and so on

[Mems]

Thank you all for replying...

I did manage to figure it out.

@Planningdev I ended up using a excel driven, active form/hidden sheet/offset formula.But it has to work in web. So I created a massive lookup sheet.
Dynamic subset with all functions as my rows and coulmns with headings 1 -100. I proceeded to use a Elcomp formula. Which linking the number and the function to show the the children.
I then used elcompn to get the number of children. This was all done in the hidden sheet.
In the 'Name formula' I used a match formula to find the CE function in the hidden sheet and a vlookup to find the amount of children. I combined these into a Offset formula.
It worked - at a snails pace but the dimension is quite big around 5k leaf elements.

As you can imagine it was\is way to slow (at least it was a working version).
I will try the picklist suggestion, that sounds like a great option thanks for the advice.

Once again thank you for your feedback/advice

Stay classy

[Mems]

For anyone reading this who wants to add similar functionality.

I added a measures with the name Cost Activity and Cost Function
I added a picklist cube to the cube. with the following two rules

Code: Select all

Cost Function = S:"Subset:Cost_Element"|CellGetS Cost Activity
Cost Element= S:"Subset:Cost_Element"|CellGetS Cost Function

I created a process that creates an MDX subset for each activity(a list of all functions for that activity) and each function(a list of all elements for that fuction).
I chose a dynamic subset because subsets will also be in sync and only when a new function is added does the process kick off.
The worksheet has a dropdown list for Activity - which is static but can be dynamically modified (activities are fixed for the company)
Once activity is chosen the picklist cube creates a subset for that activity showing all functions for that activity.
When a function is chosen it dynamically creates a list fot element for that function.

Performance is way better than expeted.

Thank you all for the advice if anyone needs assistance on the setup of this please drop me a mail and I will gladly assisit.