TM1 Forum

Happy New Year!

I am looking for a way to validate data input in TM1 Contributor.
Example: A user enters a numeric value into a field. The value is only allowed to be in the range of 0.000 to 1.000 inclusive. Is there a way to do this?

Also, I need to place requirements on a text field if another field has a specific value.

I can come up with solutions after the fact such as generating exception reports. What I want is the ability to give the data entry person immediate notification if the conditional required field is missing or the numeric value is outside of the accepted range.

Any ideas?

Thanks for your input!

I have also posted this into the IBM DeveloperWorks Cognos TM1 (Applix) Forum.

You can use rules or picklists (for discrete lists), there's a slightly longer version here: http://www.tm1forum.com/viewtopic.php?p=21632

cdredmond wrote:I am looking for a way to validate data input in TM1 Contributor.
Example: A user enters a numeric value into a field. The value is only allowed to be in the range of 0.000 to 1.000 inclusive. Is there a way to do this?

Unfortunately picklists only address one kind of data validation requirement and are only suited to situations where the user should choose from a (relatively small) set of discrete values, typically strings. For a numeric value with a lower and upper limit picklists aren't a solution. If the UI was Excel or a websheet then you could piggyback cell validation but Contributor is rather more restrictive. In terms of immediate notification i think the best you could do would be to create an additional string measure and have a rule that tests whether the inputted value is within the acceptable range and if not then the value of the rule determined string measure creates a warning message like "entered value is outside the acceptable range". For the purposes of incorporating within a cube view an additional dimension with elements like Input (N) and Validation (S) might be better than additional measures. Of course "immediate" requires a recalculation unless auto recalculate is on.

I'm sure you have already thought of this already but I don't think there is anything better in the feature set at this point in time. Of course Excel validation and picklists both suffer from the same problems of the validation only applying to single cell input and easily being circumvented by copy/paste, and no ability for after the fact exception reporting should the validation rules change

cdredmond wrote:Also, I need to place requirements on a text field if another field has a specific value.

Not sure what you mean by this but I think a picklist cube with some rules should fulfill this requirement. It is relatively easy to turn a picklist on/off or change its contents based on the contents of another cell.

cdredmond wrote:I have also posted this into the IBM DeveloperWorks Cognos TM1 (Applix) Forum.

Good luck if you are expecting a knowledgeable response from anyone beyond Mr Usherwood!

Thanks for your response lotsaram!

I had thought of the validation measure to display a message based on a rule. This may be the route I go as it seems to be a "Best Practice". I hope IBM adds validation capabilities in the next release.

As for my poor request on text field requirements, here's another attempt:

Measure A is based on a dimension picklist. If "X" is picked as the value for Measure A, then Measure B requires data entry (based on a different picklist). If Measure A has any value other than "X", Measure B is optional. So in this scenario, I don't want to turn the Measure B picklist on or off, I simply want to make it required or optional. I'm thinking the answer to this may be the same as the numeric data entry range constraint discussed above.

Thanks again for your input!

cdredmond wrote:I hope IBM adds validation capabilities in the next release

I think it would add a big overhead, because in a multi dimensional world it would have to be very flexible to be useful. E.g. I might want to say headcount must be between 80% and 120% of last year. That figure will vary by cost centre so the validation parameters themselves need to be multi dimensional.

I'm also a big believer in giving data ownership to the users. It's their data, if they want to stick in 300% revenue growth, that's their lookout.

cdredmond wrote:Measure A is based on a dimension picklist. If "X" is picked as the value for Measure A, then Measure B requires data entry (based on a different picklist). If Measure A has any value other than "X", Measure B is optional. So in this scenario, I don't want to turn the Measure B picklist on or off, I simply want to make it required or optional. I'm thinking the answer to this may be the same as the numeric data entry range constraint discussed above.

It will have to be rule based again, along the lines outlined by lotsa. E.g.
['Measure C'] = S: if(['Measure A'] @= 'X', if(['Measure B']@='', 'Measure B is required', ''), '');
Note this is logic only, string rules have to be fully qualified, as in DB('Cube', !Dim1, !Dim2, 'Measure A);