Finit has delivered solutions for the office of finance 20 years, beginning in 2002 with a focus on Oracle EPM. In 2011, we were the first OneStream implementation partner and delivered the first OneStream solution to go live. We no longer offer consulting services for Oracle EPM. However, the functional experience and technical expertise built from this experience form the foundation of our industry-leading OneStream practice.
HFM User:
Do you have any tips on rules? I currently have some rules that are not running, and I don’t know why.
Chris Barbieri:
I’ve seen a few cases recently in which rules are not running because the variables that the conditions use are not handled properly. I pulled together some design and troubleshooting tips to identify and understand these situations. Additionally, using these techniques can simplify your application and reduce ongoing maintenance.
Use structured variable naming
I include an explanation like this at the top of each rules file. It explains the prefix naming scheme I use, to help whoever is reading the rules file understand the purpose and meaning of each variable:
Here are variables that incorporate the above naming scheme. I include this section at the top of the Sub Calculate. In addition, I include a subset of these variables at the top of Sub Translate and Sub Consolidate.
Use Subroutines
Here is an example of how I use these variables in a custom subroutine, from within Sub Calculate:
This shows the syntax for the custom subroutine that “receives” the variables:
The variables can be passed to the subroutine in any order, but they must be received in that same sequence.
Notes on String comparisons
String comparisons are case sensitive, so you must carefully match the upper and lower case characters of the entity, scenario, value members. Alternatively, you can force the string to be all upper (or lower) case by using the UCASE (or LCASE) functions like this:
If you omit the quotation marks from around a string, vbscript may interpret it as a variable:
In this case, JULFCST is an undefined variable, so the contents of the variable pov_scenario will be compared to a null (empty variable.) Accordingly, this condition will never be True.
The solution is to wrap the string in quotation marks so that HFM identifies it as a string:
Switch between strings and integers
Occasionally, I see a problem with a string that needs to be treated as if it were an integer. The CINT function turns a string into an integer, which can then be used in arithmetic formulas. For example, I may take the last two characters from the scenario’s label. Let’s review how this condition would work for a scenario called Forecast02:
sPeriod = Right(sPovScenario,2) ‘Note the variable starts with “s” indicating this is a string. The result must be a string because sPovScenario is a string and I’m using a vbscript string to parse the string and return only the last two characters.
If CINT(sPeriod) > 3 Then ‘This converts the string “02” into an integer: 2
Alternatively, I could accomplish the same thing by storing the period number in a variable this way:
iPeriod = CINT(Right(sPovScenario,2))
If iPeriod > 3 Then
Use Comments
Make extensive use of comments! Before I write or update any rule, I first write comments to explain what I’m doing and why. Not only does this help me think through the rule, but it can help me later recall why I made the change in the first place. Additionally, this context and explanation can be invaluable for someone else who is trying to understand the rule.
In this situation, I walk the reader through the calculation step-by-step:
Lastly, it is very important to INDENT code and to use subroutines! At some point, each of us has spent time troubleshooting rules and missed a condition from several hundred lines above our rules that still applies and prevents our particular rule from running. Visual indentation can help immensely by quickly showing which conditions are still being used in specific sections of the rules file. To make things even easier to read, I prefer to indent my comments to the same level as the rules and conditions to which they apply.
I hope that you enjoyed reading these rule-writing tips.
Do you have an issue or topic that you’d like Chris to discuss in his next blog post? Please e-mail us at insights@finit.com and we will “Ask our ACE”!