Fields of Gold

Just like building formulas in Excel, Monarch lets you build your own formulas for your specific needs. One of the tools with which Monarch stores these formulas is called a “calculated field”.

Monarch categorizes its calculated fields as follows:

  1. Formula-based fields. These are often very similar to the formulas you use in Excel.
  2. Runtime parameter fields. You can use these fields to tell Monarch to assign a specific value to a field every time you open the model. Monarch can remember what value you supplied for that field the last time you used the model.
  3. Lookup fields. These are great for converting specific values to other values automatically. You can set it and forget it, and count on Monarch to get it right every time.
  4. User-edited fields. These give you a very spreadsheet-like way to enter values directly into the Table window.

All-Star Performance

There plenty of reasons to create calculated fields. Here are my top 10:

  1. Converting data types. I often find that Monarch doesn’t recognize the date formats in the reports I use as dates. It can only read them as character fields. I then use a calculated field to get the date field type I need.
  2. Lookup fields make it easy to convert static, meaningless codes into descriptive titles for those readers who aren’t as familiar with the specific data as others. A shipper in the warehouse might know what category “E” products are, but an auditor might prefer to read “Cookies” as opposed to the other products that the business ships, like cakes.
  3. When you build calculated fields with Monarch, you remove a step from using your data with another tool, like Excel or Access. You’re ensuring that the person who’s getting your extracted data (if it’s not you), is getting everything they need. After all, they might not be as adept at formula writing as you.
  4. This tip isn’t specifically about calculated fields, but you can take advantage of this feature when using calculated fields. Just because you needed to create a handful of fields to get to the end result of a single field, it doesn’t mean that all of those intermediate fields have to be displayed in the table. Hide them from view. They’re still there, calculating away. You just can’t see them. It makes for a nice clean presentation when you export the table. Nobody needs to know that it took ten fields to get one usable one. It’ll be your little secret.
  5. Logically breaking up (slicing and dicing) large fields. Sometimes there’s just no way to cleanly extract multiple fields from your report. In those cases you capture a long string that contains all of those fields. You then define multiple calculated fields to break up that long string into the individual fields you want.
  6. Breaking up complex calculations into manageable pieces. Instead of building a really long expression in a single calculated field, split the calculation into bite size pieces. This practice not only makes it easier to maintain the model later on, but you’ll likely find that it makes getting the results you want easier in the first place.
  7. Runtime parameters are wonderful things. Really and truly. Of course you can supply the value of your field manually each time you open your model. But the best way to take advantage of them is by passing them values either programmatically, with the COM model, or using the command line parameter in a batch file. This opens up a terrific opportunity I like to call dynamic filtering, which we’ll explore in another post.
  8. Similarly related to calculated fields but not specifically for calculated fields, keep in mind that your shiny new calculated field doesn’t need to stay in the particular sequence, or column order, that Monarch gave it. You can move them around, left and right, to where it makes sense for them to reside. For increased readability, and easier maintenance later on, group related items. Maybe you want to duplicate the general structure of the original report. It’s up to you.
  9. Calculated fields let you tap some of Monarch’s built in functions, and include special data in your dataset. You can include values such as the report file name, or the page number on which the record appears in your report. You can even use calculated fields to check that your external lookups all returned values for you as you expected.
  10. Your calculated fields can use your custom functions, simplifying your calculations and your models. Custom functions can enhance the readability of your expression and help with accuracy. Once you’ve built a custom function (which can be somewhat complex) you can reuse it by way of object linking. Or your special field might gain a life of its own by becoming a custom function!

Using calculated fields really lets you leverage Monarch’s strengths to your advantage. They visibly help you demonstrate your prowess, making you a valuable resource so that both you and your organization can always excel with Monarch.