Picking attribute values instead of element name in picklist

[Jodha]

hi,
i am trying to place attribute values instead of dimension element in picklist drop down and use this picklist in excel slice i tried to achieve this by writing rule to picklist control cube

['Current Forecast Month'] = S:'Dimension:ATTRS('Versions',!Versions,'Last Actual Month')';

this rule is throwing error do i need to follow other approach?
please advice me

[declanr]

"Pick List Types
You can create three types of pick lists: static, subset, and dimension." - Developer Guide

What are you trying to do? - (Whatever it is - that wont work.)

[Jodha]

thanks for your fast replay
what i am trying is to display attribute values instead of dimension elements in Picklist drop down for selected cube intersection
please advice me best approach to achieve this

[declanr]

What do you mean by the attribute values?

From:

ATTRS('Versions',!Versions,'Last Actual Month')';

I would assume that you are trying to pull the attribute of 'Last Actual Month' for the version, in that case your picklist would only contain 1 value... if it is only containing 1 value why have a picklist?

Perhaps a detailed explanation of the dims/cubes/attributes/picklists... i.e. just any detail at all

[Jodha]

Thanks for the replay i achieved this by using excel pick list function

[TM1Learner]

Hi ,

I have similar requirement where in I want to show user in excel perspective a picklist having all the attribute values. Can you please advise how you did this please.

[declanr]

You could create a TI process that creates a dimension (called zPickList or something) by looking at the attributes for the dimension and creating an element for each.

Then just use that dimension as a dimension picklist.

If you are likely to get new attributes on occasion just set the TI to run every night as part of a chore.

[TM1Learner]

Thanks sir...and what do i do if i want to filter corresponding elements appearing in excel perspecitves...

I have a reuqirement to have an attribute called employee manager, based on which a drop down should appear in perspectives. Selecting a particular employee manager would need to filter and refine the employees appearing in the view in excel perspectives..Have seen this provided as direct functionality in other CPM tools but not in TM1...

Any more insight into this please let me know..otherwise your reply was very much needed and very helpful..thanks a lott..

[declanr]

I am a little confused at to what you are asking.

Do you just want to be able to choose a manager from a drop down box at the top of an excel sheet and then have all the employees who work for that manager appear in the rows?

If so you simply need to use an active form and you could either have a TI run through the attributes and create the Manager as a consolidation with the employees as children or you could create a subset for each manager and have the active form pick that up instead.

[lotsaram]

Thanks sir...and what do i do if i want to filter corresponding elements appearing in excel perspecitves...

I have a reuqirement to have an attribute called employee manager, based on which a drop down should appear in perspectives. Selecting a particular employee manager would need to filter and refine the employees appearing in the view in excel perspectives..Have seen this provided as direct functionality in other CPM tools but not in TM1...

Any more insight into this please let me know..otherwise your reply was very much needed and very helpful..thanks a lott..

This looks like a cross-post. http://www.tm1forum.com/viewtopic.php?f=3&t=7737#p34131
As I said there you can base the filtered rowset directly on the attribute value with MDX. While you could build subsets based on the attribute values you don't have to. Building consolidations in a dimension like employee for managers would however be extremely sensible.

[TM1Learner]

Thanks I followed what you guys have suggested and managed to make that working ..thanks for that..

I have created a excel pick list and populated that with certain static attribute values and have used TM1RPTROW and TM1RPTVIEW functions to pass the dynamic MDX based on the parameter and select only chosen elements.

Next thing I want is to populate the static pick list dynamically with attribute values from the cube itself rather then specifying it in a static way in excel.

One more thing is when I filter elements based on attribute values the consolidation above those elements still remain same on all elements, despite on me applying a filter. Any thoughts on this??..Even if I write an mdx in subset editor filtering some elements, the consolidations always has whole list of complete elements (filtered and unfiltered)...so total will always be sum of all elements..not just the filtered ones...

I do agree though that best way is to create subsets based on attribute values, as suggested by you guys..

Thanks Again..

[tomok]

One more thing is when I filter elements based on attribute values the consolidation above those elements still remain same on all elements, despite on me applying a filter. Any thoughts on this??..Even if I write an mdx in subset editor filtering some elements, the consolidations always has whole list of complete elements (filtered and unfiltered)...so total will always be sum of all elements..not just the filtered ones...

That is the proper behavior in TM1. Subsets are nothing more than a filtered list of elements from a dimension, they can't change how any element in a dimension is calculated. For example, let's say you have the following a list of accounts and then a rollup called "Total Assets". You create a filtered subset of only the Premises accounts and show those, along with the rollup Total Assets. The amount shown for Total Assets will still include the values for all accounts, not just those from the Premises accounts because that's not how TM1 works. Subsets cannot change a consolidation.

[lotsaram]

One more thing is when I filter elements based on attribute values the consolidation above those elements still remain same on all elements, despite on me applying a filter. Any thoughts on this??..Even if I write an mdx in subset editor filtering some elements, the consolidations always has whole list of complete elements (filtered and unfiltered)...so total will always be sum of all elements..not just the filtered ones...

That is the proper behavior in TM1. Subsets are nothing more than a filtered list of elements from a dimension, they can't change how any element in a dimension is calculated. For example, let's say you have the following a list of accounts and then a rollup called "Total Assets". You create a filtered subset of only the Premises accounts and show those, along with the rollup Total Assets. The amount shown for Total Assets will still include the values for all accounts, not just those from the Premises accounts because that's not how TM1 works. Subsets cannot change a consolidation.

Paul posted a good explanation here http://www.tm1forum.com/viewtopic.php?f ... 053#p34053
I agree that TM1's approach is the correct one. The value of a dimension member is the value. Displayed values should not change on context of what the user has filtered or what the user has security access to.

[TM1Learner]

Thanks ..

[paulsimon]

Hi TM1Learner

Unless I have misunderstood, I am still unclear as to why you even need a Picklist.

I would suggest that you just set up a sheet with a SUBNM on the Employee dimension that gives a Subset of the Manager Consolidations. The user double clicks on that to select the Manager they want.

You can then either use an ActiveForm to get a listing of the Employees below the Manager, or just use ELCOMP to get the components of the Manager Consolidation. You might need to filter that a little for cases where a component of a Manager consolidation is another Manager Consolidation rather than an Employee. You can use DTYPE or ELLEV to check whether the component is a base level element of a consolidation. In any case there is probably not too much wrong with listing the Manager Consolidations and just using Conditional Formatting to colour code those that are consolidations.

I am thinking of the case where eg the CEO will probably have Manager Consolidations linking in to them, but may also have some direct reports such as Company Secretary or a PA.

Regards

Paul Simon