And once again, after an unfortunate and unexpected interruption caused by my site host, ExcelWithMonarch.com returns. It was a bit like a radio station whose transmission tower is destroyed in a storm or something.

Though the outage was out of my control, just days ago I’d promised daily posts for this series. Timing is everything, right? I swear, if it weren’t my bad luck I’ve have no luck at all. Nonetheless, I’m pleased that you’ve returned, so let’s carry on then, shall we? Onwards and upwards…

There was a moment in a recent television drama episode wherein the hero was struggling to find clues to solve a mystery, and it occurred to him that two seemingly unrelated documents were in fact related. He stacked the two sheets as if he were to staple them together. He noted that one document that had some elliptical graphic elements above a text message, and proceeded to cut holes where the graphics were and the holes then revealed a meaningful message in the text message on the second page.

That’s what Monarch does with your report when you define templates: it highlights the data that you want to see, and effectively ignores, or obscures, the rest.

In order to for a model to extract data from a report, fields must be defined in a template.

Monarch offers four types of templates in its toolkit, and on day 3 of the 30 Days to Become a Better Monarch Modeler series, we’ll examine how each of the templates work together, and when to employ with template type to accomplish specific tasks.

The Detail Template

The detail template is the core of Monarch’s report mining abilities, and every model that has a report file as its source must have one (and only one) detail template in order to extract data from the report.

Detail templates are generally used for the fundamental basis of the report; its raison d’être. Whether it’s an inventory list, a sales report, an accounts payable report, or a financial statement, reports tend to have a central topic, and detail templates intend to hunt for the information that relates most closely to that topic.

The Page Header Template

When you want to capture data that appear at the top of the report pages in addition to other places in the layout of the report, you want to use a page header template. Like the detail template, only one page header template can be defined per model.

Page header templates have another role though. You may find that on some reports detail records carry over from one page to another with a complete page heading such as the report title or run data and column heading appearing right in the middle of the record.

In such cases, Monarch won’t “see” the last part of the detail record and won’t capture it properly. But there’s a solution to this problem.

To ensure that records that are interrupted by a page header are properly captured, it is necessary to define a proper page header template even when you don’t intend to extract any data from within this template.

Once the page header template is defined, Monarch will capture the entire record properly.

The Append Template

A Monarch model can contain multiple Append templates. Append templates are intended to add data to the data captured by Detail template which physically appear on the report prior to the detail data. I say “intended” because there is at least one invaluable technique, coined as the “guru trap”, which is used to append data that appears subsequent to one detail record, and prior to another.

The Footer Template

Data that is extracted with a footer template is added to the detail data. Footer data will be added to all detail data that was extracted since the last instance of qualifying footer data.

Within the report, footer data appears subsequent to detail records, and perhaps prior to append or additional detail data.

Unlike the detail and page header templates, multiple append and footer templates can be created within a single model, but to a maximum of 20 total templates of these types.

Your task for today

Becoming a better Monarch modeler requires familiarity with each of the available four template types. Today you need to build a new model which utilizes each of the template styles.

First, examine the report and determine which aspects of the report should be handled by which type of template. Now build each type of template. My experience is that not every report has data that can or should be handled with a footer template, so unless you have a good candidate for this modeling at the ready, you may want to resort to one of the sample reports that are installed alongside Monarch for this exercise.

Your template for success

While you might not be attempting to solve mysteries and perform espionage with your models, a solid grasp of the available templates will help you excel with Monarch.

—

Continue your commitment to Become a Better Monarch Modeler with Part 4 of the series, or review Part 2.