Adventures in Groovy – Part 45: Locking Cells On Load
Often times, there is a need to lock cells from a user outside of the traditional dimensional security methods. Maybe you don’t want to add dimensional security to accounts so your data push allows users to push data to another cube. Or, maybe there is more complicated logic to identify who can and who can’t edit a cell. The logic to do with is very simple.
Locking Cells
If you have read many of my articles, you likely have read about how to iterate though cells with the data grid iterator. If you haven’t, starting with Part 3 would be a good start. The grid iterator allows the script to loop through all cells, edited cells, or a filtered list of cells. This is traditionally used to execute logic after a user edits a form, but it can be used to lock cells when the form is loaded. Whether it is simply specific accounts, products, or a more complicated filter like products that have or don’t have a specific attribute, it quite easy to do.
Following are two ways to lock the cells. The first is more dynamic. This could do all kinds of things, like lock the cell if it was above a specific value, or if it had a specific attribute, or even something silly, like if it the user’s name was Kyle!
// identify the rows to be locked in a list collection
List<String> lockCells = ['Net Income']
// lock the cells
operation.grid.dataCellIterator().each {
// Lock the cells if the measure member of the cell is in the lockCells list
if(lockCells.contains(it.getMemberName("Measures"))){
it.setForceReadOnly(true)
}
}
The next example would do the same thing as above, but would filter the cells the iterator loops on and lock all the cells that meet the filter applied.
// identify the rows to be locked in a list collection
List<String> lockCells = ['Net Income']
// lock the cells
operation.grid.dataCellIterator(lockCells.join(',')).each {
it.setForceReadOnly(true)
}
Add To The Form
Now that the rule is built, the only thing left is to add this to the form in which it should run. The key is to add this rule and check the Run After Load checkbox. This will allow the form to load (or render), and then run the rule, which in this case, will change predefined cell properties so that it is not editable.
That’s A Wrap
This is not the longest post I have ever made, but it doesn’t mean it is less useful. Locking cells based on criteria, criterial we never had access to before, is quite interesting. When security is set to lock a cell from a user through dimensional security, it also means the users can’t use data pushes to move that calculated data to other cubes, which is an issue. Think about adding an attribute or UDA to those accounts and then building a common script that runs on all forms that locks the cells with that attribute or UDA. It wouldn’t be any more difficult to build, and it can be automated through metadata builds. Hmmm, wouldn’t that be nice!
Hi Kyle,
I have a use case where I want to make an account dimension member on a form read-only based on criteria which is another account dimension member
For e.g. If Expense Amount is 500 leave it as is and let user enter the Expense Description
can I use the above code for this use case?
With some modification, you sure can.
Hi Kyle,
I have been trying to work it out but not able to so far. Below is the code
List lockCells1 = [‘Expense Description’]
List lockCells2 = [‘Sales US’]
List lockCells3 = [‘BegBalance’]
/*lockCells1.eachWithIndex { acct, index ->
println “Account dim: $acct is index $index”
}*/
operation.grid.dataCellIterator().each {
if(it.data0) {
println “$it.MemberNames $it.data”
it.setForceReadOnly(true)
}
}
I have 2 accounts in the columns (Expense Amount and Expense Description which is a Text datatype) and Entity on the rows. Everything else is on page/pov to dummy NA members or begbalance. I need to make Expense Description read only based on the value of Expense Amount. Currently I have only 1 value in the form as 50 . The above code is working but is making Expense Amount as read only, what I want is to make Expense Description read only and keep Expense Amount open for entry
I’m just a noob new to the world of groovy but have to work on this use case so any help would be appreciated 🙂
Get to know the the method crossDimCell. It ssounds like you have enough experience to k ow what a cross dim is in essbase. This is the equivalent. Read http://www.in2hyperion.com/2018/12/10/adventures-in-groovy-part-37-improving-the-user-experience-with-autofill/. This will give you some info on how it works. If it was me I would only loop through your expense column and when that isn’t 0 use the cell cross dim method to reference your description column and lock it. You are almost there. You just need one more method to reference the right cell. If this doesn’t help let me know.
Hi Kyle,
I’m not sure if the below is what you meant and I’m sure there maybe a better way to do it but this is working. Really appreciate your help in pointing me in the right direction 🙂
List lockCells = [‘Expense Amount’,’Expense Description’]
operation.grid.dataCellIterator().each { cell ->
if(cell.data0) {
def descmbr = lockCells.findIndexOf{cell.getAccountName()} + 1
/*println “$descmbr”
println lockCells1[descmbr]*/
cell.crossDimCell(lockCells[descmbr]).setForceReadOnly(true)
}
}
The class I have would be perfect for you. It is still discounted. If you don’t understand the basics, you are going to continue to struggle and write inefficient scripts. I replicated and guess when I started and it caused me a lot of pain.
operation.grid.dataCellIterator(‘Expense Amount’).each {
if(cell.data > 0){
cell.crossDimCell(‘Expense Description’).setForceReadOnly(true)
}
}
Is there a way to set dimensional security from groovy?
Yes and no, and sorry for the late response. You can’t interact with dimensional security. You can, on the load of the form, allow and dissallow access to cells. I have used UDAs to define access and with Groovy, read the UDAs of the members and applied security that way. I would use it less now that cell security is released, but allowing access to parents and dynamic calc members has to be done with Groovy. If you were doing dynamic security, like if dim1 has a UDA of x and dim2 has a UDA of y, Groovy would work well for that. It can also read smartlist and you could theoretically allow specific people to turn on and off security without having admin access.
Hi Kyle,
I Need to lock the cell by user but my script was locking the cell for all the users
class User {
String name
String role
User(String name, String role) {
this.name = name
this.role = role
}
String getRole() {
return this.role
}
String generateReport() {
return “Name: $name, Role: $role”
}
}
// List of users
List users = [
new User(“balapriya.darshan@us.crawco.com”, “User”),
new User(“rajapriya.maran@us.crawco.com”, “Service Administrator”)
]
List allowedRoles = [‘User’]
List report = []
users.each { user ->
if (allowedRoles.contains(user.getRole())) {
def curDataGrid = operation.grid
def cube = operation.application.getCube(‘OEP_FS’)
if (cube.hasSubstitutionVariable(‘OEP_FcstMnth’) && cube.hasSubstitutionVariable(‘OEP_EndMnth’) && cube.hasSubstitutionVariable(‘OEP_FCSTStartYr’)) {
def startMonth = cube.getSubstitutionVariableValue(‘OEP_FcstMnth’)
def endMonth = cube.getSubstitutionVariableValue(‘OEP_EndMnth’)
def forecastStartYear = cube.getSubstitutionVariableValue(‘OEP_FCSTStartYr’)
println(“StartMonth: $startMonth”)
println(“EndMnth: $endMonth”)
println(“Forecast Start Year: $forecastStartYear”)
def monthsToLock = [“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”]
def quartersToLock = [“Q1”, “Q2”, “Q3”, “Q4”]
int startIndex = monthsToLock.indexOf(startMonth)
int endIndex = monthsToLock.indexOf(endMonth)
def monthsAndQuartersToLock = []
if (startIndex != -1 && endIndex != -1 && startIndex <= endIndex) {
monthsAndQuartersToLock.addAll(monthsToLock.subList(startIndex, endIndex + 1))
for (int i = startIndex; i
def cellMember = dataCell.getMemberName(“Period”)
def cellYear = dataCell.getMemberName(“Years”)
if (monthsAndQuartersToLock.contains(cellMember) && cellYear == forecastStartYear) {
dataCell.setForceReadOnly(true)
}
}
} else {
println(“Start month, end month, or forecast start year substitution variables not found.”)
}
}else {
println(“You do not have permission to run this script.”)
}
report.add(user.generateReport())
}
// Print the generated report
report.each { println it }
like this I am tried but it’s not working for me