So I’ve been working on a 20,000+ line business rule in Planning. Yes, for real. It’s not a complicated script, it’s just repetitive due to using values from a Smart List. It’s quite simple – depending on the month and the cost center they will be moving to, copy over the employee details for the remainder of the year to the new cost center while retaining values in the old cost center up to the point of transfer. However, the script would keep wiping the data. I knew it was working because when I tested only for copying of data, I had values in both cost centers. However, when I added the second section to clear the data in the old cost center, the entire year’s data was wiped, not just the old cost center data months. How frustrating.
Here’s what I discovered…
Here is my test worksheet. The bottom part of the sheet has the original data. The top is the new cost center that data is supposed to copy to.
Here is my original script. Note that if the conditions are met, 2 actions are to take place.
However, when I run the script, all monthly data gets wiped. You can see that my TransferMonth and TransferToCC values are still intact.
I had a hypothesis that it was running the second action even if the conditions were not met. To test, I replaced the #Missing with 412. Sure enough, it was happening.
However, when I removed the second action from the IF/ELSEIF statement, it ran just fine. (Note that I separated out both actions to different scripts and it worked perfectly.)
So, in short, I had to write double the code to get the data to copy and then to clear from the old cost center.
So let me pose a question…does Essbase take a second action item in an IF/ELSEIF statement to be an ELSE statement instead? From my research, it appears that way, but I couldn’t find anything definitive in the Essbase Tech Ref. The closest I found was how Essbase treats IF versus ELSE calculations (that it calculates for the IF/ELSEIF items before the ELSE), but nothing about multiple items below an IF/ELSEIF statement.
Moral of the story? Only one action item per IF/ELSEIF condition set.