Bespoke Picklists

Post Reply
20 Ton Squirrel
Posts: 19
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Bespoke Picklists

Post by 20 Ton Squirrel » Tue Mar 30, 2021 6:58 pm

Is it possible to define a picklist for a specific cluster of child elements in a dimension?

For example, here's a basic cube layout:
  • !Region: North America, East Europe, Asia Pacific, etc…
  • !Month: Ya know… a bunch of months.
  • !Measure: Shipments, Revenue, Price, etc…
  • !Adjustment: Output Value, Calculation Flag
The Calculation Flag element is used to fork rule calculations in different directions based on whatever the user puts in. If it is blank, a default rule calculation is used.

I would like to have a picklist in !Adjustment's Calculation Flag based on the element in !Measure.

So…
  • The picklist in Calculation Flag for Shipments might have: A, B, C
  • The picklist in Calculation Flag for Revenue might have: D, E, F
Is this possible with some MDX magic or am I dreaming?
TM1 Version: 11.8.00200.24

20 Ton Squirrel
Posts: 19
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Re: Bespoke Picklists

Post by 20 Ton Squirrel » Tue Mar 30, 2021 7:09 pm

Within a few minutes of posting this I think I found my answer.

Picklists on Control Cubes. The link is here.

I'd love to hear whatever feedback is out there from you pros, however, so feel free to chime in.
TM1 Version: 11.8.00200.24

User avatar
ykud
MVP
Posts: 136
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: Bespoke Picklists

Post by ykud » Wed Mar 31, 2021 3:39 am

I think it's just called }Picklist cubes , definitely something to use in your case.

lotsaram
MVP
Posts: 3481
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Bespoke Picklists

Post by lotsaram » Wed Mar 31, 2021 8:10 am

You can use a rule in the picklist cube (in fact whenever you use a picklist cube it's waaay more likely that you would use string rules versus data entry) and have logic in the rules to look up whatever you want and branch the picklist options based off that.

Using dynamic subsets which are themselves looking up values in parameter cubes and using StrToMember you can get pretty fancy with dynamic picklists but it would be so much easier if IBM would just support MDX expression directly as a picklist source, but as yet they don't. Here's a link to a ER to support MDX as object type for picklist source which I would encourage you to vote for. https://ibm-data-and-ai.ideas.aha.io/ideas/PAOC-I-318
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Post Reply