For most accountants, VB script rules are scary. This is because they don't feel comfortable going into the application and changing rules. But with some basic script writing rules, you can help accountants avoid scripts altogether. The goal is for rules to be driven off metadata, with the result being that instead of changing rules in the application, accountants can update metadata and have those changes flow into how the rules calculate things.
Let's take a look at how we do this. Below are some things you can use to drive your script-writing rules:
- Naming convention of
- User-defined fields of metadata; and
- Drive rules off member lists and hierarchies.
Script writing rules for application changes
All applications will have changes over time, and some of the changes will require rule changes. Is there a way to make these changes and have the rules handle the changes, without the rules themselves being changed? In some cases, the answer is yes.
The number of cases where this can be applicable may be surprising. To really achieve this, it needs to be identified as a goal at the beginning of an application design. This is probably the most challenging design criteria, one of the most interesting design criteria, but if implemented well, it will deliver ongoing benefits.
Every application element that the rules have access to, that can be used to drive the rules behavior, needs to be designed with the rules in mind. For most companies, this means that metadata (primarily scenarios, entities, accounts and the custom dimensions) and member lists would meet these criteria.
For the metadata, script-writing rules can be driven by the label (naming convention). You can use the VB functions -- left, right and mid -- to feed parts of the labels into your rules functions. For example, you can have an account labeled vcash, use a right function and pull out "cash" to reference that account in a formula to populate the vcash account.
You can reference any metadata attribute, but the most common approach would be to reference user-defined fields. There are only three user-defined fields available for every member, but the user-defined fields can be used for multiple purposes. Using the above example you can put "cash" in the user-defined field in the account vcash and use that to drive the rule. In addition to "cash," you could also put something else in that in that user-defined field for use with a different rule. For example, in your user-defined field you have a series of ones and zeros. The application can look at this and determine which rule to run: If position three has a one, do this. If position five has a one, do that.
Rules can also access member lists. Member lists can be used not only to reduce maintenance to rules, but also to reports, data grids and data forms. All of the metadata attributes just mentioned are available to create member lists. If designed and built properly, they will be updated dynamically as the metadata is changed.
The most common approach is to create a loop for members on the list and create a rule that will run on every member of that list. For example, you can create a loop for all base accounts for a parent member, or you can create a loop for all members on a list and then run the rules for all of those members.
By designing script writing rules so that they're driven off other things, accountants can change the rules without having to touch the rules. Also, taking this approach will reduce maintenance. Finally, by designing the application so that changes in the rules are driven by changes to the metadata as opposed to going in the application and playing with the rules makes life much easier for accountants. Most accountants don't want to see the rules -- i.e., they don't want to look at scripts. Allowing them to change rules without looking at scripts is one sure way to have happy accountants.
ABOUT THE AUTHOR
Mike Arnoldy is a principal solutions architect at TopDown Consulting, a Hyperion implementation provider and Oracle partner based in San Francisco. He is a certified public accountant with more than 20 years of experience designing, developing and implementing financial applications.
This was first published in May 2013