Many of the reports that our various computer systems generate for us present data in a very linear fashion. These reports tend to be structured in a very top-down manner, having a layout similar to:

• Header
• Optional grouping data (what we as Monarch modelers normally think of as append data)
• A cluster of detail records
• Optional subtotals
• Optional new groupings, followed by more details and subtotals
• Finally, there might be footer data

In fact, that’s probably the most common presentation.

But what about the rest of the reports that we use? What layout do they tend to have?

Typically those reports either scatter data so that the whole page, or most of the page, is a single detail record, as is often seen with special purpose forms, or they display the data in a newspaper-like flowing column format. The latter is what Monarch calls “Multi-Column Regions”, or MCR, and that’s the focus of day 4 of the 30 Days to Become a Better Monarch Modeler series.

Break Down the Boundaries

There are a few steps to setting Monarch up for MCR work.

1. Review the document to see if there’s anything unique about the start of the MCR area within the report.
2. In the Report window, under the Template menu, select Multi-Colum Region…
3. Activate the MCR tools by clicking the MCR “is active” checkbox.
4. Tell Monarch how many column regions exist within the report as you move horizontally across the page. You must define at least two columns, even though Monarch allows a value of 1 for the number of column. Also, you cannot define more than 40 columns.
5. Determine the starting position of the leftmost column
6. Set the width of each column.

You want the columns to be wide enough to capture every field within the group. Assuming that there are three fields within each of four columns, you want the column width to be sufficient to paint the three fields, not all twelve within the line (as you would with a conventional detail template).

Notice that the dialog box also has options for the “Boundaries (vertical)”. These are how we instruct Monarch to limit the MCR area to only specific zones within the report.

We can use the defaults of the top of the page and the bottom of the page, but we can also use previously defined templates as our start and end points. Unless the entire report is an MCR area then we should, at a minimum, set the start of the MCR area with a template.

When we close the MCR dialog by clicking the OK button, we now see that Monarch displays a type of a red frame around each column, and it will treat the data contained in these regions differently that it would without the MCR feature activated.

Now we’ll handle the task of building a template to extract detail records from within the MCR area.

Select a detail line from the report as you would normally, by clicking just to the left of the line(s) that you want to use for your template, and create a new template.

What’s wrong with the sample line? Monarch didn’t bring the content of the whole row in as it does normally? Fear not, there’s nothing wrong. It’s only showing the fields available in a single (the first) column.

Now trap and paint fields as you would normally, but when the new template definition is saved, it’s applied to all of the columns in the MCR area.

New to Monarch V10 is the Column() function which, when used with MCR-derived data, returns the MCR column number in which the data appears on the page.

Your Task for Today

If you’ve not had to model a report with wrapping columns previously, today’s the day that you’ll gain some experience with this aspect of report mining. If you don’t happen to have a report that has this style handy, don’t worry – you can quickly whip up a basic layout in Excel.

First, create a blank Excel workbook and change the font for the entire sheet to Courier New. Now populate columns A through F with some sample (randomly made up) data in, say, the first 10 rows only. Keep in mind that when designing reports most programmers will ensure that the columns maintain a repeating fixed width for the columns, so that each column is maybe 20 characters wide, for instance. Do what a report programmer would do and be consistent.

Now you can save this sheet as a “Formatted Text (space delimited)” prn file type with Excel, which you’ll be able to open in Monarch to experiment with. First define the MCR template, and then set out to define a detail template.

When you’ve successfully captured your sample data in the Table window and add a calculated, formula-based Numeric field employing the Column() function. When that’s done, go back to the Report window and experiment with changing the parameters in the MCR definition to see what happens. Be sure to revisit the Table window to see what effect your changes have made. Do this even if you did have a good MCR candidate report file handy and didn’t resort to creating your own with Excel.

Subscribe and be Involved

We’re approaching the next stage of the 30 Days to Become a Better Monarch Modeler series as we’ll move on from some of the more fundamental aspects of Monarch. And while some of what has been covered so far is old hat to some, every day many more people are just being introduced to Monarch, so I felt it important to include these topics in the series.

Make sure that you don’t miss any of the remainder of the series; use the “Keep up to date” section above to subscribe to the free service that emails new ExcelWithMonarch.com site updates directly to you when they’re posted. I invite you to take a moment and post a comment below to share your thoughts on this post, the series, or the site in general. Or if you’d prefer, email me directly.

Is your data on a long and winding road?

Just because your data is twisting and turning all over the place doesn’t mean that you can’t control it. Just use the Multi-Column Region template building feature, and you’ll be headed in the right direction as you excel with Monarch.

—

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