Active form and read only element

Post Reply
spark1713
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

Post by spark1713 »

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!
declanr
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

Post by declanr »

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.
Declan Rodger
User avatar
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

Post by Alan Kirk »

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.
"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.
spark1713
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

Post by spark1713 »

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?
User avatar
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

Post by Alan Kirk »

spark1713 wrote: Fri Aug 04, 2017 9:45 am 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?
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.
spark1713
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

Post by spark1713 »

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!!!
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

Alan Kirk wrote: Fri Aug 04, 2017 9:23 am 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
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
User avatar
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

Post by Alan Kirk »

Wim Gielis wrote: Fri Aug 04, 2017 10:35 am
Alan Kirk wrote: Fri Aug 04, 2017 9:23 am 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
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.
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. ;)
"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.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

Alan Kirk wrote: Fri Aug 04, 2017 10:40 amMe with cell security is a bit like you with active forms. ;)
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
declanr
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

Post by declanr »

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
Post Reply