The origins of this post date back to my old blog and 3 years. It was in my drafts for quite a long until I decided enough was enough 🙂
You might have seen the option to copy comments while performing a “Copy Version.”
3 years back (or longer) somebody asked on my favorite forum Network54 about copy comments using a business rule, that could mean
- Copy comments only – no data
- Copy comments to other dimensions – copy version is from one version to other
- Maybe something else altogether
Here is my first take at a “copy cell comment” business rule. Of course, it uses GROOVY 😉
Prerequisites
Business rule (which calls a groovy script) is using an LCM export/import method, and you’ll need an Export.xml file to perform the LCM export.
You can create an XML file similar to the one shown below.
If you have no clue like me, run an LCM to set up this file.
Once I got the Export.xml file, I moved that to a different folder, and this is the one we’ll use for the business rule.
You might ask what is that weird looking password in the XML, that my friend is the LCM encrypted password. It gets generated and gets stored in the Export.xml file after a successful LCM operation. To obtain the password, I did run an export operation using Utility.bat.
I’m calling Export.xml using a base folder option.
Here is my folder structure after LCM export.
There are the two things that you’ll need (considering you already got Groovy setup, if not please read my blog post on the same)
- LCM Export XML file
- LCM encrypted password
How it works
I did consider doing this (I guess everyone thinks about it first) by poking at the relational tables and quickly dropped that idea (like everyone else) and went ahead with an LCM approach.
If you look at the LCM extract of Cell Text, you can easily find a pattern to perform this operation.
DimMappings tells us which dimension gets mapped to which dimension in the cell text.
All cell notes from a specific plan type are under that plan type node.
What we are going to do in the groovy script is to replicate an entire node of information and replace them with values provided by the user.
As you can see, Operation Expenses – Plan got a cell comment on the Adj Plan scenario. I’ve also attached a rule so that the user can select a scenario to copy the cell comment.
Here is what the Calc Manager rule looks like.
When called, the above rule executes CopyCellText.groovy rule, which provides 3 parameters for the rule.
- Plan Type Name
- Copy From Scenario
- Copy To Scenario
The rule below is copying from one dimension to another, if you want to expand these to multiple dimensions then the groovy rule is going to be different (maybe a different blog post)
Groovy to perform the copy
/* Import groovy XML driver */
import groovy.xml.*
now = new Date()
/* parameters */
lcmFolderName= """Cell Text ${now.format("yyyyMMdd-HHmmss.SSS")}"""
lcmRootFolder = "E:\\Cell Text Copy"
adminUser="admin"
adminUserEncPass="{LCM}akbvG/gihyDq8JhxFw9jQ42Up2ljcq58VPm0owapuh/H8bt1+RNlV8INFFKvkA6g"
/* Extract existing cell texts */
Process expProc=Runtime.getRuntime().exec("cmd /c E:\\Oracle\\Middleware\\user_projects\\epmsystem1\\bin\\Utility.bat \"$lcmRootFolder\\Export.xml\" -b \"$lcmRootFolder\\$lcmFolderName\"")
expProc.waitFor()
/* add new node for the copy */
cellTextXML = new XmlParser().parse(new File("$lcmRootFolder\\$lcmFolderName\\HP-Vision\\resource\\Relational Data\\Cell Texts.xml").toURI().toString())
/* find all XML nodes with scenario */
dim1Node = cellTextXML.planType.find { it.@planName == planName }.cellNote.findAll{
it.DIM1[0].text().equals(fromScenario)
}
dim1Node.each{
def clonedDim1Node = new XmlParser().parseText( XmlUtil.serialize( it ) )
clonedDim1Node.'DIM1'[0].value = toScenario
cellTextXML.planType[0].children().add( 0, clonedDim1Node )
}
/* write the updated XML file */
cellTextUpdatedXML= new File("$lcmRootFolder\\$lcmFolderName\\HP-Vision\\resource\\Relational Data\\Cell Texts.xml")
cellTextUpdatedXML.withWriter('UTF-8') { writer ->
writer.write( XmlUtil.serialize (cellTextXML))
}
/* add user name and encrypted password to exported folder's import.xml */
importXML = new XmlParser().parse(new File("$lcmRootFolder\\$lcmFolderName\\Import.xml").toURI().toString())
/* find the XML node with empty password */
userNode = importXML.User.find{
it.@password.equals('')
}
/*add the username and password */
userNode.replaceNode { node ->
User(name: adminUser , password: adminUserEncPass)
}
/* write the updated XML file */
importUpdatedXML= new File("$lcmRootFolder\\$lcmFolderName\\Import.xml")
importUpdatedXML.withWriter('UTF-8') { writer ->
writer.write( XmlUtil.serialize (importXML))
}
/* import XML process */
Process impProc=Runtime.getRuntime().exec("cmd /c E:\\Oracle\\Middleware\\user_projects\\epmsystem1\\bin\\Utility.bat \"$lcmRootFolder\\$lcmFolderName\\Import.xml\"")
impProc.waitFor()
Line 7 to 10 is where we store the parameters for Groovy; line 7 is the name of the folder, which gets created after a successful LCM execution.
When the groovy rule gets executed, it performs an LCM of the existing Cell Texts into a folder with a timestamp – Line 13.
Once the XML file extraction gets completed, it then looks for the node that contains the Plan Type Name supplied by the rule – Line 19.
With the plan type node in hand, it then searches for the Scenario “Adj Plan” in all the cell notes, once found these are stored as XMLNodes – dim1Node
It is now time to replace and add new nodes; for each node (the one with “Adj Plan”), a new node is created. DIM1 value gets replaced with the user-supplied one (toScenario). Once replaced, the node then gets added before the fromScenario. All these are happening in lines 23 to 27.
That’s it, and it is now time to write the newly created XML and import it into Planning.
Existing Cell Texts.xml gets replaced with this newly created XML file – Line 30 -33.
Once the XML file is written, we need to import this XML file, if we don’t supply a user name and password to the Import.xml (the one which gets created when you perform an LCM) the command line is going to wait for an input. This is not ideal when the command is executed by a rule.
Here comes the groovy magic to the rescue. We can add the LCM encrypted password we obtained in the prerequisite into Import.xml.
Lines 36-52 updates the Import.xml file with the user name and encrypted password. All done, the next obvious step is the import – line 55.
Here is the rule in action.