Help on rule

[Stuart74]

Hello

I am an experienced Cognos Planning developer new to TM1. So maybe this is welcome to enlightenment or the darkside

I am attempting to write a rule in a cube so that a picklist is defined by what is chosen in another picklist, a cascasing picklist. Unfortunately I seem to be going round in circle with errors from the editor. My rule is posted below. Any advice would be greatfully received.

Code: Select all

['Fleet Assumptions':'Business Sub Type']= S: if (DB('Fleet Dimensions', !Elist, !Version, ATTRS('Fleet Assumptions','Business Type') @='Selection1','Static:Test1:Test2'));

The editor complains of unexpected parenthesis.

Where Business Sub Type is the cascaded picklist and Business Type is the "master". So I want Business Sub Type to show Test1, Test 2 if Business Type is Selection1.

For info there are two business types and 5 sub types so I dont think I need to use a dynamic table of values, or a design of this sort.

Thank you

Stuart

[Michel Zijlema]

Code: Select all

['Fleet Assumptions':'Business Sub Type']= S: if (DB('Fleet Dimensions', !Elist, !Version, ATTRS('Fleet Assumptions','Business Type') @='Selection1','Static:Test1:Test2'));

There is a number of things in this rule that are not OK:
1. the colon in the area definition (should be a comma?)
2. the ATTRS function should have 3 arguments, not 2
3. there's no ending parenthesis for the DB function
4. there's a double ending parenthesis for the if statement.

Michel

[Stuart74]

Thank you for your quick respone. Ill have a look and advise.

Regards

Stuart

[Duncan P]

Michel. I think that the left hand side is OK. The 'Fleet Assumptions' is merely fully qualifying the 'Business Sub Type' element with its dimension name. This is good syntax.

However Stuart, the other issue is that your IF only appears to provide a value for the true case. IF takes three arguments, condition, true_result and false_result. You need a value for the case where your DB returns something other than 'Selection1'.

[Michel Zijlema]

Michel. I think that the left hand side is OK. The 'Fleet Assumptions' is merely fully qualifying the 'Business Sub Type' element with its dimension name. This is good syntax.

You're right - I completely forgot about that.

[Stuart74]

Hello

Thank you for your help.

I have now made changes in line with your suggestions and the rule saves without error. However, it does not seem to work as nothing is returned. (I have tried to simplifiy to get to work, hence no Static word)

Code: Select all

['Fleet Assumptions':'Business Sub Type']=S:if(DB('Fleet Dimensions', !Elists, !Version, ATTRS('Business Type',!Fleet Assumptions,'Caption_Default'))@='Selection1','1','0',)

Is there anything I am doing wrong?

Thanks

Stuart

[declanr]

Hello

Thank you for your help.

I have now made changes in line with your suggestions and the rule saves without error. However, it does not seem to work as nothing is returned. (I have tried to simplifiy to get to work, hence no Static word)

Code: Select all

['Fleet Assumptions':'Business Sub Type']=S:if(DB('Fleet Dimensions', !Elists, !Version, ATTRS('Business Type',!Fleet Assumptions,'Caption_Default'))@='Selection1','1','0',)

Is there anything I am doing wrong?

Thanks

Stuart

Do you have any rules before this that apply to the same cells?

Right-click on one of the cells in question and select "trace calculation" (if the cell is just white however try tracing on one of its consolidations and drilling down)... that should tell you what rules are applying etc.

[lotsaram]

Is there anything I am doing wrong?

Hard to say, and I don't think anyone can do better than guess unless you post the full dimensionality of both the Picklist cube and the Fleet Dimensions cube. Only then can we say for sure.

[Stuart74]

Hello

Heres the extra information; I hope is is enough, let me know if more is required.

There is no picklist cube.

Fleet Dimensions cube has 3 dimensions; name (dimension type).

Elists (Hierarchy)
Version (Versions)
Fleet Assumptions (Generic)

On the Fleet Assumptions there are the elements

Business Type
Business Sub Type

Business Type is formatted with a picklist which uses another dimension called Business Type which has two elements, Selection1 and Selection 2.

Busienss Sub Type does not have any formatting.

Amongst others. I have confirmed that there are no feeders, links, or calculations other than the one I am writing in this forum thread on the Item Business Sub Type.

Thanks

Stuart

[lotsaram]

There is no picklist cube.

Then where on earth are you writing this rule !?!
You can only have a picklist, and therefore a rule for a picklist against either an attribute called "Picklist" or in a Picklist cube. So given from the left hand side of your rule it clearly is not for the picklist element in an ElementAttributes cube then if there is no picklist cube then it sounds like you have led us on a merry wild goose chase ...

[java_to_tm1]

Staurt,
Picklists in Cognos TM1
picklists need to go into 1 of 2 places..
1. You can create picklists with element attributes, these go into the control cube '}ElementAttributes_dimname'.
2. You can create Picklists using a picklist cube: if you need greater control on the picklist (specifyin different picklists for different sections of the cube)

In your case, you should probably start off by creating the picklist cube and then writing rules in the picklist cube to show up custom picklists for 'Business Sub Type' depending on 'Business Type' selection

lotsaram, think I missed something in there?

~Sri

[java_to_tm1]

It is very tempting to try to write the text that defines the picklist as a rule-calculated value of the cell itself 'static:Test1:Test2', but TM1 will just show that up as a String. It will not treat that as a picklist at all.

Why doesnt TM1 allow you to set the picklist directly in the cube itself instead of forcing you to create/use a control cube?
The cells of the cube should contain DATA: The metadata (picklist, format, etc.) should be in control objects.

~Sri

[Stuart74]

Thank you for your responses. Gives me alot to think about.

I had originally looked at

http://www.tm1forum.com/viewtopic.php?f=3&t=6078

But guess I have lost my way some what after.

Thank you

Stuart