TM1 CellSecurity Using isleaf & IF

[rnbloom]

Relatively new with TM1 and attempting to use for planning marketing results using top down established targets and bottoms up product line personnel entry which must equal the top assigned number. Created a cube with top targets and another for bottom up entry which will roll up to match the top number. The bottom up cube has the top number fed at the consolidated level so product personnel can see how close they are to accounting for their target. An additional 'gap' measure is calculated for the rolled up product value difference to the fed in top down target. To make it cleaner for product personnel entry use the gap measure so they can see, at the consolidate level, the difference between the accumulated product value against the top level target but they see all 0's at the product detail level which is just clutter. Since have lots of product lines and other dimensions do not want to manually update via the cell level security tool but instead want to have rules that, for the leaf level, sets access to the gap measure for product personnel to NONE which takes the gap value to grey (not zeros). The first attempted rule was:

[]=IF((ISLEAF=1),
IF((!}Groups @= 'ProductPersonnel'),
IF((!RevenueMeasure @= 'Revenue Gap'), 'NONE', 'READ'),
'STET'),
'STET');

When check this get "Syntax error on or before: (((ISLEAF=1), IF( missing colon". Also tried using and statements as follows with same colon error:

The second attempt was with and function below getting same colon error:

[]=IF(((ISLEAF=1) & (!}Groups @= 'ProductPersonel') & (!RevenueMeasure @= 'Revenue Gap')), 'NONE', 'READ'); .. gets error "Syntax error on or before: (((ISLEAF=1) & (!}Gr missing colon".

Think there is some syntax that cannot find in any other post or the doc. Any help on setting cellsecurity rules as attempted above or any other method is appreciated.

[ajain86]

When you are setting string values in a rule, you have to place a "S:" immediately to the right of the equal sign.

Try:
[]=S:IF(((ISLEAF=1) & (!}Groups @= 'ProductPersonel') & (!RevenueMeasure @= 'Revenue Gap')), 'NONE', 'READ');

[rnbloom]

THANK YOU THANK YOU ... it works .. .. spent a few hours figuring the use of if or isleaf was wrong ... never looked at the =S location. Many thanks.

[tomok]

Although what you are doing is a perfect example of where a Cell Level security cube can be beneficial, you really should never do this unless absolutely necessary. This is because Cell Level security is the most inefficient way of handling security and it can really kill the perfomance on any cube you use it with. If you think about it, it all makes sense. When you use Element Level security TM1 can just take a quick look at the dimension to see if you have access across the entire cube whereas Cell Level requires a cube lookup into each individual cell in the cube. If the cube is small you can get away with that but if there is any size to it then performance is going to suffer.

As a general rule it's probably not a good idea to use security to perform a "zero suppress" function for you. Better to do that in the view or report itself. There are a number of options available to you for that type of thing using Active Forms.