Active form and read only element
-
- Posts: 5
- Joined: Thu Aug 03, 2017 11:24 am
- OLAP Product: perspectiv
- Version: 10.2.2
- Excel Version: 14
Active form and read only element
Hi All,
I am new in this forum and I hope you can help me
I am working using TM1 10.2.2 developing active form.
I need an help on read only element.
I have two columns 'Vendor' and 'Other Vendor', what I need to know is how to make 'Other Vendor' read only if 'Vendor' is different form 'Others'.
I have DBRW formulas into these to columns.
thanks guys!
I am new in this forum and I hope you can help me
I am working using TM1 10.2.2 developing active form.
I need an help on read only element.
I have two columns 'Vendor' and 'Other Vendor', what I need to know is how to make 'Other Vendor' read only if 'Vendor' is different form 'Others'.
I have DBRW formulas into these to columns.
thanks guys!
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Active form and read only element
Active forms can have security applied in 2 ways; the first is through standard TM1 security, this one will also apply in cube views. The second less used option is simple excel cell protection; I wouldn't recommend this but if you changed your formula in column A to include something like; if these 2 columns don't equal then make the value X otherwise carry on as is.
Then add a format row at the top of the page and add cell protection to on/off for everything accordingly.
Then just protect the sheet and that will do it.
The recommended way would be cell security in TM1. So create a cell security cube and apply a rule to it that says if the 2 measures are not equal then "read" otherwise... whatever else you want.
Then add a format row at the top of the page and add cell protection to on/off for everything accordingly.
Then just protect the sheet and that will do it.
The recommended way would be cell security in TM1. So create a cell security cube and apply a rule to it that says if the 2 measures are not equal then "read" otherwise... whatever else you want.
Declan Rodger
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Active form and read only element
Declan has given you a good answer but to be honest I really don't like using cell security. I've never been comfortable with its performance impact.
The other concern that I have with trying to do it this way is the prospect of someone changing the "trigger value" and invalidating the data. What I mean by that is this:
- The user enters "Others" into the "Vendor" cell. that leaves them free to enter something into the "Other Vendor" cell.
- They later realise that the "Vendor" should not have been "Others" and change it to whatever it should be.
That then leaves you with a value in "Other Vendors" that should not be there. (And, just for good measure, the "Other Vendors" cell is now locked and would have to be unlocked by entering "Others" back into the "Vendor" cell.)
The way I usually approach "input validation" is to use an input value and an output value. If all of the measures need to be validated, I'll usually use a dimension with two elements; Input and Evaluated. In your case I might just use two elements in the measures dimension; "Other Vendor Input" and "Other Vendor". "Other Vendor" would be evaluated by a rule; if Vendor = "Other", then Other Vendor = the "Other Vendor Input" value. Otherwise, just return a blank.
The other concern that I have with trying to do it this way is the prospect of someone changing the "trigger value" and invalidating the data. What I mean by that is this:
- The user enters "Others" into the "Vendor" cell. that leaves them free to enter something into the "Other Vendor" cell.
- They later realise that the "Vendor" should not have been "Others" and change it to whatever it should be.
That then leaves you with a value in "Other Vendors" that should not be there. (And, just for good measure, the "Other Vendors" cell is now locked and would have to be unlocked by entering "Others" back into the "Vendor" cell.)
The way I usually approach "input validation" is to use an input value and an output value. If all of the measures need to be validated, I'll usually use a dimension with two elements; Input and Evaluated. In your case I might just use two elements in the measures dimension; "Other Vendor Input" and "Other Vendor". "Other Vendor" would be evaluated by a rule; if Vendor = "Other", then Other Vendor = the "Other Vendor Input" value. Otherwise, just return a blank.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 5
- Joined: Thu Aug 03, 2017 11:24 am
- OLAP Product: perspectiv
- Version: 10.2.2
- Excel Version: 14
Re: Active form and read only element
thanks guys!
Alan, how could I use your solution?
I mean I got an input form with these two colonms. Which one should I show to the user?
Alan, how could I use your solution?
I mean I got an input form with these two colonms. Which one should I show to the user?
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Active form and read only element
That's the only ugly (I use the term loosely) part; you'd have to show both since they'd need to input into one while the other would show the result of it. You'd therefore need to have three columns rather than two. I have done this before with some forms; I usually format the evaluated one with grey so that the users know that they can't input to it.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 5
- Joined: Thu Aug 03, 2017 11:24 am
- OLAP Product: perspectiv
- Version: 10.2.2
- Excel Version: 14
Re: Active form and read only element
thanks Alan,
Actually I tried the solution of Declanr, and you were right related the issue.
If a user choose Vendor= 'Others', put value into 'Other Vendor', and than change the value into Vendor, the value into Other Vendor is still there.
But, I added a rule into the input cube which says IF the vendor is different from Other, blank, else continue.
it looks like it is working great!!!
Actually I tried the solution of Declanr, and you were right related the issue.
If a user choose Vendor= 'Others', put value into 'Other Vendor', and than change the value into Vendor, the value into Other Vendor is still there.
But, I added a rule into the input cube which says IF the vendor is different from Other, blank, else continue.
it looks like it is working great!!!
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Active form and read only element
I cannot generalize to any TM1 model but usually, this has not been an issue anymore in my opinion.
Certainly not if we restrict the dimensions in the CellSecurity cube to only those that are needed.
Since a couple of versions this is possible and we do not need to take all dimensions in the CellSecurity cube.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Active form and read only element
I'll take your word for it. I had some bad experiences with it in the early days and have pretty much avoided it ever since. I've never had a compelling need to go down that path. Me with cell security is a bit like you with active forms.Wim Gielis wrote: ↑Fri Aug 04, 2017 10:35 amI cannot generalize to any TM1 model but usually, this has not been an issue anymore in my opinion.
Certainly not if we restrict the dimensions in the CellSecurity cube to only those that are needed.
Since a couple of versions this is possible and we do not need to take all dimensions in the CellSecurity cube.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Active form and read only element
Then you will likely be using the cell security in the future
In fact, once I understood that the TM1RPTVIEW function is not very forgiving to changes in the sheet, I accepted it.
We have come to respect each other and we do not fight anymore. Or not that often
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Active form and read only element
I have used cell security a fair few times without any noticeably terrible performance impacts but that being said I only ever need to put them on planning cubes and I very rarely have "large" planning cubes, as I tend to design away from that as much as possible.
Declan Rodger