Monarch 11 Learning Guide

Monarch 11
Learning Guide
Datawatch Corporation makes no representation or warranties with respect to the contents of this manual or the associated software and especially disclaims any implied warranties of merchantability or fitness for any particular purpose. Further, Datawatch Corporation reserves the right to revise this publication and make changes from time to time to its contents without obligation to notify anyone of such revisions or changes. Monarch software is offered and is to be used in accordance with a SOFTWARE LICENSE AND MAINTENANCE AGREEMENT. This agreement stipulates that this software be used only in the computer system designated in that agreement. The agreement further stipulates that the customer shall not copy or alter, or permit others to copy or alter, the software or related materials in whole or in part, in any media for any purpose, except to make an archive (back-up) copy or to make a copy as an essential step in the use of the software with the customer's computer. Datawatch Corporation hereby grants the buyer the right to reprint this documentation for internal uses only. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, for any other purposes, without the prior written permission of Datawatch Corporation. Monarch Learning Guide July 2011 Copyright 2011 by Datawatch Corporation All rights reserved. Printed in the U.S.A. Unpublished - Rights reserved under the copyright law of the United States. Monarch is a trademark of Datawatch Corporation. Other products mentioned herein may be trademarks or registered trademarks of their respective owners in the United States or other countries. U.S. GOVERNMENT: If licensee is acquiring the software on behalf of any unit or agency of the U.S. Government, the following shall apply: (a) for units of the Department of Defense: RESTRICTED RIGHTS LEGEND: Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data Clause at DFARS 252.227-7014. (b) for any other unit or agency: NOTICE - Notwithstanding any other lease or license agreement that may pertain to, or accompany the delivery of, the computer software and accompanying documentation, the rights of the Government regarding its use, reproduction and disclosure are as set forth in Clause 52.227-19(c)(2) of the FAR.
In the United States: Datawatch Corporation 271 Mill Road Quorum Office Park Chelmsford, MA 01824 Technical Support: +1-978-441-2200 In Europe: The Software Centre, East Way Lee Mill Industrial Estate Ivybridge, Devon PL21 9GE United Kingdom Technical Support Fax: +44-(0)1752 894 833 Technical Support Phone: +44-(0)1752 893 100
Table of Contents
Preface ................................................................................................. i Using the Monarch Documentation ................................................. i Monarch Training ........................................................................ii Monarch Model Building Service ....................................................ii [1] Introducing Monarch ..................................................................... 1 What is Monarch? ....................................................................... 1 Functional Overview ......................................................................... 2 Monarch Data Flow .......................................................................... 4 [2] Monarch Lessons ........................................................................... 6 Before You Begin ............................................................................. 8 Installing Lesson Files ................................................................. 8 The Options Dialog ..................................................................... 9 [3] Working in the Report Window .................................................... 16 Starting a Monarch Session ..............................................................17 Opening a Report File ................................................................18 The Report Window .........................................................................19 The Document Explorer ..............................................................19 Working with Report Colors ........................................................24 Changing Fonts .........................................................................25 Moving Around in a Report .........................................................26 Using the Go to Page Icon ..........................................................27 Finding Information in a Report ...................................................28 Using Bookmarks ......................................................................29 Copying Data from a Report........................................................31 Smartcopying Data ....................................................................32 Printing from a Report ...............................................................34 Summary ......................................................................................37 [4] Extracting Data from a Report ..................................................... 38 Starting the Lesson .........................................................................39 Setting Template Colors .............................................................40 What is a Data Extraction Template? ............................................41 Creating a Detail Template ...............................................................42 Trapping the Detail Lines ............................................................43 Highlighting Fields .....................................................................47 Verifying Field Boundaries ..........................................................49
Naming Fields ...........................................................................51 Naming the Template ................................................................ 52 Changing the Template Colors..................................................... 52 Displaying Data in the Data View Window .......................................... 55 Saving Your Work ...........................................................................56 Using the Auto-Define Trap Feature ................................................... 58 Using the Auto-Define Fields Button ..................................................63 Summary ......................................................................................65 [5] Reports with Multiple Sort Levels ................................................ 66 Starting the Lesson .........................................................................67 Creating the Detail Template ............................................................68 Trapping the Detail Lines ............................................................68 Highlighting the Detail Fields....................................................... 70 Creating Append Templates ............................................................. 71 Trapping the Ship Date Lines ...................................................... 72 Trapping the Account Number and Contact Lines ........................... 73 Defining a Page Header Template ..................................................... 75 Trapping the Page Header ..........................................................75 Verifying Field Boundaries ................................................................77 Naming Fields ................................................................................80 How Records are Assembled.............................................................83 Configuring the Report Index ...........................................................85 Navigating within Report Index ................................................... 87 Exporting from the Report Window .................................................... 89 Saving Your Work .....................................................................93 Summary ......................................................................................94 [6] Special Data Extraction Techniques ............................................. 95 Starting the Lesson .........................................................................96 Special Problems with Addresses ......................................................97 Extracting an Address Block ........................................................97 Using the Address Block Feature ..................................................... 100 Saving Your Work ................................................................... 102 Using the Floating Trap ................................................................. 103 Using the Floating Trap to Capture Lines..................................... 104 Using the Floating Trap to Define Fields ...................................... 108 Using the Multi-Column Region (MCR) Trapping Feature ..................... 111 Creating a Template to Trap the Data ........................................ 115 Specifying Vertical Boundaries for the MCR ................................. 121
Saving Your Work ................................................................... 123 Summary .................................................................................... 124 [7] Working in the Data View Window............................................. 125 Starting the Lesson ....................................................................... 126 Naming Fields .............................................................................. 127 Quick Naming ......................................................................... 128 Format Adjustments...................................................................... 130 Filling Empty Cells ................................................................... 130 Adjusting Field Widths ............................................................. 132 Moving Fields.......................................................................... 134 Hiding Fields ........................................................................... 136 Finding Information in a Table ........................................................ 138 Assigning the Field Type ................................................................ 141 Displaying the Source of a Record.............................................. 141 Saving Your Work ................................................................... 143 Summary .................................................................................... 144 [8] Printing, Copying, and Exporting ............................................... 145 Starting the Lesson ....................................................................... 146 Printing Table Data ....................................................................... 147 Adjusting the Font Size ............................................................ 147 Page Setup Options ................................................................. 147 Selecting and Printing the Data ................................................. 150 Copying Data to Other Applications ............................................ 152 Exporting Data to a File............................................................ 153 Adding Data to an Existing File .................................................. 157 Saving Your Work ................................................................... 159 Summary .................................................................................... 160 [9] Sorting the Table ....................................................................... 161 Sorting the Table .................................................................... 161 Starting the Lesson ....................................................................... 162 Creating a Sort Order Definition ...................................................... 163 Sorting on Multiple Fields ......................................................... 165 Duplicating a Sort Order Definition............................................. 166 Restoring the Original Record Order ........................................... 167 Saving Your Work ................................................................... 168 Summary .................................................................................... 169 [10] Record Selection Filters ........................................................... 170 What is a Filter? ...................................................................... 170
Starting the Lesson ....................................................................... 171 Creating a Values-Based Filter Expression ........................................ 172 Creating a Formula-Based Filter ...................................................... 175 Creating a Compound Filter Expression ............................................ 177 Using Functions in Filters ............................................................... 179 Restoring the Original Data....................................................... 180 Saving Your Work ................................................................... 180 Summary .................................................................................... 181 [11] Calculated Fields ...................................................................... 182 What is a Calculated Field? ....................................................... 182 Starting the Lesson ....................................................................... 183 Creating a Calculated Field ............................................................. 184 Making Comparisons ..................................................................... 187 Hiding and Deleting Calculated Fields ......................................... 189 Saving Your Work ................................................................... 190 Summary .................................................................................... 191 [12] Summaries ............................................................................... 192 What is a Summary? ............................................................... 192 Starting the Lesson ....................................................................... 195 Creating a Summary ..................................................................... 196 Adding Key Fields and Measures ................................................ 197 Selecting a Filter to Apply when Building the Summary ................. 199 Naming and Displaying the Summary ......................................... 199 Suppressing Duplicate Values ................................................... 200 Adding Subtotals and Blank Lines .............................................. 201 Adding Measure Calculations ..................................................... 202 Adding Item Fields .................................................................. 205 Collapsing and Expanding a Summary ............................................. 206 Specifying Summary Design Preferences .......................................... 208 Creating a Quick Summary ............................................................ 211 Copying, Printing, and Exporting Summaries .................................... 213 Saving Your Work ................................................................... 213 Summary .................................................................................... 214 [13] Advanced Summary Capabilities .............................................. 215 Starting the Lesson ....................................................................... 216 Summary Displays ........................................................................ 217 Displaying Key Field Values Across ............................................ 217 Displaying Null Values .............................................................. 218
Freezing Panes ....................................................................... 219 Adjusting Column Widths ......................................................... 220 Viewing Other Measures ........................................................... 221 Displaying Multiple Measures in Across Key Summaries................. 223 Sorting a Summary ....................................................................... 225 Sorting by Measure Values ....................................................... 226 Restoring the Key Field Direction ............................................... 228 Top n Analysis .............................................................................. 230 Specifying Key Field Values ............................................................ 232 Sorting by Key Field Values ...................................................... 234 Upper Limit Values .................................................................. 235 Summary Limit Values ............................................................. 235 Saving Your Work ................................................................... 237 Summary .................................................................................... 238 [14] Charting Summary Data ........................................................... 239 How Monarch Creates Charts .................................................... 239 Starting the Lesson ....................................................................... 241 Displaying Charts ......................................................................... 242 Navigating a Series of Charts ......................................................... 245 Locating a Specific Chart .......................................................... 246 Displaying More Information on Each Chart ...................................... 247 Changing Chart Type and Display Options ........................................ 250 Viewing Chart Values .................................................................... 251 Selecting Chart Display Options ...................................................... 253 Adding a Title to a Chart ................................................................ 256 Specifying Chart Colors and Fonts ................................................... 258 Specifying Chart Colors ............................................................ 258 Specifying Chart Fonts ............................................................. 259 Copying a Chart to Another Application ...................................... 260 Exporting a Chart .................................................................... 261 Printing a Chart ...................................................................... 263 Saving Your Work ................................................................... 263 Summary .................................................................................... 264 [15] Working with Multiple Instances of a Report ........................... 265 Analyzing Data from a Series of Reports ..................................... 265 Starting the Lesson ....................................................................... 267 Opening Multiple Instances of a Report ....................................... 267 Extracting Data ............................................................................ 269
Working in the Data View Window ................................................... 271 Sorting .................................................................................. 271 Identifying the Source of Each Record ........................................ 272 The ID() Function .................................................................... 275 Creating a Summary to Analyze Data from Multiple Reports ................ 277 Saving Your Work to a Model File .............................................. 278 Summary .................................................................................... 279 [16] Extracting Multiple Line Fields ................................................. 280 Starting the Lesson ....................................................................... 281 Capturing a Multiple-Line Field ........................................................ 284 Displaying a Multiple Line Field in the Data View Window .................... 292 Memo Fields ................................................................................ 293 Viewing Memo Fields ............................................................... 294 Using Memo Fields .................................................................. 295 Exporting Memo Fields ............................................................. 298 Printing Memo Fields ............................................................... 300 Saving Your Work ................................................................... 300 Summary .................................................................................... 301 [17] Importing Data from HTML and External Databases ................ 302 Accessing Database Data with Monarch............................................ 303 Importing Data from an External Database ....................................... 304 Adjusting Fields ............................................................................ 310 Storing Import Parameters in a Model File ........................................ 313 Opening the Model File............................................................. 313 Importing Data from an HTML File................................................... 315 Saving Your Work ................................................................... 319 Summary .................................................................................... 320 [18] Performing Lookups from an External Database ...................... 321 What is an External Lookup? .......................................................... 322 Creating an External Lookup from a Report ...................................... 324 Storing Lookup Parameters in a Model File ....................................... 331 Opening the Model File............................................................. 331 Creating a Lookup from Two Different Reports .................................. 332 Using a Report to Create a Lookup File ....................................... 332 Linking to the Lookup File ......................................................... 334 Creating a Lookup from Two External Databases ............................... 337 Importing Database Data ......................................................... 337 Linking Database Data ............................................................. 339
Summary .................................................................................... 342 [19] Working with PDF and XPS Files .............................................. 343 Importing a PDF or XPS File into Monarch......................................... 344 Stretch Option ........................................................................ 345 Mono-spaced Option ................................................................ 346 Freeform Option ...................................................................... 347 Customizing the PDF/XPS Import Options ......................................... 348 Exporting to a PDF File .................................................................. 350 Summary .................................................................................... 353 [20] Digital Signatures .................................................................... 354 What are Digital Signatures? .......................................................... 355 Starting the Lesson ....................................................................... 356 Adding Digital Signatures to an Excel File ......................................... 357 Viewing Digital Signatures ............................................................. 359 [21] Pivot Tables ............................................................................. 361 Starting the Lesson ....................................................................... 362 Exporting to a Pivot Table .............................................................. 363 Viewing a Pivot Table .................................................................... 365 Working with Pivot Tables .............................................................. 367 [22] Monarch Context ...................................................................... 369 Starting the Lesson ....................................................................... 370 Applying Monarch Context ............................................................. 371 Working with Monarch Context ....................................................... 372 Conclusion ....................................................................................... 376
Preface
Using the Monarch Documentation
The Monarch documentation consists of a Learning Guide and a Help file.
MONARCH LEARNING GUIDE

The Monarch Learning Guide contains a short introduction to Monarch along with a series of lessons designed to quickly acquaint you with the program. The lessons are divided into several sections: The Report Window, The Data View window, The Summary Window, and Advanced Topics. Each of the first three sections describes a major area of Monarch. First-time users will be well-rewarded for the time they spend working through these sections. The Advanced Topics section should be tackled after you become familiar with the basic concepts of Monarch, as this section will leverage what you learned in the previous sections. Each lesson should take from 20 to 30 minutes to complete.
MONARCH HELP FILE

The Monarch Help file provides detailed information and step-by-step instructions for performing numerous tasks in Monarch. It also includes information on automating Monarch operations, preparing files for input to Monarch, and technical specifications. To access the Help file, double-click on the MAIN.chm file in the Monarch Program folder (C:\Program Files\Monarch\Program). To access the file while within Monarch, select File, click on the drop-down button of the Help Topics from the options that display. menu, and then select Help
Note
For information on features not covered in the Monarch Learning Guide, consult the Monarch Help file.
Monarch Training
Datawatch provides Monarch training courses to help anyone in your organization rapidly improve his or her Monarch proficiency skills. Available in locations around the country, these training classes are designed for all levels of expertise. Whether you are just getting started and need the basics, or you would like to hone your skills and learn new ways to use Monarch, Datawatch is ready to help you reach new levels of Monarch proficiency. Our objective is to help Monarch users succeed as quickly as possible. Taught by Datawatchs seasoned staff of Monarch experts, the training courses guide you through all aspects of Monarchs features and capabilities, including tips and techniques on effective, time-saving shortcuts. There is also a lab session at the end of class where you can bring in your own files and receive individual instruction and suggestions for using Monarch to meet your specific requirements. Monarch onsite training and personal web-based training courses are also available. For additional information on Monarch training, go to the Datawatch website at www.datawatch.com, point to the Services link at the top of the page, and then select Product Training from the drop-down menu that displays. In North America, you may also call the Training Department directly: Phone: +1-800-445-3311 E-mail: [email protected].
Monarch Model Building Service

Datawatch provides a model building service for its Monarch customers. Free up your time and let the Monarch experts create your models for you. Datawatchs highly trained team of Monarch experts will work with you one on one to create a model that meets your specifications and satisfaction. Are you puzzled by a really complex report? Datawatchs Monarch professionals will be happy to create your Monarch models for you. When you view your new Monarch model, you will be able to learn from the experts by viewing the way they have trapped your report, created calculated fields and filters, defined summaries, created charts, and more. Best of all, this is all done from your own report. To learn more about the Monarch Model Building Service, including pricing, you can go to the Datawatch website at www.datawatch.com. Point to Services, select Monarch Model Building, and then click Let Us Build Your Difficult Reports. In the page that displays, you will be able to download the forms that you need to begin your Monarch model building service with Datawatch. In North America, you may contact the Model Building Services directly: Phone: +1-978-441-2200 E-mail: [email protected].
[1] Introducing Monarch

What is Monarch?
Monarch is a data access tool that lets you view, print, analyze, and extract data from existing computer reports. Any report used in your organization can be accessed via Monarch. When a computer generates a report, it creates a report file that contains all the characters and control codes required by a printer to produce the actual report printout. Monarch reads that same report file. Instead of producing a hardcopy printout with words and numbers frozen on the page, however, it creates a softcopy of the report on screen, with live data you can work with. Monarch reads report files created within any computing environment. These files are commonly known as print or spool files, but they are also referred to as text (TXT), formatted text, PRN, PDF, and SDF files. In most organizations, Monarch users access report files across a network or via terminal emulation software. If youre unfamiliar with this process, ask your PC manager or IT manager for help. If your PC is not connected to a host computer, your IT staff can generally supply report files on the network in CDROM. Monarch can also read a wide variety of input files, such as delimited text, HTML, MS-Excel, MS-Access, dBase, PDF, and XPS, among others, and is able to connect to OLE DB and ODBC sources.
Monarch Learning Guide 1
Functional Overview
Monarch provides three distinct views of report data, each with its own window. When you load a report file, a softcopy of the report is displayed in the Report window.
Figure 1-1. The Report window.
Data extracted from the report displays in the Data View window.
Figure 1-2. The Data View window.
2 Monarch Learning Guide
User-defined summaries, which analyze report data, are displayed in the Summary window. The Summary window includes a charting facility to graphically display the summarized data.
Figure 1-3. The Summary window.
Monarch Data Flow

Figure 1-4 shows how report data flow through Monarch. A Monarch session begins when you load a report file into the Report window. As the session unfolds, you can produce a variety of outputs from each window. The lessons in this guide correspond to the flow chart. Youll learn how to bring a report into Monarch, and you will progress step-by-step through the tasks performed in each window.
Input
Report File
Report Window View and explore report Look things up (Find) Use Report Index Print selected pages Copy to clipboard Define data extraction template
Output
Hardcopy printout of selected pages Clipboard image of data copied from the report, with automatic formatting of the data when pasting into a worksheet Export file in fixed width text or PDF format
Extracted data
Data View window View extracted data Apply filters to extracted data Sort data Calculate new fields Copy to clipboard Export selected records Print selected records Hardcopy printout of selected data in columnar report format Clipboard image of data copied from the table, in both text and worksheet formats Export file containing selected data, in a choice of formats, including XLS, XLSX, XLSM, PDF, MDB, DB, DBF, HTML, WK3, TXT, and delimited text.
Extracted data
Summary Window Analyze selected data Chart summarized data Copy data/chart to clipboard Export analysis Print analysis or chart Hardcopy printout of summary report or chart Clipboard image of chart (picture format) or summary (in both text and worksheet formats) Export file containing selected data, in a choice of formats, including XLS, XLSX, XLSM, PDF, MDB, DB, DBF, HTML, WK3, TXT, and delimited text
Figure 1-4. Monarch flow chart.
Monarch reads both report files and data from database files and OLE DB/ODBC databases, and information from databases can be extracted, manipulated, analyzed, and transformed. It also allows users to access data from additional sources, share user-defined functions, and link and share information from other models, and can access data from files that are of the types XLS, OpenXML, XLSX, XLSM, PDF, XPS, DBF, DB, MDB, HTML, and delimited text, as well as any OLE DB- or ODBC-compliant data source, such as SQL Server, Oracle, and DB2 database management systems.
Input
Database data
Data View window Import data from any XLS, XLSX, XLSM, PDF, XPS, MDB, DBF, DB, HTML, delimited text file, or from any OLE DB/ODBC compliant data source View imported data Apply filters to data Sort data Calculate new fields Copy to clipboard Export selected records Print selected records
Output
Hardcopy printout of selected data in columnar report format Clipboard image of data copied from the table, in both text and worksheet formats Export file containing selected data, in a choice of formats including WK3, XLS, MDB, DB, DBF, HTML, XLSX, XLSM, TXT, PDF, and delimited text
Imported data
Summary Window Analyze selected data Chart summarized data Copy data/chart to clipboard. Export analysis Print analysis or chart Hardcopy printout of summary report or chart Clipboard image of chart (picture format) or summary (in both text and worksheet formats) Export file containing selected data, in a choice of formats, including XLS, XLSX, XLSM, PDF, MDB, DB, DBF, WK3, TXT, and delimited text
Figure 1-5. Monarch flow chart showing data imported from a database.
Figure 1-5 shows how data imported from a database flows through Monarch. You may begin a Monarch session by either opening a report, HTML, PDF, XPS, XLSX, XLSM, or delimited text file, or by importing data from an external database, such as an MDB file or an OLE DB/ODBC data source. When importing data from a database, you can use all of Monarchs Table and Summary window capabilities to manipulate, transform, and analyze data. You can even combine data extracted from report files with database data through a mechanism called an external lookup. These additional capabilities of Monarch are described in detail in Chapters 17 onwards.
[2] Monarch Lessons

This Chapter introduces you to the lessons included in this learning guide. These lessons are designed to quickly acquaint you with basic program operations. For additional material not covered in this guide, consult the Monarch Help file by selecting File, clicking on the drop-down button of the Help menu, and then selecting Help Topics from the options that display.
Chapter 3 Working in the Report Window Shows you how to load a report file, navigate the report on screen, look up information in the report, copy selected data to other applications, and print selected pages from the report. Chapter 4 Extracting Data from a Report Shows you how to create a template to extract data from a simple columnar report, view the data in the Data View window, and save the template to a Monarch model file. Chapter 5 Reports with Multiple Sort Levels Shows you how to extract data from a multi-level report, configure the Report Index, and export from the Report window. Chapter 6 Special Data Extraction Techniques Deals with special problems caused by address blocks and fields that run together in a report. Chapter 7 Working in the Data View window Shows you how to navigate the table, look up information in the table, move and hide fields, and modify field properties. Chapter 8 Printing, Copying, and Exporting Shows you how to copy table data to another application, print the table, and export table data to a file. Chapter 9 Sorting the Table Shows you how to sort the table, create multiple sort definitions, select a sort definition, and save sort definitions. Chapter 10 Record Selection Filters Shows you how to use a filter to select records based on any field value, create multiple filters, utilize value-based filters, select an active filter, and save filter definitions. Chapter 11 Calculated Fields Shows you how to create calculated fields to add information to the table database. Chapter 12 Summaries
Teaches you how to create a summary report from your Monarch data, specify summary design preferences, and create quick summaries. Chapter 13 Advanced Summary Capabilities Shows you how to reveal even more information about your data via Monarchs advanced summary features. Chapter 14 Charting Summary Data Shows you how to create charts to represent the data in a summary. This lesson also covers copying a chart to another application and printing charts. Chapter 15 Working with Multiple Instances of a Report Shows you how to load and extract data from a series of reports. This lesson also covers special calculated field functions related to multiple reports and using a summary to analyze data from a series of reports. Chapter 16 Extracting Multiple Line Fields Shows you how to extract a multiple line text block from a report. It also introduces memo fields, which are used to hold the contents of a multiple line field, and covers exporting and printing memo fields. Chapter 17 Importing Data from HTML and External Databases Shows you how Monarch can be used to access data from HTML and databases. Chapter 18 Performing Lookups from an External Database Shows you how to use Monarch to perform a lookup into an external database to import additional fields that relate to the information in your Monarch session. Chapter 19 Working with PDF and XPS Files Shows you how to import PDF files into Monarch, customize the PDF import options, and export to PDF files. It also shows you how to import XPS files. Chapter 20 Digital Signatures Shows you how to use digital signatures to enhance the authenticity and non-repudiation of your Excel exports. Chapter 21 Pivot Tables Shows you how to create and work with pivot tables. Chapter 22 Monarch Context Shows you how to use Monarch Context, an Excel add-in used for special spreadsheet compliance, auditing, and report navigation purposes.
Before You Begin

Throughout the lessons we make the following assumptions: Monarch is properly installed and the Monarch program item is available when you start Windows. The lesson files are installed and Monarchs default Report, Model, and Project folders are configured to point to the lesson files (the Setup program automatically assigns these defaults the first time you install the product.). You are using an operating system for which Monarch is certified.
Note
If you use a different OS, some slight deviations from the steps listed in the lessons will occur (e.g., the default paths, the appearance of screens, and other minor differences), but you can effectively complete the lessons on all versions of Windows that Monarch supports.
Installing Lesson Files

The Monarch Setup program gives you the option of installing the lesson files. If, when installing Monarch, you chose not to install the lessons files, you will need to re-run the Setup program to install them now. To install the lessons:
Steps: 1. 2. 3. 4. 5. Insert the Monarch CD in your CD-ROM drive. Select the Run command from the Windows Start menu. Type d:\monarch\setup ("d" being the location of your CD-ROM drive) in the command line box, and then press ENTER. Follow the screen instructions. When the setup options screen displays, ensure that the Lessons check box is selected. The Setup program will install the lesson files in the appropriate folders under your Monarch program folder. When the installation is complete, the Setup program will return control to Windows.
The Options Dialog

The Options dialog allows users to configure some universal Monarch settings, including Folders, input information, views, and exports.
Figure 2-1. The Options dialog.
Each of the options listed down the left-hand side of the dialog displays a new set of sub-options with which several Monarch settings could be specified. Each of these options is discussed further below. When you have selected the settings you wish, simply click OK on the lower right-hand corner of the dialog accept your changes and close the box. You may specify several options before selecting OK. Select Cancel to disregard any changes you made and close the dialog. In this case, none of the previous settings are modified.
SETTING FOLDER DEFAULTS

Several file and folder defaults are established during installation and are used in the lessons. These include the default Report Files, Model Files, Project Files, Import and External Lookup Files, and Export Files folders, as well as the default input file extensions. If you have changed any of these defaults since installing the product, you will need to load Monarch and reset each default to its original state. The following steps will show you a typical installation for folder defaults.
Note
If you have not changed any of the default folders, you may skip this section and proceed to Lesson 1.
Steps: 1. Select the Monarch program item from the Windows Start menu. The splash screen displays, after which the main Monarch window is opened. 2. On the File tab, select Options .
The Options dialog displays (see Figure 2-1). 3. In the Report Files box, type C:\Users\Public\Documents\Monarch\Reports, then select dat;prn;rpt;txt as the Default Extension option. In the Import & External Lookup Files box, type C:\Users\Public\Documents\Monarch\Data, then select * as the Default Extension option. In the Model Files box, type C:\Users\Public\Documents\Monarch\Models. In the Project Files box, type C:\Users\Public\Documents\Monarch\Projects. In the Export Files box, type C:\Users\Public\Documents\Monarch\Exports, and then select * as the Default Extension option.
4.
5. 6. 7.
Note
If your Documents and Settings or Users folder is on a drive other than C, substitute the appropriate drive in Steps 37.
8.
Choose OK to close the dialog. You are now ready to begin the lessons.
SETTING INPUT OPTIONS

Several input options may be set regarding how Monarch reads and returns information in the Data View window. To set these, simply check the box or radio button of the setting you wish to enable and then click OK. Each of the options in the Input tab of the Options dialog is specified below.
Option Fields Subtab Function
Date Format
Sets the date format in the Data View window. Available options include: M/D/Y D/M/Y Y/M/D
Range for 2-digit years
Sets the date from which 2-digit years (e.g., 2010 10, 2011 11) will begin to range.
Extraction patterns
Sets the date/time information returned in the Data View window. Available options include: Date Date followed by Time Time Time followed by Date
Numbers
Sets the decimal character for numbers. Options include a period (e.g., 2.50) or a comma (e.g., 2,50). Trims leading and trailing spaces from Character and Memo fields. Leave this box unchecked if you do not wish Monarch to do so.
Text
Text Files Subtab
Encoding
Selects the type of encoding Monarch uses. ANSI ASCII UTF-8 UTF-16LE UTF-16BE
Ignore print control characters Hide n leading characters on each line Page break: Ignore the form feed character. Force page break after n lines.
Trapping Subtab
Specifies whether or not to ignore print control characters. Where n represents the number of leading characters to hide. Sets whether or not to ignore form feed characters. Where n represents the numbers of lines
Trap Characters
Specifies the trap characters to use: English Latin Lower ASCII
Traps are case-sensitive
Specifies whether or not traps created are case-sensitive. If this box is checked, entering CUSTOMER in the Trap line to create a trap from a line with the word Customer will not highlight any report lines. Applies all the settings you selected as defaults (i.e., they will be applied to the current and all future reports/models/projects) Restores previous default settings.
Make these the default settings
Restore from default settings
SETTING VIEW OPTIONS

Each of the options in the View tab of the Options dialog is specified below.
Option Function
Suppress zero values Display null values as Lines in column title row Lines in data rows Object comments
Specifies whether or not to display zero values in rows. Specifies specific strings with which to replace null values in a row. Specifies the number of lines for the column title row. Specifies the number of lines for the data rows. Specifies where object descriptions/comments must appear: Top Bottom Not included
If Not included is selected, comments cannot be added to objects. Use software rendering Make these the default settings Specifies whether or not to enable software rendering in graphics. Applies all the settings you selected as defaults (i.e., they will be applied to the current and all future reports/models/projects) Restores previous default settings.
SETTING EXPORT OPTIONS

Each of the options in the Export tab of the Options dialog is specified below.
Option Field Names Subtab Function
Include field names as the first row of output
Exports the Data View window field names (or Summary window column titles) when exporting data to a text file, a delimited text file, HTML, or Monarch Version 3 formats. Specifies where you want to get the column names from, i.e., from either the Data View window field names or the Summary window column titles, by selecting the appropriate option.
Get column names for summary exports to database formats from
Excel Files Subtab
Apply formatting to XLS and XLSX files
Reflects Monarch's formatting as far as possible when exporting to Excel. This creates only a very small performance impact when using the Monarch Engine. However, it can have a considerable performance impact if you are using the Monarch Version 8 Engine. Enables exports from the Summary using Excel Formulas, Excel Conditional Formatting Rules & Outlining. It also enables the AutoFilter option when exporting from the Data View window.
Apply the Export Wizards "Advanced Excel Features"
Text Files Subtab
Pad each field with one extra space (Fixed-length Text Files)
Separates exported fields with an extra space when exporting to a text file or a delimited text file. With this option unselected, field values might "bump up" against each other producing, for example, "123.45CD" rather than "123.45 CD". Export numeric fields using a standardized format that pads each value with leading zeros, removes the decimal point and places the negation sign at the end of the field value. Encloses the data on the first row of the file in quotes Encloses the data on the first row of the file in quotes Specifies the character to use for separating field values when exporting to a Delimited Text file Selects the preferred format for exporting date values. yyyy-mm-dd yyyymmdd
Pad numeric fields with zeros, suppress the decimal point, and use trailing negation (Fixed-length Text Files) Put "Quotes" around character field values (Delimited Text Files) Put "Quotes" around field names on the first row (Delimited Text Files) Delimiter (Delimited Text Files)
Date format (Delimited Text Files)
Encoding Append DOS end-of-file character (x1A)

MDB & DBF Files
Selects the type of encoding Monarch applies. Determines whether or not a DOS end-of-file character (x1A) is appended to the end of the file
DBF Files
Allows you to select from a drop-down list the version of dBase to use as a legacy file type
MDB Files
Allows you to select from a drop-down list the version of MS Access to use as a legacy file type Applies all the settings you selected as defaults (i.e., they will be applied to the current and all future reports/models/projects) Restores previous default settings.
SETTING CLIPBOARD OPTIONS

Each of the options in the Clipboard tab of the Options dialog is specified below.
Option Function
Include field names as the first row
Exports the Data View window field names (or Summary window column titles) when exporting data to the Clipboard. Selects the text format copied. Tab-delimited Fixed-length text
Text Format
Applies all the settings you selected as defaults (i.e., they will be applied to the current and all future reports/models/projects) Restores previous default settings.
SETTING SECURITY OPTIONS

Each of the options in the Security tab of the Options dialog is specified here.
Option Function
Save passwords from imported databases in projects and models
Specifies whether or not passwords in imported databases will be saved in projects and models. Always Never Prompt
Save passwords from PDF files in projects
Specifies whether or not passwords in imported PDF files will be saved in projects and models. Always Never Prompt
Login Credentials
Specifies a default user name and password to be applied when using files from SharePoint (HTTP) and FTP
locations. Use Current Windows Login Save Credentials in projects Make these the default settings User ID Password Confirm Password
Uses login credentials from Windows login Specifies whether or not to store the specified credentials in projects. Applies all the settings you selected as defaults (i.e., they will be applied to the current and all future reports/models/projects) Restores previous default settings.
SETTING TIME INTERVAL OPTIONS

Option Definitions Subtab Function
Fiscal Year
Displays the Fiscal Year screen of the Time Intervals wizard, with which you can define fiscal years, fiscal periods, and fiscal weeks. Displays the Calendar Week screen of the Time Intervals wizard, with which you can define calendar weeks. Lists available time interval names.
Calendar Week
Interval
Formatting Subtab
Interval Formatting Mask Sample Value
Lists available time interval names. Displays the formatting mask applied to the adjacent interval. Allows the setting of a date via a calendar which can be used to see changes in the formatting masks. By default, the date will be todays date. Shows descriptions of time intervals. Allows the setting of a date via a calendar which can be used to see changes in the formatting masks. By default, the date will be todays date. Applies all the settings you selected as defaults (i.e., they will be applied to the current and all future reports/models/projects) Restores previous default settings.
Description Sample date
[3] Working in the Report Window

In this chapter, you will learn how to launch Monarch and work in the Report window. Lesson topics include: Starting a Monarch session Opening a report file Adjusting the display Moving around in a report Using the Go to page Finding information in a report Using bookmarks Copying data from a report Printing data from a report
Most computer applications produce reports as output. Monarch is unusual because it uses reports as input. A Monarch session begins when you open an existing report. The report displays in the Report window. The Report window gives you a scrollable view of your report. You can move around, look things up, copy data to other applications, and print selected pages on your local or network printer. In this lesson, you will learn the basics of these operations. In later lessons, you will learn how to extract data from reports.
Starting a Monarch Session

The following steps are followed to start a Monarch session.
Steps: 1. Select the Monarch item from the Windows Start menu. The Monarch splash screen displays, after which the Monarch main window opens to a full screen.
Figure 3-1. The Monarch splash screen.
Figure 3-2. The Monarch main window.
Opening a Report File

To begin working with Monarch, you need to open a report file. We use the term report file to describe any computer report stored on disk. These files are often referred to as print, spool, TXT, PRN, SDF, PDF, and formatted or fixed width text files. We have provided several report files for use with these lessons. For Lesson 1, we will open a report called Classic.prn.
Note
Monarch opens a report as a read-only file. Monarch can write a new file with data extracted from the report, but the original report remains safe because it cannot be altered.
Lets open a report file.
Steps: 1. On the File tab, select Open , and then Report.
Figure 3-3. The Open Report dialog.
The Open Report dialog appears. The Files list displays all the files in the Reports folder with a DAT, PRN, RPT, or TXT extension. If you dont see a list of files, make sure you are viewing the Monarch Reports folder, and the file type is set to Print Files (*.dat;*.prn;*.rpt;*.txt), as shown in Figure 3-3. 2. Select Classic.prn, and then choose Open. Monarch displays a softcopy of the report in the Report window. The report shows customer shipments for a distributor of classical music recordings.
The Report Window

Although the softcopy report looks just like its hardcopy cousin, you can use Monarch to zoom, scroll, page, jump, and quickly find information within it. These tasks would be a lot more difficult to carry out with a hardcopy report. Lets look at some of the benefits of viewing and exploring a softcopy report on screen.
Figure 3-4. Viewing a report.
The Document Explorer

The Report window opens and Monarch also displays, by default, a panel on the left side of the screen: the Document Explorer. (If this panel does not display automatically, go to the Home tab and then select Document Explorer from the View group.)
The Document Explorer presents drop-down lists of all open reports and summaries in the current Monarch session. Initially, all the nodes in this panel are unexpanded, but double-clicking on each heading displays the contents of each node. Note that because only one table can be displayed in every Monarch session, the Table node presents only two options: Data View, which opens the Data View window, and Field List, which displays a dialog box including all of the fields contained within the table.
Figure 3-5. A fully expanded Document Explorer showing 3 reports and one summary.
In Figure 3-5, clicking on any report under the Reports node opens a new tab in the Report window, while clicking on any summary under the Summaries node opens a new tab in the Summary window. Lets take a closer look at what else we can do with the Document Explorer. Clicking on the drop-down button on the upper right-hand corner of the panel displays several options that allow you to move the Document Explorer.
Figure 3-6. Move options for the Document Explorer.
Selecting Float lifts the Document Explorer from its position and allows you to place it anywhere on the screen.
Figure 3-7. A Document Explorer in Float mode.
When the Document Explorer is in Float mode, closing (either by pressing the button or deselecting Document Explorer in the Home tab, View Close group) and then reopening it will not cause the Explorer to return to its original position (left-hand side of the screen). To do so, select the drop-down button on the upper right-hand corner of the panel and then select Dock. Selecting Dock as a Document sets the Document Explorer as a new tab group.
Figure 3-8. A Document Explorer in Document mode.
Again, to return the Document Explorer to its original position, you must set it to Dock mode.
The Document Explorer may also be set to Auto Hide mode by selecting Auto Hide from the list of Explorer movements. When this is done, the Document Explorer is hidden and a button displays to on the left-hand side of the Monarch screen. Hovering your mouse over this button displays the Document Explorer, and moving the mouse away hides it.
Figure 3-9. A Document Explorer in Auto Hide mode.
To work with the Document Explorer in Auto Hide mode, click on the Auto Hide button to display the Explorer. To hide it again, simply click anywhere on the report window. Another way of moving the Document Explorer is by utilizing the docking pads. To view the docking pads, click on the header of the Document Explorer and then, without letting go, drag the panel to the middle of the Report window.
Figure 3-10. Displaying the docking pads.
The docking pads provide you with a guide that you may use to decide where in the Monarch window the Document Explorer will dock.
Figure 3-11. The docking pads up close.
To utilize the docking pads, simply drag the header of the Document Explorer to any of the pads pictured in Figure 3-10 and then release your mouse.
Drag the Document Explorer to:
To
Dock the Document Explorer at the lefthand side of your screen. Dock the Document Explorer at the top of your screen. Dock the Document Explorer at the righthand side of your screen. Dock the Document Explorer at the bottom of your screen. Dock the Document Explorer as a document at the left-hand side of your screen. Dock the Document Explorer as a document at the top of your screen. Dock the Document Explorer as a document at the right-hand side of your screen. Dock the Document Explorer as a document at the bottom of your screen. Dock the Document Explorer as a document.
Working with Report Colors

Because Monarch operates in a graphical environment, you can adjust the display to enhance readability and suit your own visual preferences. For example, because many people are accustomed to working with reports printed on green bar paper, Monarch can display greenbar on the screen. Greenbar helps your eyes track across sparse columns in a wide report. If you prefer not to display greenbar, however, you can easily remove it.
Steps: 1. To add greenbar shading, select the Report tab. From the View group, select Greenbar. The report displays with greenbar shading.
Figure 3-12. The report displayed with greenbar shading.
2.
To remove greenbar shading, select the Report tab once more, and from the View group, select Greenbar.
Note
For the remainder of this Learning Guide, we will not use the greenbar feature.
Changing Fonts
Monarch also allows you to change the font used to display reports on screen. To do so:
Steps: 1. To change the font, in the Report tab, locate the View group, and then click the drop-down button on the font selection box.
Figure 3-13. Changing font styles.
Only non-proportional fonts with fixed letter spacing are available because report columns may appear misaligned if proportionally spaced characters were used. 2. Select the Lucida Console font from the list.
Note
If the Courier New font is not available, select one of the other fonts.
The font style is changed throughout the entire report. You may also change font size in order to zoom in or zoom out. Experiment with the font size until you find the size you like best.
3.
Click the drop-down
button on the font size box.
Figure 3-14. Changing font sizes.
Font sizes vary depending on the font style selected. If the size you want isnt available, use the font list to select another font. You can let Monarch select the font size for you using the Size Font to Display Width command. This selects the font size that matches the report width to the display width, so you can view the entire width of the report on screen. 4. To change the font size to match the report width to the display width, from the Report tab, View group, select Zoom to Fit . If the report is too wide to fit on screen even at the smallest available font size, Monarch will select the smallest font size to fit as much of the report width as possible in the available display area.
Note
For the remainder of this lesson, well use the 9-point Courier New font. If these arent your font and font size settings, change them via the corresponding boxes.
Moving Around in a Report

It can be a tedious chore to dig through page after page of a big hardcopy printout. With softcopy reports on screen, however, your computer does most of the work for you. Monarch lets you move around with just a few clicks of your mouse. Like most Windows applications, Monarch provides a vertical scroll bar to help you scroll through a report. You can scroll through the report by pressing the up or down arrow on the scroll bar or by clicking and dragging the slider with your mouse. As you drag the slider, a tooltip window displays the current page of the report. To display a particular report page, drag the slider until the tooltip displays the desired page number.
For example, the following figure shows the report on Page 8.
Figure 3-15. Scrolling the report.
To move up or down one full screen at a time, click on the scroll bar above or below the slider. To move one report page up or down, press the Previous Page button or the Next Page button. A dotted line indicates page breaks. Monarch relies on page break characters in the report file to determine where breaks belong. If breaks are not explicitly defined in the report file, Monarch breaks pages after a fixed number of lines. To move left or right, press the left or right arrows on the horizontal scroll bar, or drag the slider with your mouse.
Using the Go to Page Icon

Monarch includes a Go to Page command which you can use to quickly jump from one page to another, regardless of the number of pages between them.
Steps: 1. Select the Report tab, locate the View group, and then select Go to Page . The Go to Page dialog displays.
Figure 3-16. The Go to Page dialog.
2.
Enter the desired page (e.g., 15) into the Page Number field and then click OK. Monarch displays the specified page in the Report window.
Finding Information in a Report

Its not always easy to find information buried deep in a hardcopy report. If youre looking for a particular invoice in a report that is sorted by customer number, you may be out of luck. Fortunately, Monarch can search reports at lightning speed to find whatever you want. Suppose you want to find all references to the composer, Mozart. To do this, you can use the Search function.
Steps: 1. Select the Home tab, and then click Search The Search panel displays. on the View group.
Figure 3-17. The Search panel.
The Search panel can be moved exactly like the Document Explorer. Briefly, the panel can be: Floated Docked Docked as a document Auto Hidden (This time, the panel is hidden on the right-hand side of the Report window, not the left) Closed
To learn more about each movement, refer to Chapter 3 Working in the Report Window The Document Explorer.
2.
Type Mozart in the search field and then click the Search
icon.
The first occurrence of Mozart in the report will be highlighted. By default, search items are returned in a top-down manner. To search in a bottomup manner, check the Search Up option on the Search panel. 3. Continue clicking the Search icon to locate the next instances of Mozart.
Note
You can make searches case-sensitive by selecting Match Case or Match whole word only. To reverse your search, i.e., to search for previous instances of the word or phrase, check the Search up box before clicking the Search icon. The option Search all reports allows you to locate all instances of the search item in all open reports, while the option Search all open documents allows you to locate all instances of the search item in all open documents, including reports, tables, and summaries.
4.
Close the panel by clicking Close
Using Bookmarks
Monarch allows you to set bookmarks within a session that can be used to navigate quickly between certain lines or sections of a report. You can even annotate bookmarks.
Note
Although bookmarks arent saved in model files, they are saved in project files. When exporting to a PDF file while in the Report window, bookmark comments will be displayed as comments within the PDF file.
Lets set bookmarks for Spinning Records and Musique Royale.
Steps: 1. 2. Use the search tool to locate Spinning Records. Click in the left-hand margin next to CUSTOMER to highlight that line.
3.
On the Bookmarks group of the Report tab, select Bookmark . The Add Bookmark dialog displays. With this dialog you can add a comment to a bookmark. Lets try doing this.
Figure 3-18. The Add Bookmark dialog.
4.
Type whatever text youd like (e.g., This is a bookmark comment.), then click the OK button. A bookmark indicator appears on the left hand margin next to the highlighted line of text. Place your mouse pointer over the bookmark indicator. The text you typed is displayed.
5.
Figure 3-19. A bookmark is added to Spinning Records.
6.
Use the search tool to locate Musique Royale, and then repeat Steps 24 to bookmark it.
Note
You can click the OK button on the Add Bookmark dialog without entering a comment if you prefer. To prevent the Add Bookmark dialog from displaying on a specific bookmark, clear the Show when adding check box. To display the Add Bookmark dialog box once more for a specific bookmark, right-click on the bookmark and then select Edit Bookmark on the context menu. Check the Show when adding box.
7. 8.
You can move through bookmarks by clicking either Next Bookmark or Previous Bookmark from the Bookmarks group. To remove a bookmark, select the bookmark you would like to remove, right-click, and then select Remove Bookmark from the context menu that displays. To clear all bookmarks, select the Clear Bookmarks Bookmarks group. from the
9.
10. Close the Search panel by clicking the Close button on its upper righthand corner before you proceed with the lesson.
Copying Data from a Report

Monarch makes it easy to transfer report data to other applications. Just copy and paste! When you copy data from a report, Monarch creates both a text image and a worksheet image with separate columns and cells in the clipboard. Lets copy a customers name and address into your word processor. You might use this feature if you wanted to insert the address into a letter.
Steps: 1. 2. Go to the first page of the report. Click at the beginning of the customers name (Bettys Music Store in this case) and drag diagonally down to the right to highlight the entire customer name and address, as in Figure 3-20.
Figure 3-20. Copying a portion of the report.
3.
Click Copy
from the Clipboard group of the Home tab.
4. 5.
Launch your word processor. (If you dont have a word processor, simply follow along.) Position the cursor where you want the address to appear and use your word processors Paste command to insert the address into the document.
Figure 3-21. Pasting to a word processor.
If you are going to keep this document, save it before returning to Monarch. (You dont need to save it for this lesson.) 6. Exit your word processor without saving the document.
Smartcopying Data
So far weve seen plain-vanilla copy and paste operations. Now, well copy data into a spreadsheet and learn about Monarchs powerful Smartcopy feature. Monarch uses special parsing and recognition technology to automatically separate columnar data into individual spreadsheet cells. Then, it automatically assigns the appropriate format to each cell (either character, numeric, or date). Well copy several lines of detail information into your spreadsheet.
Steps: 1. Select the first three detail lines from the report, as shown in Figure 3-22. There are two alternative methods to select this block of text: a) click down on the first character in the block, then drag across diagonally to select the block, or b) click and drag in the line selection area (i.e., the vertical area at the left edge of the Report window).
These are the lines we will copy.
Figure 3-22. Copying detail lines.
2. 3. 4.
Click Copy
from the Clipboard group of the Home tab
Launch your spreadsheet. (If you dont have a spreadsheet, simply follow along.) Position the cell pointer where you want the data to appear and use your spreadsheets Paste command to paste the data. For some spreadsheet applications, you may need to use the Paste Special command.
Figure 3-23. Pasting to a spreadsheet.
5.
Adjust column widths and cell formats in your spreadsheet as necessary.
6.
Exit your spreadsheet without saving.
Note
Copy and paste operations are best suited for quick and dirty data transfer jobs. For more robust data transfer, use Monarchs data extraction and export capabilities, as described in later lessons.
Printing from a Report

Monarch lets you print from the Report window. You can print a block of text, a page or series of pages, or the entire report. Lets print a single page from the report. Well begin with Print Preview to see how the page will look before producing the actual printout.
Steps: 1. 2. Return to the first page of the report. Select the File tab, click on the arrow of the Print menu, and then select Print Preview from the options that display. The Print Preview window displays.
Figure 3-24. The Print Preview Window (zoomed in).
3.
Examine the Print Preview window to see if your settings for font size, margins, and page orientation are appropriate to produce satisfactory output. If not, you can change the settings until everything looks just right. Well change settings in a minute, but first, well see how the Print Preview window works. Click Zoom to zoom in or out.
4.
Note
Windows sometimes substitutes proportional fonts in the Preview window when zooming in or out, causing report columns to appear misaligned. Columns will align correctly in the actual printout.
5.
Click the Next Page back.
or Previous Page
icons to move forward or
Now, well adjust our page orientation, margins, and font settings. On many printers, the Classic report prints nicely with a portrait page orientation, half-inch margins, and a 9-point font. You may need to experiment to find the right settings for your printer. 6. Select the drop-down button of the Page Orientation confirm that the page orientation is set to Portrait. menu to
7.
and If you wish to change page margin settings, select Page Setup adjust the margin settings as necessary for your printer by double-clicking on the margin you wish to modify and entering your desired value. Select OK when you are finished. For now, though, lets leave the margins as they are.
Figure 3-25. The Margins dialog.
8. 9.
When satisfied, close the Print Preview window by clicking on the Close button on its upper right-hand corner. Select the File tab, click on the arrow of the Print menu, and then select Print from the options that display.
Well print Page 5.
Figure 3-26. The Print dialog.
10. Select the printer you would like to use. 11. Select the Pages radio button and then type 5 in the Pages box. 12. Select Print. The selected page will be printed. Monarch prints report pages as composed in the report file. Because Monarch adopts pagination from the report file, you must be careful to select an appropriate font size and margin settings to ensure that the full report image will fit on each page. Any lines that do not fit will be carried over to a subsequent page. Any columns that do not fit will be truncated.
This completes Chapter 3. If you plan to do Chapter 4 later, you may close Monarch by selecting the File tab and then clicking Close All . If you plan to go on to Lesson 2 now, you may leave Monarch open, but close the report menu, and by selecting the File tab, clicking on the arrow of the Close then selecting Report. Select Yes when asked whether or not Classic.prn will be closed.
Summary
In this chapter, you learned how to launch Monarch and open a report file in the Report window. You explored the report on screen, copied data to another application, and produced a hardcopy on your local or network printer. For further reading, see the following sections of the Monarch Help file. Chapter 2 - Reports Working with Reports Viewing Reports Searching Reports Copying Report Data Printing Report Data
In the next chapter, you will learn how to define templates to extract data from a report.
[4] Extracting Data from a Report

In Chapter 3, you learned how to use the Report window to explore a report on screen. While this is very useful, Monarch goes much further in giving you access to the data buried in your reports. By defining data extraction templates, you can extract the data from your reports, then analyze it or export it using the Table and Summary windows (discussed in later lessons). In this chapter, you will learn how to extract data from a simple columnar report. The lesson topics include: Determining what an extraction template is Creating a detail template Displaying data in the Data View window Saving your work
Starting the Lesson

To get started, well load Monarch and open the sample report file for this lesson.
Steps: 1. Open Employ.prn. The report displays in the Report window.
Figure 4-1. The Employ.prn report.
Employ.prn is a simple columnar report listing employee information for a fictional company. The report contains a single line of detail information about each employee.
Note
You may need to change your font style and/or font size so that your screen displays the same amount of data as shown in Figure 4-1.
Setting Template Colors

Monarch includes a function that allow users to clearly differentiate between the various types of templates that may be created (Detail, Append, Page Header, Group Footer) via template colors. Template colors allow you to easily determine what type of template is being used to extract information from a selection. Aside from being able to choose your own colors, Monarch also provides a pre-determined set of template colors. If you havent previously set your template colors yet, well do so now. You may change the template colors via the Report Colors dialog box.
Steps: 1. Click Report Colors from the View group of the Report tab.
The Report Colors dialog displays, showing the current color selections for each type of template.
Figure 4-2. The Report Colors dialog.
Aside from the default setting of None (shows no color backgrounds in the report after defining templates), you can select: Gray background to display all templates defined in a report in a gray background. Custom background for each template type to select colors for specific template types. This option is selected by default.
Lets try changing the color for Detail templates. For now, its enough that we use Monarchs pre-determined colors.
2.
Select the radio button for Custom background for each template.
The dialog shows the color set associated with the selection by default. 3. Select OK to accept your changes and close the dialog box. Well change the template colors once more later in the lesson. For now, were ready to extract data from the report. To do this, you will need to define a data extraction template.
What is a Data Extraction Template?

Imagine taking a piece of cardboard and cutting holes in it at certain locations. If you place this imaginary cardboard template on a printed report, you will see only the information that shows through the holes. Monarch uses electronic templates to extract information from your report files in a similar manner. If your report is sorted on several levels, you define a separate template for each sort level. The Employ.prn report has a simple structure, so extracting information from it is relatively easy. Each line in the report will become a record containing information about a single employee. Header lines at the top of each page are thrown away. You can extract all the important information by creating a single detail template.
Creating a Detail Template

A detail template extracts data from the lowest level in a report, in this case, the employee data. To create a detail template, you begin by selecting a sample of the detail information (any line or group of lines that contains a single instance of the employee information). In the Employ.prn report, all of the information for each employee fits on a single line, so well select a single sample line. The sample is used to identify other detail lines and to define the fields to extract. To select a sample line, you highlight the line by clicking in the line selection area to the left of the line. The line selection area is the thin vertical area at the left edge of the Report window.
Steps: 1. Click in the line selection area to the left of the line containing Stephen McPherson.
Note
We could have selected any detail line as our sample. For this exercise, we will use the first detail line.
2.
In the Report tab, select the drop-down button of the Templates menu in the Templates group, and then click New. Monarch splits the Report window between the Template Definition dialog (at the top) and the report (at the bottom). The selected sample line is copied into the sample box at the bottom of the Template Definition dialog.
Figure 4-4. The Template Definition dialog.
3.
In the dialog, select the Role drop-down button and then choose Detail from the options that display. This setting specifies that the template you are creating is a detail template.
Trapping the Detail Lines

Well use the sample line to identify all of the other detail lines throughout the report. To do this, well use a process called trapping. By setting a trap, you tell Monarch which lines to capture and which to ignore. A detail trap identifies common features shared by all of the detail lines throughout the report, but not shared by other lines in the report, such as page header lines, lines containing labels, or lines from higher sort levels. A proper detail trap will capture only those lines we want while ignoring lines from headers and other sort levels. In the Employ.prn report, we need to look for features that differentiate our employee information lines from the title, date, and field name lines at the top of each page. Specifically, we need to look for characters, such as letters, numbers, or punctuation, which always appear at the same position in the detail lines, but do not appear in any other lines.
Note
In this lesson, we will illustrate the trapping process by making some common mistakes and revising the trap until it captures only the detail lines. Once you start building your own templates, you will find that you are usually successful on the first attempt.
USING THE ALPHA CHARACTER TRAP

Lets take a moment to inspect the detail lines in the report. We can see that all detail lines share many characteristics. For example, each detail line contains the name of a city starting at the same column position.
Figure 4-5. Identifying common features.
Lets trap every line in the report with a letter in the first CITY column. To create this trap, well enter a special trap character in the appropriate column position on the Trap line (above the sample box).
Steps: 1. Click in the Trap line above the B in Boston. Note that the status bar at the bottom of the window shows the current cursor position. The cursor should be at Column Position 48. (Note that you can verify the trap position using the Trap Position indicator at the bottom of the dialog.) If the cursor is not positioned correctly, you can use the left and right arrow keys to move it. 2. Click the Alpha Trap icon (see Figure 4-6). An appears on the Trap line. This is a wildcard character indicating that only those lines with a letter in Column 48 will be trapped.
Figure 4-6. Entering an alpha trap character.
Notice that the color of the Trap line changes from white to yellow. This indicates a "match" between the trap character and the associated character in the sample line. The results of our trap are indicated in the report in two ways. First, a chevron character () appears in the line selection area to the left of each line that is selected by the trap. Second, all trapped lines in the report are highlighted.
Figure 4-7. Results of first trapping attempt.
By scrolling through the report, it appears that our first try has indeed trapped all of the employee detail lines. However, this trap is also capturing lines that we dont want: the ACTIVE EMPLOYEE REPORT title at the top of each page and the line containing the column names. These lines are captured because they also have an alphabetic character in Column 48.
USING A NUMERIC CHARACTER TRAP

Lets try another approach. This time well use a trap that looks for a number in the first position of the STREET column.
Steps: 1. 2. Click Reset Trap to clear the previous trap character.
Click in the Trap line above the 4 in 410 Market Street. The cursor should be at Position 26, as shown on the status bar. Click the Numeric Trap icon . An appears on the Trap line. This trap selects only those lines with a number in Column Position 26.
3.
Figure 4-8. Trapping a numeric character.
This trap has been more successful (see Figure 4-9). We now have the employee detail lines captured, but not the extraneous lines at the top of each page. There is just one problem. At least one of the employee lines is not highlighted because its street address starts with a letter rather than a number. Since we are trapping specifically for a number in this column position, the line is ignored.
Figure 4-9. Results of second trapping attempt.
USING MULTIPLE CHARACTER TRAPS

The previous examples were designed to illustrate some of the common mistakes that can be made during the trapping process. In the first example, we trapped more lines than we wanted. In the second example, our trap missed some detail lines. Lets try one more trap. This time well use two trap characters to look for a number in the last position of the ZIP column, immediately followed by a blank.
Steps: 1. 2. Click Reset Trap to clear the previous trap character.
Click in the Trap line above the 5 in 02115. The cursor should be at Position 75. Click the Numeric Trap icon . An appears on the Trap line.
3. 4.
Click the Blank Trap icon . A appears in Column 76. This trap character looks for a blank in the designated column position. By combining these trap characters, only those lines where a number appears in Column 75 and a blank appears in Column 76 are trapped.
Figure 4-10. Using multi trap characters.
As you can see in Figure 4-11, weve now successfully trapped all of the employee detail lines and none of the other lines.
Figure 4-11. Results of the third trapping attempt.
Highlighting Fields
After you are satisfied that the trap is working to capture all of the detail lines, and no other lines, youre ready to highlight the fields that you want to extract. Monarch includes a special button that automatically highlights fields in your template. You can use this capability as a jump start when highlighting fields in your templates, but you should not rely upon this feature exclusively, as Monarch cannot predict which fields you might want to extract and which you want to leave out, nor can it deal with subtleties of report design that allow fields to wrap onto multiple lines or about one another. This lesson teaches you how to manually highlight fields. Lets start by highlighting the LASTNAME field.
Steps: 1. Click down in the sample box at the beginning of the name McPherson, and then drag right to highlight the entire name.
Figure 4-12. Highlighting a field.
Note
Using the Keyboard to Highlight Fields The keyboard provides a more precise method of highlighting fields, since you can easily adjust the field length by a single character at-a-time. To use the keyboard method, press TAB until the focus is on the sample box, and then use the arrow keys to move the cursor to the first character of the field. Press INSERT, and then use the RIGHT ARROW to extend the highlight all the way to the end of the field. Press ENTER to complete the field definition.
The highlight now appears only on the LASTNAME field, not on the entire detail line. This shows you exactly the information youve trapped, in this case, only the LASTNAME field on each line. Note that the sixth name in the list, Stancowicz, has not been entirely highlighted. When we highlighted the LASTNAME field, we did not extend the field highlight far enough to the right to capture longer names. Lets re-define the field to accommodate this name.
Figure 4-13. Testing a field definition.
2.
Click down in the sample box at the beginning of the name McPherson, then drag right until the highlight leaves just one space before 410 Market Street. Note that the field is now long enough to accommodate all of the names.
Figure 4-14. Extending the field highlight.
Lets define more fields.
3.
Use either the mouse or the keyboard to highlight the FIRSTNAME, STREET, CITY, STATE, ZIP, and HIREDATE fields as in Figure 4-15. Each field highlight should be long enough to allow for long field values but not so long that you extend the highlight into another fields data.
Figure 4-15. Highlighting all of the desired fields.
Note
If you make a mistake, place the cursor anywhere in the incorrect field, click Edit Field , and then re-define the field. To delete all of the fields, use the Reset Fields button.
4.
View the results of your field definitions.
Figure 4-16. Results of highlighting multiple fields.
5.
Select OK to accept the new template.
Verifying Field Boundaries

Although you could scroll through the report to ensure that each field highlight is long enough to capture all of the data, this method becomes tedious when you are working with large reports. Therefore, a Field Verification feature is provided that reads the entire report and verifies that your fields are properly defined. When you activate the field verification feature, Monarch scans the entire report and examines the field boundaries. If any characters are found immediately adjacent to a field, Monarch will highlight the field to alert you
that the field definition may be too short to accommodate a field value or that the field may be defined at the wrong location.
Note
If multiple reports are loaded, Verify automatically scans through all of them. When Verify is started it always starts at the first page of the current report, and will continue to report verify hits from that and other reports until it has cycled through all of the reports, thereby completing a full pass. The verification will start over from the top of the current report and re-scan for verify hits (but with any ignores still in effect). Upon reaching the end of a pass without ever having stopped, the dialog returns a message saying that verification has been completed. In this way the user can clearly see when they have succeeded in making a complete "clean" pass through the report(s).
Lets verify that our field boundaries are correct.
Steps: 1. Click Verify on the Templates group of the Report tab.
A progress dialog displays the current status of the Verify operation. When the verify operation is complete, Verify completed displays at the top of the Verify Reports dialog. This indicates that our fields are properly defined.
Figure 4-17. A completed Verify operation.
2.
Click Close to remove the Verify Reports dialog.
Naming Fields
When you are satisfied that the field definitions are correct, you can name each field. Monarch automatically assigns default field names to each field that you highlight, using the prefix "eg_" and the sample data from the first record encountered. You can keep the default names or assign new names that are more meaningful.
Steps: 1. Open the detail template you defined by clicking on the drop-down button of the Templates tool on the Templates group of the Report tab, selecting Detail from the template list, and then clicking Edit. 2. Double-click on Stephen in the sample edit box. The Field Properties dialog displays.
Figure 4-18. The Field Properties dialog.
The Field Properties dialog displays the sample field values for the selected field along with the current field name. 3. 4. Type First Name in the Name box, and then click OK to accept the new field name. Repeat Steps 1 and 2 for each of the remaining fields, using the following names: Last Name, Street, City, State, Postal Code, and Hire Date.
Note
Field Naming Rules Field names may be up to 62 characters in length and may contain uppercase and lowercase characters, spaces, and punctuation marks. However, periods (.), exclamation points (!), accent graves (`), and brackets
([ ]) may not appear in any part of the name. As well, names may not begin with spaces or underscores (_). If a name is entered with leading spaces, the name is accepted but the leading spaces are trimmed.
Naming the Template

When you are satisfied with the trap and field definitions, its time to name the template and apply it to the report. Our detail template extracts employee detail information, so well name the template "Employee Detail".
Steps: 1. Add Employee to Detail in the Name box at the top of the Template Definition dialog so that the name appears as Employee Detail.
Note
Template Naming Rules Template names follow the same naming conventions that apply to field names and may be up to 31 characters in length.
2.
Click the OK button next to the Name box.
Changing the Template Colors

You would have noticed by now that different types of templates (Detail, Append, Page Header, Group Footer) display as different colors in the Report window, as set during the first part of this lesson. Lets change the colors of the Detail template further. You may change the template colors via the Report Colors dialog box.
Steps: 1. 2. After defining your templates, select the Report tab. Click Report Colors from the View group.
The Report Colors dialog displays, showing the current color selections for each type of template.
3.
Click directly on the Detail button to launch the color selector. Note that right now, the button is blue.
Figure 4-20. The Report Colors dialog displaying the color selector.
4. 5.
Select the first color on the first column, first row (#FF8080). Click anywhere on the Report Colors dialog to accept your selection and then close the color selector. You will notice that the Detail button is now bright pink.
6.
Select OK to close the dialog box. A quick look at the Report window shows that the color of the Detail template has been updated.
Figure 4-21. The Detail template color is updated.
Before you proceed, we suggest that you change the Detail template color back to the original one.
Steps: 1. 2. 3. 4. Click the Report Colors button. button once more, and then select the Detail
Double-click on the color drop-down list located on the upper left-hand corner of the color selector to highlight the current color. Type #FFFABD into the list and then press ENTER. Click anywhere on the Report Colors dialog to accept your selection and then close the color selector. You will notice that the Detail button is now the original yellow.
5.
Select OK to close the dialog box.
The steps outlined immediately above show you yet another way to change the template colors using the Report Colors dialog.
Displaying Data in the Data View Window

Congratulations! You have successfully created a template that extracts selected data about each employee. You can view the extracted data in the Data View window.
Steps: 1. Expand the Table node on the Document Explorer and then click Data View. The extracted data appears in the Data View window.
Figure 4-22. Data View window displaying extracted report data.
Note
If any of the columns are truncated, click the Autosize button located in the View group of the Table Columns tab to fully display them.
Each detail line in the report is used to create a database record and each field extracted from the report is used to create a database field. In later lessons well return to explore the Data View window. For now, its enough to see the end result of our data extraction template.
Saving Your Work

After you have created a data extraction template for a report, you can save it in a Monarch model file. A model file saves all of the templates and definitions that you have applied to a report during a Monarch session. Models save work by allowing you to apply the same templates and definitions to a periodic report every time it is generated. Lets save a model file to see how this works.
Steps: 1. Select File, click on the arrow of the Save As Model. The Save Model As dialog displays. menu, and then select
Figure 4-23 The Save Model As dialog box.
2. 3. 4.
Navigate to the folder you would like to save the model file to. Type Employ1 in the File name box, then click Save. The XMOD extension will be added automatically. Select the File once more, and then click Close All to close the Employ report and model. Now lets load the same report with our new model file.
5. 6.
Select File, click on the arrow of the Open Report.
menu, and then select
Select Employ.prn from the dialog box that displays, and then click Open. The Employ.prn report displays in the Report window.
7.
Select File, click on the arrow of the Open Model.
8.
From the dialog box that displays, select Employ1.xmod, and then click Open. The Report window is updated to reflect the effects of the template.
Figure 4-24. Loading the Employ report and model file.
9.
Select the File tab, and then click Close All to close the Employ report and model.
Using the Auto-Define Trap Feature

Now that weve seen how Monarch allows you to manually create traps to extract data from a report, lets turn our attention to the Auto-Define Trap feature. This new feature allows you to automatically define traps. One way we can explore just how effective this tool can be is to use it on the report we were just working with. Using the Employ.prn report, we can compare the auto-define trapping process with the manual trapping procedure we just performed. There are three important things to consider when using the auto-define trap feature: 1. When selecting your sample line (Step 2 below), it is important to select a line that best represents the bulk of the lines in the report, since Monarch will trap all the lines that feature formatting that matches your sample line After you have pressed the Auto-Define Trap button (Step 4 below), be sure to examine more than just one or two pages of the report. If you fail to do so, you may overlook lines of the report that you wanted to capture but Monarch failed to trap due to some anomaly in them. Lines left unchecked (Step 6 below) in the Auto-Define Trap dialog will not be trapped by Monarch, so be sure to select the check boxes of all the lines you want trapped.
2.
3.
Steps: 1. Open the Employ.prn report. The report displays in the Report window.
Figure 4-25. The Employ.prn report.
2.
Click in the line selection area to the left of the detail line (e.g., the one containing Stephen McPherson).
Figure 4-26. Selecting the detail line.
3.
In the Report tab, click on the drop-down button of the Templates menu, and then click New
4.
From the Role drop-down list, select Detail to specify that the template you are defining is a detail template.
5.
Click Auto-Define Trap Trap dialog displays.
on the toolbar. The Auto-Define
Figure 4-28. The Auto-Define Trap dialog.
6.
Scroll down to view the highlighted lines. Monarch analyzes the detail line we selected as our sample, and selects all the other lines in the report that have the same format. Note, however, that the auto-define trap feature has failed to select all of the detail lines.
Figure 4-29. Viewing detail lines that havent been trapped.
This is because, while the detail line we selected as our sample contained a street address that begins with a number (e.g., 410 Market Street), a
few of the street addresses in Employ.prn begin with a letter, as in PO Box 1752. When comparing the sample line with all the detail lines in the report, the auto-define trap feature opted not to trap any lines with such a discrepancy. Note that, on the first page of the report, there are two detail lines which werent trapped. We obviously want these detail lines to be trapped, however, as well as any that may exist on other pages of the report. Fortunately, the Auto-Define Trap dialog provides an easy way to ensure that they are selected. 7. Select the check boxes for the two detail lines whose street addresses are PO box numbers (see Rows 16 and 30). Note that the text color for these two detail lines changes from black to red. This is simply to notify us that the lines dont perfectly match the sample detail line.
Figure 4-30. Selecting the additional detail lines.
8.
Click the Recalculate button. This tells Monarch to additionally trap any lines that match the format of the newly selected ones. The additional detail lines we selected are now highlighted.
Figure 4-31. Viewing the result of the recalculation.
Although we could check the other pages of the report for detail lines which havent been trapped, lets see if the detail lines weve selected are sufficient. 9. Click the OK button to close the dialog, and then scroll down to make sure that all of the detail lines have been successfully trapped.
Figure 4-32. Scrolling the report to make sure all the detail lines have been trapped.
Note that all of the detail lines are now highlighted in the report.
Using the Auto-Define Fields Button

Although we can now manually highlight and name the fields we want to extract, as we did earlier in this lesson, lets try using the Template Definition dialogs Auto-Define Fields feature, which allows us to automatically highlight fields in the template. It uses Monarchs built-in parsing and data recognition capabilities to determine where fields begin and end.
Note
While Monarchs auto-define trap feature is a powerful and effective tool, its important that you dont come to rely on it exclusively. Monarch cannot predict which fields you want to extract and which fields you dont, nor can it deal with the subtleties of report design that allow fields to wrap onto multiple lines or to about one another.
Steps: 1. Click Auto-Define Fields on the toolbar.
Note that the fields in the sample line are now highlighted.
Figure 4-33. Viewing the result of auto-define fields.
Rather than taking the time to name these fields, lets let Monarch assign default names to them. 2. In the Name field, add the word Auto so that the template name reads Auto Detail, and then click the OK button to close the Template Definition dialog.
3.
Expand the Table node in the Document Explorer, and then click on Data View.
Figure 4-34. Viewing the trapped fields in the Data View window.
As you can see, the Auto-Define Trap and Auto-Define Field features can be very effective, particularly when used in conjunction with one another. In the next lesson well use the auto-define field feature again, this time to help us extract fields from a more complex report. 4. Select File and then click Exit Monarch.
Note
Select No if prompted to save any changes.
Summary
In this chapter, you learned how to create a data extraction template to extract data from a simple columnar report. You employed Monarchs special trap characters to select all of the detail lines throughout the report and you defined the fields to extract. You also used the auto-define trap feature to quickly and easily create traps. For further reading, see the following sections of the Monarch Help file. Chapter 2 - Reports Creating Data Extraction Templates Overview Creating a Template Using the Auto-Define Trap Feature Using the Auto-Define Field Feature Verifying Field Boundaries
In the next chapter, you will learn how to extract data from a more complex report, one that includes multiple sort levels and a page header.
[5] Reports with Multiple Sort Levels

In Chapter 4, you learned how to extract data from a simple columnar report. In this chapter, you will work with a report that is organized with multiple sort levels. The lesson topics include: Creating a detail template Creating append templates Defining a page header template Verifying field boundaries Naming fields Learning how records are assembled Saving your work Configuring the Report Index Exporting from the Report Window
Starting the Lesson

To get started, load Monarch and open the Classic.prn report.
Steps: 1. 2. Select the Monarch item from the Windows Start menu Open the Classic.prn report. The report displays in the Report window.
Figure 5-1. The Classic.prn report.
Classic.prn is a monthly shipment report for a distributor of classical music recordings. Like many reports, Classic.prn is organized with multiple sort levels. The entire report is sorted by customer. Within each customer, orders are sorted by ship date. Within each order, shipments are itemized on detail lines. In addition, three header lines appear at the top of each page. In this chapter, Monarch will draw data out of each level in the report based on templates that you define. You will start by defining a detail template, just as you did in Chapter 4. Then youll define an append template to extract fields from each sort level and a page header template to extract fields from the page header. Fields from the append templates and the page header template will be appended to fields from the detail template to produce database records.
Creating the Detail Template

The detail template extracts data from the lowest level in the report, which in this case are the lines representing individual shipments. To create the detail template, well use the same procedure learned in Chapter 4. First, well select a sample detail line, and then well use the sample to identify the other detail lines and to highlight the fields to extract.
Steps: 1. Click in the line selection area to the left of the line containing Bartok, Sonata for Solo Violin. Depending on the font size you are using, you may need to scroll the report to see this line. In the Report tab, select the drop-down button of the Templates menu, and then click New The Template Definition dialog displays and the sample line is copied into its sample box. 3. Select Detail from the Role drop-down to specify that the template you are creating is a detail template.
2.
Trapping the Detail Lines

In Chapter 4, we used the numeric and blank trap characters to trap the detail lines in our employee report. This time, well employ an additional trap character that traps any non-blank character.
Steps: 1. Click in the Trap line above the number 4. The cursor should be at Column Position 9, as shown on the status bar. If the cursor is not positioned correctly, you can use the left or right arrow key to move it to the correct position. Click the Numeric Trap button. appears in the Trap line, representing any number in the designated column position. Click the Blank Trap button twice. appears adjacent to the first trap character, representing a blank in each column.
2.
3.
4.
Click the Non-blank Trap button. appears in the Trap line, representing any non-blank character. By combining these trap characters (see Figure 5-3), were telling Monarch to trap only those lines with a number in Column 9, followed by two blank characters in Columns 10 and 11, followed by a non-blank character in Column 12.
Figure 5-3. Trapping the detail lines.
5.
Scroll through the report to ensure that the trap captured all of the detail lines and no other lines. The report should appear as shown in Figure 5-4.
Figure 5-4. Results of the detail trap.
Note
If you made any mistakes when entering the trap characters, you can highlight the incorrect trap characters and press DELETE on your keyboard to remove them. To delete all trap characters, click Reset Trap .
Highlighting the Detail Fields

After youre satisfied that the trap is working to capture all of the detail lines, but no other lines, youre ready to highlight the fields that you want to extract. In Chapter 4, we were introduced to the Auto-Define Fields button. Lets use it to highlight the fields for our detail template.
Steps: 1. Click Auto-Define Fields .
Each field that is found in the detail template is highlighted.
Figure 5-5. Results of the Auto Define Fields command.
Note
Remember, you should not rely upon the Auto-Define Fields button exclusively, as Monarch cannot predict which fields you might want to extract and which you want to leave out, nor can it deal with subtleties of report design that allow fields to wrap onto multiple lines or about one another
2.
Add Line Item to Detail in the Name box so that the template name reads Line Item Detail, and then click the OK button to accept the template definition.
Figure 5-6. The completed detail template.
Creating Append Templates

In the Classic.prn report, orders are sorted by order number. Also at this sort level is the ship date. Well create an append template to extract these two fields and an additional append template to extract the account number and contact fields that appear at the next higher level. The procedure for extracting fields from each sort level is the same as for extracting detail fields. First, well select a sample, then well trap the other occurrences of the sort level throughout the report, then well highlight the fields we want to extract.
Steps: 1. 2. 3. Press CTRL+HOME to return to the top of the report. Select the line containing ORDER NUMBER. This line is indented and appears below the line containing the field names. In the Report tab, select the drop-down button on the Templates menu, and then click New. The Template Definition dialog is opened and the sample line is copied into its Sample box. 4. Select Append from the Role drop-down list to specify that the template you are creating is an append template.
Figure 5-7. The selected ORDER NUMBER line.
Note
The total number of all append and footer templates cannot exceed 20. For each footer template used, there is one less append template available. For example, if you create three footer templates, then only 17 appends can be used.
Trapping the Ship Date Lines

Well use an exact match trap to capture all lines representing the order number level. An exact match trap looks for an exact match of a character or series of characters. Well use the two colons that appear at the end of the ORDER NUMBER: and SHIP DATE: labels.
Steps: 1. Continuing from the previous exercise, click in the Trap line above the colon following ORDER NUMBER in the sample line. The cursor should be in Position 27, as shown on the status bar. Type a colon (:) in the Trap line directly above the colon in the sample box. Move the cursor to Position 49 in the Trap line. Type another colon (:) in the Trap line. Highlight the ORDER NUMBER and SHIP DATE fields (as shown in Figure 5-8).
2. 3. 4. 5.
Figure 5-8. Highlighting the ORDER NUMBER and SHIP DATE fields.
6.
Scroll through the report to see the results. Note that on the fourth page of the report the RETURN AUTHORIZATION and RECEIVED fields have been highlighted (see Figure 5-9). We dont want to capture these fields, so we need to edit our template definition.
Figure 5-9. Viewing the result of the highlights.
7.
In the Trap line, place the cursor directly above the 5 that begins the order number (536017) in the sample line, then click the Numeric trap button.
Note
Note that the RETURN AUTHORIZATION and RECEIVED fields are no longer highlighted on Page 4 of the report.
We can now safely save our template. 8. Replace Append 1 with Order Number Level in the Name box of the Template Definition dialog, and then click the OK button to accept the template definition. The fields trapped by the append template we just created can be easily distinguished from those trapped by the detail template due to the different color that Monarch assigns them.
Trapping the Account Number and Contact Lines

Steps: 1. Press CTRL+HOME to return to the top of the report. Note that the ACCOUNT NUMBER and CONTACT fields are on different lines. To capture both fields, well select a multiple line sample. 2. 3. Click down in the line selection area to the left of ACCOUNT NUMBER, and drag down two lines to highlight the CONTACT line. Select the Templates then click New drop-down button from the Report tab and
The three lines are displayed in the Template Definition dialog. 4. Select Append from the Role drop-down list to specify that you are creating another append template.
Figure 5-10.The Template Definition dialog showing the sample lines.
For a multiple line sample, you need to indicate which of the sample lines to trap. Generally, you will be able to trap on the first line by identifying features unique to that line. Occasionally, however, there will be no unique features that identify the first line. If it is impossible to trap on line 1, use
the Trap Line box to indicate another line that has unique features. (The Trap Line box is located in the row of buttons in the middle of the Template Definition dialog.) In this lesson, well trap on the first line of the sample, so you wont need to adjust this setting. Note that an ACCOUNT NUMBER label always precedes the account number field. Lets use this specific sequence of characters as an exact match trap. 5. Type Account Number at the beginning of the trap line. By default, Monarch traps are not case-sensitive, so you need not enter the text exactly as it appears in your sample line.
Note
You can specify case-sensitive as the default case setting for traps by selecting the File tab, and then clicking on Options . In the Options dialog that displays, select the Input tab, go to the Trapping subtab, and then check the Traps are case-sensitive box.
6.
Click Auto-Define Fields
Figure 5-11. Highlighting the account number and contact fields.
Monarch highlights the fields in the report. You may need to scroll the report to see the results. 7. Replace Append 2 with Account Number Level in the Name box, and then click the OK button to accept the template definition. Note that Monarch has assigned the same color to the fields trapped by the Account Number Level template as to those trapped by the Order Number Level template. This is because they are both append templates.
Defining a Page Header Template

Page headers often include useful information, such as the report date. In the Classic.prn report, the page header includes the report date and the page number. Lets extract these two fields.
Steps: 1. 2. 3. Return to the top of the report. Select the first line of the report as the sample line. Select the drop-down button of the Templates New menu, and then select
The sample line displays in the Template Definition dialog.
Figure 5-12. The sample line from the page header.
4.
Select Page header from the Role drop-down list to specify Page Header as the template type.
Trapping the Page Header

Most reports have a form feed character (ASCII 12, HEX 0C) embedded at the page break. Monarch can use this character to identify the beginning of a page header, so you normally do not need to set a trap to capture the page headers. However, if your reports do not include the form feed character, you will need to use a trap to capture the page headers. If you are unsure about your reports internal structure, you should play it safe and create a trap. Thats what well do for this lesson.
Steps: 1. Click the Numeric Trap button twice, then type a forward slash (/) in the Trap line. This trap will capture the first line of each page header (where the date is represented in MM/DD/YY format). Highlight the report date and the page number, as shown in Figure 5-13.
2.
Note
The Auto-Define Fields button will not work to capture the report date field due to the logic it uses to distinguish fields from labels, headings and other static text that appears in reports. Any text that does not change throughout the report is considered a label, even if that text looks like, and, as in this case, is, a field.
Figure 5-13. Highlighting the page header fields.
The page header fields in the report are highlighted. 3. Click the OK button to accept the template definition. Since you did not name the template, a default name is assigned for you. For detail and page header templates the default names are Detail and Page Header, respectively. For append and footer templates, the default names are Append n, and Footer n, where n is incremented for each new append or footer template. Note in the report that Monarch has highlighted the fields trapped by the page header template with a unique color in order to distinguish them from those trapped by the detail and append templates.
Verifying Field Boundaries

In this lesson, you have defined your fields without examining the entire report to identify the maximum length for each field. In most cases you will make the right guess about field length, but why leave it to chance? The Verify command scans the entire report and examines each field to make sure its long enough to capture all the data. When you perform a verify operation, Monarch scans the entire report to ensure that all fields are painted in the correct location and are the correct length and data type. If any non-blank characters are found immediately adjacent to a field boundary, Monarch will alert you that the field may be too short or defined at the wrong location by displaying the Verify Reports dialog (see Figure 4-17). Then you can decide whether the field is correctly defined or needs to be redefined. When a right boundary hit occurs, plus (+) and minus () buttons appear in the Verify Reports dialog to the right of the Continue button. Pressing the plus (+) button will expand the field in question by one character on its right side. Pressing the minus () button will shrink the field by one character on its right side. Likewise, when a left boundary hit occurs, the plus and minus buttons are shown to the left of the Continue button. Pressing the plus (+) button will expand the field in question by one character on its left side. Pressing the minus () button will shrink the field by one character on its left side. When using the plus (+) or minus () buttons, the results of the expanding or shrinking are immediately reflected in the report view and in the template editor (if its up). The plus (+) and minus () buttons are grayed as necessary so that a field cannot be shrunk to zero width, nor can it be expanded to the point where it overlaps another field from the same template. Verify also detects data type failures. A data type failure occurs when a numeric field cannot be converted to a valid number or when a date field cannot be converted to a valid date. The Verify Reports dialog displays the status of a verify operation. If the verify operation is suspended to allow corrections, the Verify Reports dialog allows you to continue after the corrections are made. Lets verify that the fields are properly defined.
Steps:
1.
In the Report tab, Templates group, click Verify
A progress dialog displays the percentage of the report that has been verified. You may need to resize the dialog to view the complete set of possible functions within it. If Monarch successfully verifies all fields, Verify completed with no stops displays on the Verify Reports dialog. If Monarch finds a field with characters adjacent to its boundaries, it will stop and highlight the field. You must then decide if the field should be redefined to include the adjacent characters.
The Classic.prn report contains several field boundaries that are not clear on Page 1 of the report. Had you manually painted these fields, you might have painted the QTY field with only two characters, which would result in you missing several negative field values on Page 4 (see Figure 5-14). You may also have missed the corresponding parentheses denoting negative values in the AMOUNT field. Verify will report these problems.
Figure 5-14. Example of a field failing the Verify operation.
If Verify reports a field boundary problem you may correct it immediately. To modify a field boundary:
Steps: 1. Click the plus (+) or minus () button on the Verify Reports dialog to increase or decrease the highlighted field. Each click of the plus or minus button increases or decreases the field by one space. The Report window immediately displays the changes to the field. 2. When you have finished editing the field, click the Continue button to see if any additional fields need editing.
3.
When you are done verifying the fields, click Close on the Verify Reports dialog.
Figure 5-15. A completed Verify operation.
Naming Fields
In the previous lesson, we named our fields immediately after highlighting them, while the Template Definition dialog was still displayed. In this lesson, we let Monarch assign default field names for all fields. Now that we have completed our template definition, we can go back and name all of the fields. Monarch provides two methods we can use to name our fields: we can display the Template Definition dialog for each template, naming each templates fields in a separate step, or we can use Monarchs Field List dialog to name all of the fields at one time. Youve already seen how you can name fields in the Template Definition dialog (by double-clicking each field highlight), so now lets see how the Field List dialog works.
Steps: 1. In the Home tab, Data Definitions groups, select Field List . The Field List dialog displays as a docked list to the right of the Report window, showing the properties of all fields you have created. The Field List dialog can be moved, floated, and docked like the Document Explorer (see Chapter 3 - Working in the Report Window The Document Explorer). For the rest of this section, lets work on the Field List dialog in Float mode so that we can see all the necessary fields. To float the Field List dialog, right-click on the dialog header and then select Float from the context menu that displays. Resize the dialog box as necessary.
Figure 5-16. The Field List dialog displays names and properties for all fields.
Notice that each field has been assigned a default field name, based on the order in which you created the fields. The reason the field names appear out of order is because this dialog initially displays fields in the order in which they will appear in the Data
View window, which is based on the hierarchy of templates that you created. You can re-order the fields based on any field property by clicking on the appropriate column title at the top of the dialog. Well sort the fields based on the template that they were extracted from, as this is the most useful order for naming fields. Checking the box labeled Show Template Properties at the lower lefthand corner of the dialog displays the fields template properties, such as the number of lines occupied by the template, the column widths, and the template widths, among others, from the dialog. Likewise, checking the box Show Table Properties displays the table properties of each field, such as its table width, length, and alignment. 2. 3. For now, lets check the box for Show Template Properties. Click on the Source column title at the top of the Source column. Monarch sorts the fields based on the template names (i.e., the source of each field).
Figure 5-17. Sorting the fields according to the template in which they were defined.
Lets replace some of the default names in the Field Name column. 4. In the Field Name column, highlight the third field name (i.e., eg_8_99), type Unit Price, and then press the ENTER key on the keyboard to move to the next field name. Locate the appropriate fields and then rename them using the following field names: Amount Media Quantity Label Number Description
5.
Order Number Ship Date Page Report Date 6. You may also want to retype the field names for Account Number and Contact so that they are more consistent in format with the other field names. Close the Field List dialog by clicking on the corner of the dialog. on the upper right-hand
7.
Note
Field Naming Hints Inspect the line and column properties to determine the location of each field. You may also move the Field List dialog around on screen to view the underlying report which may help you determine appropriate field names from the labels that appear in the report.
How Records are Assembled

Monarch generates one record for each detail line extracted from the report. The detail line contains the data that changes most often in the report and is usually the reports lowest sort level. The append templates add, or append, information from higher sort levels. In this lesson we extracted fields from four levels in the report; the detail level, two sort levels and the page header. Figure 5-18 illustrates how a single record is built from the templates we defined.
Figure 5-18. Assembly of a record.
Lets open the Data View window to see the records created by our templates.
Steps: 1. Expand the Table node in the Document Explorer, and then select Data View.
The extracted data appears in the Data View window. 2. from the View group of the Table tab to Select Autosize Columns fully view all the fields. If this is not enough, select a smaller font size.
Figure 5-19. Extracted data displayed in the Data View window (with column widths autosized and a smaller font size selected).
Configuring the Report Index

Now that weve successfully extracted data from the Classic.prn report, lets try using Monarchs Report Index feature. Available when viewing the Report window, the Report Index displays a hierarchy of field values within one or more reports, thereby allowing you to quickly and easily explore a report and zero in on the information you are interested in. By exploring the tree, you can get a bird's eye view of your report data. When you find the information you are interested in, you simply click on the field value to jump to the corresponding page in the report. Initially, the tree is empty. Lets build the Report Index so that we can see firsthand how helpful it can be.
Steps: 1. Click the Report Index drop-down button from the Report tab, View group, and then select Edit Report Index Fields. The Report Index Fields dialog displays.
Figure 5-20. The Report Index Fields dialog.
In the Available Fields list are the fields we defined earlier. We can use up to five of these fields to build our tree. 2. Select the Account Number field, and then click the Add >> button. The Account Number field is removed from the Available Fields list and added to the Selected Fields list. Select the Order Number field, and then click the Add >> button. The Order Number field is added to the Selected Fields list. Select the Description field, and then click the Add >> button to add it to the Selected Fields list.
3. 4.
Note that the fields are listed in alphabetical order in the Selected Fields list (see Figure 5-21). Their order in the actual Report Index hierarchy will be determined by their order in the report data, as well see shortly.
Figure 5-21. Viewing the Selected Fields list.
Now that weve configured the tree definition, lets see how the Report Index displays in the Report window. 5. Click the OK button to close the Tree Definition dialog, and then select Open Report Index from the Report Index drop-down menu. Monarch displays the Report Index on the right side of the Table (or Report) window by default. Initially, the tree is collapsed. You can move the panel by double-clicking on the panel header and then dragging it anywhere you wish on the window.
Figure 5-22. Displaying the Report Index.
6.
Expand the Classic.prn tree on the Report Index by clicking on the arrow ( ) to the left of the label Classic.prn.
Navigating within Report Index

Monarch provides two modes of navigation within the Report Index, passive navigation and active navigation. Passive navigation involves expanding or collapsing branches to view underlying items without updating the Report view display. Active navigation involves highlighting a branch in the tree. Whenever a branch is highlighted, the Report view is automatically updated to display the corresponding page in the report. Typically, you will use a combination of passive and active navigation in Report Index, e.g., you would click on the arrow ( ) to the left of a branch to expand it (passive navigation), and then, when you find an item of interest to you, you would click the item to display the corresponding page of the report (active navigation). Lets perform both modes of navigation within Report Index.
PASSIVE NAVIGATION
First, lets try passive navigation within Report Index.
Steps: 1. 2. Return to the Report Window. Click the arrow ( ) next to the first account number (i.e., 11887) displayed in the tree, and then expand all the underlying branches by clicking their arrows ( ). Note that the two order numbers for the account are nested beneath the account number.
Figure 5-23. Using passive navigation in Report Index.
Note that nothing in the Report view has been highlighted.
ACTIVE NAVIGATION
Now lets give active navigation a try so that we can see how it differs from passive navigation.
Steps: 1. Scroll down to the bottom of the Report Index pane and click the arrow ( ) next to the last account number (i.e., 14162). Click on order number 536015. Click on the number itself, not the adjacent arrow ( ). Note that the report page (Page 19) containing the corresponding data displays in Report view and the data itself is highlighted.
Figure 5-24.Using active navigation in Report Index.
2.
Expand the 536015 order number branch (either double-click it or click the arrow ( )) of the tree to reveal the individual line items, then select the second one (i.e., Liszt, Les Preludes). Note that the corresponding line in the Report view is highlighted. Now that weve been introduced to the Report Index and examined the differences between active and passive navigation within it, lets turn to exporting data from the Report window.
Note
The Monarch Help file contains additional information on the Report Index. Refer to Using Report Index in Chapter 2 Reports of the Monarch Help file for more information.
Exporting from the Report Window

Monarch also allows you to export data from the Report window. Unlike the Table and Summary windows, which allow exports to numerous file formats, the Report window only allows exports to a TXT or PDF file. When exporting to a PDF file, if you have configured the Report Index, the tree definition will provide the bookmarks in the PDF file. Lets try exporting the data weve extracted from the Classic.prn file to a PDF file. Since we have configured the Report Index, well get to see how the tree definition provides the bookmarks in the PDF file.
Steps: 1. Select Export from the Home tab to display the General screen of the Export Wizard. The Export Wizard will step us through the process of exporting data from Monarch.
Figure 5-25. The General screen of the Export Wizard.
2.
Click the Next button to display the Export Wizards Output File screen.
Figure 5-26. The Output File screen.
3.
Next button to display the PDF Security dialog.
Figure 5-27. The PDF Security dialog.
For this exercise, lets choose to password-protect the PDF file. 4. Check the box labeled Apply.
5.
Click the Change Open Password button beside the Apply box. The Change PDF Export Open Password dialog displays.
Figure 5-28. The Change PDF Export Open Password dialog.
6.
Enter the password of your choice in the Enter Password and Confirm fields then click OK. The box labeled Save as PDF export open password default allows you to set a default password for opening all future PDF report exports. This default password is the password youve just finished specifying in Step 6. For now, well leave this box unchecked.
Note
If we wanted to, we could specify that the password we provided will be applied to all PDF file exports by clicking the adjacent check box.
7.
Click the Run button. Monarch displays a progress dialog. When the export is done, Export(s) Completed displays at the top of the progress dialog.
Figure 5-29. A completed export.
8. 9.
Click Close to exit the progress dialog. Click the Close button to exit the Export Wizard.
10. Navigate to the default export location (typically, C:\Documents and Settings\All Users\Documents\Monarch\Exports), and then double-click on the Classic.pdf file. A Password dialog displays.
11. Enter the password you specified in Step 6, and then click OK. Your PDF file viewer launches and displays the Classic.pdf file.
Figure 5-30. Viewing the Classic.pdf file.
12. Expand the Classic branch of the tree. Note that the tree definition we created earlier has been used to create the bookmarks for the PDF file.
Figure 5-31. Viewing the bookmarks.
13. Select File and then select Exit to close your PDF file viewer.
Saving Your Work

You have completed Chapter 5. We recommend that you save your work in a Monarch model file.
Steps: 1. 2. 3. Select File, click on the arrow of the Save As Model. menu, and then choose
Type Classic into the File name box of the Save Model As dialog that displays, and then choose Save. Select File, and then choose Exit Monarch.
Summary
In this chapter, you learned how to extract data from each level in a multilevel report. You created a detail template, two append templates, and a page header template. You also learned how to configure the Report Index and export from the Report window. For further reading, see the following sections of the Monarch Help file. Chapter 2 - Reports Creating Data Extraction Templates Creating a Template Creating a Page Header Template Verifying Field Boundaries
In the next chapter, youll learn about a special technique to extract address blocks from a report.
[6] Special Data Extraction Techniques

In Chapter 5, we created a table using a report with multiple sort levels, but we didnt extract the customer information from the highest sort level. In this chapter, well use Monarchs address block feature to extract the customer names and addresses. Well also familiarize ourselves with Monarchs floating trap and multi-column region trapping features. The lesson topics include: Special problems with addresses Extracting an address block Using the address block feature Saving your work Using the floating trap Using the multi-column region trapping feature
Starting the Lesson

To get started, well load the Classic report and Lesson4 model file.
Steps: 1. 2. Select the Monarch item from the Windows Start menu. Open the report Classic.prn. The Classic.prn report displays in the Report window. 3. 4. Select the File tab, click on the arrow of the Open select Model. Select Lesson4.xmod, and then choose Open. The Lesson 4 model file contains the same data extraction templates that you created during Chapter 5. menu, and then
Figure 6-1. The Classic report with the Lesson 4 model.
The customer name and address, account number, and contact name are all at the same sort level in the report. Generally, we define one append template for each sort level. But we need to use a special technique to extract the address fields, and that technique requires a separate template.
Special Problems with Addresses

Most of the information in reports is contained in fields that are easily located for extraction. However, addresses present special problems. Some may contain three lines, while others have as many as six or seven. As well, fields containing state, province, and postal code information are not found at a fixed position on the lines they occupy. Monarch provides a specialized feature in the Data View window to solve these data extraction problems easily. To extract the address fields, Monarch provides the Address Block feature in the Data View window. The Address Block is capable of taking a block of text and extracting and categorizing the address information contained within it.
Extracting an Address Block

The procedure for extracting the address fields is similar to extracting other fields. First, well select a sample address block, then well set a trap to capture all other address blocks throughout the report, and finally, well highlight the address fields to extract. Lets start by selecting a sample address block from the report.
Steps: 1. 2. Click in the line selection area to the left of the line containing CUSTOMER and the first line of the address (i.e., Bettys Music Store). Select the drop-down button of the Templates and then select New tool in the Report tab,
The first line of the address block is copied into the Template Definition dialog.
Figure 6-2. First line of the address block in the Template Definition dialog.
3. 4.
Select Append from the Role drop-down menu to specify that you are creating an append template. Type CUSTOMER in the trap line above CUSTOMER in the sample line.
5.
Highlight the field in the sample line, making sure you allow plenty of space for long address lines throughout the report.
Figure 6-3. Trapping the first line for the address block.
6.
In order to capture the whole address block, regardless of the number of lines, we need to use the advanced field options available in Monarch. To do this, double-click on the highlighted field to display the Field Properties dialog.
Figure 6-4. Field Properties dialog.
7.
Choose the Advanced tab then, under the End Field On heading, select the Blank Field Values radio button to terminate the field when a blank line or a series of blank lines is encountered. It is important to note that Monarch does not require the entire line to be blank, only the column positions occupied by the field. Enter the number of blank field values required to terminate the field, which in this case is 1. Return to the General tab, change the Name to Customer Full, and change the fields Type setting to Memo.
8. 9.
10. Click OK.
The address fields in the report are highlighted. Scroll through enough of the report to satisfy yourself that all instances of the address have been captured.
Figure 6-5. Viewing the trapped address block.
Note that this has correctly captured all of the addresses, even though some have four lines and some have five. This is the advantage of using the Advanced Field properties. 11. Replace Append 4 with Customer Address Level in the Name box of the Template Definition dialog, and then click the OK button to accept the template.
Using the Address Block Feature

When you open the Data View window, you will see that the field has been extracted and is displayed using standard memo field behavior. Now we can use the address block feature to extract the data from the block of text we have extracted.
Steps: 1. Select the Table tab, click on the drop-down arrow of the Address Blocks menu, and then select New. The Address Block Wizard opens and displays the Name and Postal Code Formats screen.
Figure 6-6. The Address Block Wizard.
2. 3.
Enter Address1 in the Name field, and then click the Next button to view the Input Fields screen. In the Available Fields list, select Customer Full, click the Add >> button to add the field to the Selected Fields list, and then click the Next button to view the Output Fields screen. Select the Address Line 1 check box. Replace the Address1Line1 entry in the Output Field Name box with Customer.
4. 5.
6.
Select Address Lines 2 and 3, City, Region, Postal Code, and Country, and rename the fields as follows: Address1 Address2 CityAddress StateAddress ZipAddress CountryAddress
7. 8.
Click the Finish button after all the fields have been named. Display the Data View window, and then scroll right to display the address fields. Select Autosize Columns to display all fields properly.
Figure 6-7. Extracted addresses in the Data View window (column widths adjusted to show all seven address fields).
Note that Monarch has extracted all the information contained in the original address block and categorized it correctly into the defined fields. The final step is to hide the original field we used to extract the address block information, Customer Full. 9. from the View group of the Table tab to Select Hide/Display Fields view the Hide/Display Fields dialog.
10. Select the Customer Full field in the Displayed Fields list then click the Hide button. Note that it now appears in the Hidden Fields list.
Figure 6-8. The Hide/Display Fields dialog box.
11. Click OK to close the Hide/Display Fields dialog, and examine the table. As you can see, the Customer Full field is no longer visible.
We will cover hiding fields more thoroughly in the next lesson. As you can see from the table, the Address block is a simple to use but powerful feature, capable of extracting information from addresses around the world.
Saving Your Work

You have completed the section on extracting data from addresses. We recommend that you save your work in a Monarch model file.
Steps: 1. 2. 3. 4. Select File, click on the arrow of the Save As Model. menu, and then select
Type Classic in the File name box of the Save Model As dialog that displays, and then choose Save. Choose Yes to overwrite the existing model. Select File, and then click Close All to close the report and model files.
Using the Floating Trap

In addition to the address block feature, Monarch has another special data extraction feature called the Floating trap. Lets take a few moments to learn about this powerful feature. In many reports, particular fields of data, such as names and addresses, occur in consistent locations (i.e., they begin at the same horizontal position throughout the report). For example, in the following report, note how all of the first names begin at the same horizontal position, as do all of the last names, streets, cities, etc.
Figure 6-9. A report showing consistent data placement, as is typical of many reports.
Because of the consistent placement of data within the report, defining a trap to capture each occurrence of any one of these fields would be quite easy. With some reports, however, a standard trap wont work because data placement within them is irregular rather than consistent. In the following report, for example, note how the occurrences of the date/time field ("[23/Aug/2008...") begin at different horizontal positions.
Figure 6-10. A report showing inconsistent placement of the date/time data.
In reports such as this, a standard trap will not work. Fortunately, Monarch includes a special trap called the floating trap, which can successfully extract data from many log files, reports and HTML files in which the data placement is not fixed.
Using the Floating Trap to Capture Lines

To familiarize ourselves with the floating trap, lets open the above report and create a floating trap to capture the date/time fields it contains.
Steps: 1. Open the Weblog.prn report. Monarch displays the Weblog.prn report in the Report window.
Figure 6-11. The Weblog.prn report.
2.
Click in the line selection area to highlight the first detail line in the report, as in Figure 6-12.
Figure 6-12. Highlighting the first detail line in the report.
3.
In the Report tab, Templates group, click the drop-down button of the Templates menu, and then select New. The Template Definition dialog displays.
Figure 6-13. The Template Definitions dialog.
4. 5.
From the Role drop-down list, select Detail to specify that you are creating a detail template. In the Trap line, enter a left bracket directly above the left bracket in the sample edit box (i.e., at horizontal position 28), as in the following figure.
Figure 6-14. Entering a left bracket in the Trap box.
Note how the background color of the Trap line changes from white to yellow. This indicates that the trap correctly matches a portion of the sample in the sample edit box. (This feature provides a convenient visual clue that a trap "works".)
6.
In the sample edit box, highlight the date/time field.
Figure 6-15. Highlighting the date/time field.
7.
Scroll down through the report and preview the template to see if the date/time fields will be captured properly. Notice that only some of the date/time fields (12 of 916, to be exact) will be successfully captured. This is because with a standard trap, for fields in a report to be captured properly, they must begin at the same horizontal position that the character in the Trap line does. In this report, only 12 of the 916 fields begin at the correct horizontal position, so only those 12 will be captured.
Figure 6-16. Previewing the template.
Now that we see why a standard trap wont work with a report that contains data in random locations, lets see how the floating trap would handle this situation. 8. 9. Click on the highlighted field in the sample edit box and then select Reset Fields . Select the Floating Trap check box on the Template Definition dialog toolbar.
10. In the sample edit box, highlight the date/time field once more.
11. Scroll down through the report once again. Notice that all of the date/time fields are now highlighted, indicating that they will be successfully captured. The floating trap works by looking across the lines to find the trap characters horizontally, rather than vertically, as in a standard trap. The trap then uses the characters to decide when fields can possibly start and finish. The field we have chosen to trap has a fixed width, but we will explore fields that have variable widths later on in the lesson. In this example, we have only defined that the field should begin after the bracket character, but we could define a close bracket to dictate where the field ends.
Figure 6-17 Previewing the template again. Note that all of the date/time fields are now highlighted.
12. Double-click on the highlighted date/time field in the sample edit box to display the Field Properties dialog.
13. In the Name field, enter Date/Time to name the field, and then click the OK button to close the Field Properties dialog and return to the Template Definition dialog.
14. On the Template Definition dialog, replace Detail with Date/Time in the Name field to name the template, and then click the OK button. Monarch closes the Template Definition dialog and returns to the Report window. 15. Expand the Table node on the Document Explorer, and then click Data View to view the Date/Time information in the Data View window. Note that all 916 date/time fields within the report have been successfully extracted.
Figure 6-19. Viewing the Date/Time table (scrolled to the bottom).
Now that weve seen how the floating trap can define lines where the trap character appears, lets explore another one of its capabilities.
Using the Floating Trap to Define Fields

In addition to defining lines that contain the trap character, the floating trap can be used to define the width of variable length fields. If we return to our Weblog.prn report, we can see how this works.
Steps: 1. 2. View the Weblog.prn report in the Report window. From the Templates group in the Report tab, select the drop-down button of the Templates menu, click on Date/Time from the template choices that display, and then select Edit. We now want to extract the data from the first field, which has a variable width. In order to do this, we can use a trap character to tell Monarch where the field begins and ends. Since this field is left justified, we only need to be concerned with where the field ends.
3.
Position the cursor in the Trap line at Position 23, click the Blank Trap button, then highlight the text in the sample line from position 1 through Position 22, as in Figure 6-20.
Figure 6-20. Floating Trap with the blank trap added.
Note the results in the Report window portion of the dialog.
Figure 6-21. Floating trap with variable width fields.
As you can see, the fields have been highlighted and the highlighting has shrunk to accommodate any field shorter than the fields we originally selected. There are some longer fields, however (e.g., PPPa28Resale_Vancouver"), in which the field is wider than the highlighting. This would result in an incomplete extraction of data. In order to rectify this, we need to manually specify the length of this field. 4. To do so, double-click on the field you highlighted to bring up the Field Properties dialog. Here we need to adjust the template and display widths to accommodate longer data than we can define visually.
5.
Enter 60 for the Display and Template widths.
Figure 6-22. Specifying the Display and Template widths.
6.
Press OK to accept the sizes then view the result. Note that all of the data is now trapped correctly.
Figure 6-23. Viewing the finished floating trap.
7.
Click the Cancel button to return to the Report window, then select File and then click Close All to reset the Monarch session (select No when prompted to save changes to the model).
Using the Multi-Column Region (MCR) Trapping Feature

When working with Monarch, you may occasionally encounter a report that has a layout similar to that of a newspaper, i.e., one with multiple, adjacent columns of data on each page. For example, the following report contains a list of composers, numbered 1 through 90, and the list is broken into three columns, each containing thirty names.
Figure 6-24. Viewing a report with multiple columns.
To handle such reports, Monarch includes a Multi-column region (MCR) trapping feature which you can use to trap the data in multiple columns via only one template. Extracting data via the MCR trapping feature consists of three simple steps. First, you define the multi-column region via the Multi-Column Region Definition dialog, then you create a template to extract the desired data from the report, and finally you specify the vertical boundaries (i.e., beginning and ending points) for the columns. Lets open a multi-column report and extract data from it via the MCR trapping feature.
Steps: 1. Open the Composers.prn report. The Composers.prn report displays in the Report window.
Figure 6-25. The Composers Multi Column report.
As you can see, this report contains the same list of composers we saw a moment ago. Composers.prn lists the names of ninety composers, arranged in three columns of thirty names, and ranked in order of popularity (1 being the most popular, 90 being the least popular) for each customer. Now that our multi-column report is open, lets define the multi-column region. 2. Select Multi-Column Region Report tab. from the Templates group of the
The Multi-Column Region (MCR) Definition dialog displays. 3. Select the Multi-Column Region is active check box to activate the dialog.
Figure 6-26. The Multi-Column Region Definition dialog with a checked MultiColumn Region is active box.
Now we need to examine the report to determine the number of columns we want the multi-column region to contain. This number should obviously equal the number of columns in the report which, in this case, is three. 4. Enter 3 in the Number of Columns box, and then select OK. A series of red, vertical broken lines or column indicators appears over the report to indicate where the columns currently are. The number of columns you specified determines the number of column indicators that appear. Since we entered 3 in the Number of Columns box, three column indicators have appeared, as displayed in Figure 6-27.
Note
To view the rightmost column indictor, you may need to enlarge the Monarch window or select a smaller font size.
Figure 6-27. Viewing the column indicators.
Next, we need to specify a left margin for the leftmost column, i.e., specify the position where we want the leftmost column to begin. Since the text in the leftmost column begins at the left edge of the report, the current setting of 1 is the one we want, so we can leave it as is.
Note
If the text in the leftmost column began one space in from the left edge of the report, then we would enter 2 in the Left Position of First Column box. If the text began two spaces in from the edge of the report, we would enter 3 and so on.
Now we need to adjust the width of the column indicators to match the width of the data columns in the report. 5. 6. Repeat Step 2 to display the Multi-Column Region (MCR) Definition dialog once more. In the Width of Each Column box, adjust the value until you obtain the correct width. For this report it is 29. The column indicators move to the left to reflect the change to the column width. Note that all three columns of composer names and ranking numbers now fall within the column indicators (scroll down to see this, if necessary).
7.
Click the OK button to accept the current MCR settings and close the dialog. The Report window displays. Note that the column indicators are still displayed on the Report window.
Figure 6-28. Viewing the column indicators in the Report window.
Now that we have determined the necessary number of columns, and have specified their width and the starting position of the leftmost column, were ready to define a template to extract the data.
Creating a Template to Trap the Data

The process of trapping data in a multi-column region report is the same as that for any other report. The first step is to select a template sample.
Steps: 1. Click in the line selection area to select the top line of composers names (i.e., the one beginning with PACHELBEL, J.).
Your screen should look as in Figure 6-28.
Figure 6-29. Selecting the top line of composers.
2.
Select the drop-down button of the Template group of the Report tab, and then select New.
menu in the Templates
Monarch splits the Report window into the Template Definition dialog, at the top, and the report, at the bottom. The selected detail lines are displayed in the sample edit box at the bottom of the dialog.
Figure 6-30. Viewing the template sample.
Note that the Multicolumn Design Mode check box is selected. In addition, note that of the three composers included in the first line of composer names (i.e., the line that we selected in Step 1 as the template sample), only PACHELBEL, J. is displayed in the Sample line; MOZART, W.A. and WEELKES, T. are not displayed. This is because, based on the MCR settings weve specified, Monarch now considers the three columns as one. If you were to uncheck the Multicolumn Design Mode check box, all three names would appear in the sample line.
3. 4. 5.
Select the Role drop-down button, and then click Detail to specify that this is a Detail template. In the Trap line, place the cursor above the P of Pachelbel in the sample edit box, then click the Non-Blank Trap then click the Numeric trap button. button. In the Trap Line, place the cursor above the 1 in the sample edit box, Notice that the columns of composer names are now highlighted in the report window, and that nothing else is highlighted.
Figure 6-31. Viewing the results of the non-blank and numeric traps.
6.
In the sample edit box, highlight the composer name and then highlight the number 1.
Note
When highlighting the composers name, extend the highlight well beyond the end of the name, as some of the composers names are longer than the one in the sample edit box. Likewise, when highlighting the number 1, be sure to begin the highlight at least one space before the number, as the numbers extend into the double digits. See Figure 6-32 for an example of how you should highlight the fields.
Figure 6-32. Highlighting the fields.
7.
Double-click on the composer name in the sample edit box to display the Field Properties dialog, type Composer in the Name field on the General tab, and then choose OK. Double-click on the number 1 in the sample edit box, type Rank in the Name field on the General tab of the Field Properties dialog that displays, and then choose OK. In the Template Definition dialogs Name field, replace Detail with Composers, and then click the OK button. Monarch redisplays the Report window. Note that the composer names and rank numbers are successfully highlighted.
8.
9.
Figure 6-33. Viewing the results of the Composers template in the Report window.
Now lets create an append template to extract the CONTACT data from the report. This template will also help us with specifying our vertical boundaries for the MCR. 10. Click in the line selection area to highlight the first CONTACT line (i.e., the line containing CONTACT: Betty Yoder).
Figure 6-34. Selecting the template sample.
11. Select the drop-down button of the Templates group of the Report tab, and then select New
menu in the Templates
Monarch displays the Template Definition dialog. The selected template sample is displayed in the sample edit box at the bottom of the dialog. 12. From the Role drop-down menu, select Append. 13. Type CONTACT in the Trap line above CONTACT in the sample line, then highlight Betty Yoder in the sample line.
Note
Be sure to extend the highlight well beyond the end of the last name, as in Figure 6-35.
Figure 6-35. Highlighting the Contact field.
14. Lets name the contact field. To do so, double-click on Betty Yoder in the sample edit line to display the Field Properties dialog, type Contact in the Name field, and then click the OK button. 15. Type Contact in the Template Definition dialogs Name field to name the template, and then click the OK button. If we wanted to, we could now create additional append templates to capture more information from the report, such as the CUSTOMER and ACCOUNT NUMBER fields. Instead, lets move on and learn how to specify vertical boundaries for the MCR.
Note
As an example of a model file that contains additional append templates, Monarch includes a Composers.xmod file in the Monarch Models folder.
Specifying Vertical Boundaries for the MCR

So far, we have configured the column indicators to the correct width and have created a detail template that extracts the composers names and rank numbers from the report. We also created a Contact append template, which will be helpful to us when specifying vertical boundaries for the multi-column region.
Note
When we specify vertical boundaries for the MCR, we specify the top and bottom boundaries for the columns within it.
Lets see how this is done.
Steps: 1. 2. 3. Scroll to the top of the report. from the Templates group of the Select Multi-Column Region Report tab to display the Multi-Column Region Definition dialog. Under the Boundaries (vertical) heading, select the Start after > Template radio button option, and then ensure that Contact is selected from the Template drop-down list. A horizontal broken line appears at the top of the composer columns, just below the CONTACT line. This line indicates the top boundary of the multi-column region. Everything above this line will be excluded from the MCR.
Figure 6-36. Viewing the top boundary of the MCR (just below the CONTACT line).
4.
Scroll down through the report to locate the bottom boundary of the multicolumn region. Under the Boundaries (vertical) heading on the Multi-Column Region Definition dialog, note that the current bottom boundary setting for the MCR is Stop at > Bottom of page. Notice that the bottom boundary of the MCR occurs just after the end of the columns on Page 1 and before the beginning of Page 2, as specified by the current bottom boundary setting. This boundary will suit our purposes as is, so we dont need to adjust it.
Figure 6-37. Viewing the bottom boundary of the MCR.
Now that youve seen how helpful templates can be when specifying the top and bottom boundary settings of the MCR, keep this in mind, as you can create blank traps solely for this purpose. 5. Click the OK button to save the current MCR settings. Monarch redisplays the Report window. Lets view the trapped data in the Data View window. 6. 7. Expand the Table node in the Document Explorer, and then select Data View. Select Autosize Columns to view all of the fields properly.
Monarch displays the extracted data in the Data View window.
Figure 6-38. Viewing the extracted data in the Data View window (column widths adjusted to show column names).
Saving Your Work

You have completed the section on using the MCR feature to extract data from multi-column reports. You may save your work in a Monarch model file. To do so:
Steps: 1. 2. 3. Select File, click on the arrow of the Save As Model from the options that display. menu, and then select
Type an appropriate name (e.g., MultiColumn) in the File name box of the Save Model As dialog that displays, and then choose Save. Select File, and then choose Exit Monarch to end the Monarch session.
Summary
In this chapter, you learned how to use the address block to capture fields from address blocks that contain a varying number of lines. You also learned how to use the floating trap to extract data from irregularly formatted reports and to define the width of fields. Lastly, you learned how to extract data from multi-column reports using the MCR trapping feature. This lesson concludes our exploration of the Report window. For further reading, see the following sections of the Monarch Help file: Chapter 2 - Reports Creating Data Extraction Templates Creating a Template Capturing Address Blocks with Varying Lines Using the Floating Trap Using the Multi-Column Region Trapping Feature
The next five chapters are devoted to the Data View window, where you can explore and manipulate the report data and export it to other applications.
[7] Working in the Data View Window

In this chapter, you will learn how to work in the Data View window. The lesson topics include: Naming fields Assigning a field type Format adjustments
This chapter assumes that you are familiar with opening and viewing report files and creating data extraction templates. Before you begin this lesson, we recommend that you complete Chapters 4, 5, and 6.
Starting the Lesson

To get started, well open Classic.prn and Lesson5.xmod.
Steps: 1. 2. 3. 4. 5. 6. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open Report. Open Classic.prn. Select File, click on the arrow on the Open Model. Open Lesson5.xmod. Expand the Table node on the Document Explorer, and then select Data View. Monarch generates a table based on the Classic report and the Lesson 5 model. The resulting database displays in the Data View window. menu, and then select menu, and then select
Figure 7-1. The Monarch Data View window.
When Monarch extracts data from a report, the data is placed in a table, which you can view and manipulate in the Data View window. You can filter and sort the data, and export it to popular PC applications. You can also use the Data View window together with the Summary window to analyze the data. This lesson will show you the basics of navigating and working in the Data View window. Later lessons describe how to use Monarchs built-in expressions to calculate new fields, create filters, and create sort definitions in the Data View window.
Naming Fields
Monarch automatically assigns default field names to each field that you highlight, using the prefix "eg_ and the sample data from the first record encountered. You can keep the default names, or you can assign new names that are more meaningful. For this lesson, weve named all but the first five fields to reduce the amount of repetitive work youll have to do within the lesson. To name the first field:
Steps: 1. Double-click any cell in the first field (column). The Field Properties dialog displays.
2.
Type Report Date in the Name box.
Note
Field names may be up to 62 characters in length and may contain uppercase and lowercase characters, spaces, and punctuation marks. However, periods (.), exclamation points (!), accent graves (`), and brackets ([ ]) may not appear in any part of the name. As well, names may not begin with spaces or underscores (_). If a name is entered with leading spaces, the name is accepted but the leading spaces are trimmed.
3.
Choose OK. The field name Report Date appears at the top of the first column.
4.
Repeat Steps 1 through 3 to name the second field, using Page as the name.
Quick Naming
If you have a lot of fields to name, you may find the Field List dialog more efficient.
Steps: 1. On the Home tab, Data Definitions group, select Field List The Field List dialog displays 2. 3. 4. 5. In the Field Name column, highlight the field named eg_536017, type Order Number, and then press ENTER on your keyboard. Highlight the next field (the field named eg_4/06/2010), type Ship Date to name this field, and then press ENTER. Highlight the next field (the field named eg_11887), type Account Number to name this field, and then press ENTER. Close the dialog by clicking on the Close button on the upper right-hand corner of the box. .
NAMING TIP
Sometimes the contents of the fields can be obscure, making identification and naming difficult. For example, its not immediately obvious that 5/1/2008 is the report date it could just as easily be the ship date. Heres a way to make naming easier:
Steps: 1. While both Data View and Report window tabs are open, click on the Data View tab, and then, without releasing the mouse, drag the tab to the center of the Monarch work area. Docking tabs similar to those discussed for the Document Explorer display.
Figure 7-3. Docking tabs for the Data View window.
You can refer to Chapter 3 Working in the Report Window The Document Explorer for more information on the docking tabs. 2. Dock the Data View tab such that it displays below the Report window tab.
Figure 7-4. Comparing values in the Data View and Report windows.
By comparing values in the two windows, you can often identify the fields quite easily. In the example cited above, with both windows open, you can see that 5/1/2010 in the Data View window corresponds to the report date in the upper left corner of the report (05/01/10). Note that when you select a field, Monarch displays the field name, field type, and the template name and type on the status bar at the lower lefthand corner of the window. 3. Because our focus is only on the Data View window, right-click on the Classic.prn tab and then select Close. The Report window closes and the Data View window remains.
Format Adjustments
As weve seen, Monarch automatically formats the data as it builds the table. The column width is also set for each field in order to display the widest field value. You can override these automatic assignments, and you can make several other format adjustments, as discussed in the next few sections. You will learn how to: Fill empty cells Change field widths Move fields Hide fields
Filling Empty Cells

Sometimes the report data includes assumed dittos. An example would be the Media column shown in the Data View window of Figure 7-5 (simply scroll to the right in your Data View window to view these fields).
Figure 7-5. An assumed ditto in the Report window.
In this list, the media identifier CD obviously applies to the first three entries in the list, rather than just the first entry, even though it is only shown explicitly next to the first entry. When these entries appear in the table (Records 13 in the Data View window of Figure 7-5), the CD is still missing from two of the entries.
Monarch has an easy solution to this:
Steps: 1. Double-click any cell in the Media field. The Field Properties dialog displays. 2. Change the current Empty Cells setting by selecting Copy from previous record, and then click OK.
Figure 7-6. An assumed ditto in the Report window.
Monarch fills in the blank cells in that field (see Figure 7-7).
Figure 7-7. Blank cells are no longer present in the field.
Adjusting Field Widths

If you examine the beginning of the table (Line 1, scrolled all the way left), you will note that the Report Date, Order Number, and Account Number columns, among others, are not wide enough to display the entire field name (see Figure 7-7). Even though the field values are not truncated, you may want to widen the columns.
Figure 7-7. The widths of the fields Order Number and Account Number are too narrow.
To adjust the column width:
Steps: 1. Double-click on any cell in the Order Number field. The Field Properties dialog displays. 2. Enter 12 in the Display Width box, and then choose OK.
You can do this even faster using the mouse:
Steps: 1. 2. Move the mouse pointer to the right edge of the Page field title. The mouse pointer will become a resizing handle. Click down and drag left or right to resize the field.
For the rest of the fields, well use Monarchs Autosize Columns feature to adjust the field width.
Steps: 1. Select Autosize Columns from the View group of the Table tab.
All the fields are resized to match its widest field value or the field name, whichever is wider. The table now looks like Figure 7-8.
Figure 7-8. The Account Number column width increases.
button resizes all the columns in the table, The Autosize Columns not just the column you selected. If you reduce the width of a character field or a date field to less than the length of a field value, the field value is truncated on screen. If you reduce the width of a numeric field to less than the width of a field value, the field value is truncated as well.
Note
The column width setting affects only the appearance of the data on screen and in printed output. It does not affect the underlying data.
Moving Fields
The order of fields in the table is purely a function of their order in the report file. Therefore, you may frequently want to rearrange the order of your data in the table. You can rearrange the order of fields in the table via the Field List dialog, or by dragging and dropping fields in the Data View window. To move a single field, position the cell pointer anywhere in the field. To move a range of fields, click down anywhere in the first field and drag left or right to select the desired range. Lets move the Order Number and Ship Date fields to the right of the Account Number field using the drag-and-drop feature.
Steps: 1. 2. Click on the column heading of Order Number and drag your mouse to the right to highlight the columns Order Number and Ship Date. Click on either of the two highlighted column headings but do not release it. Drag your mouse to the right, ending at the right side of the column heading Account Number, and then release.
Figure 7-9. The Field List dialog.
When you do so, two things are noticeable: 1) the column headings Order Number and Ship Date float over the columns, and 2) indicator arrows appear where you intend to move the fields (marked as in Figure 7-9). The fields you selected appear to the right of the Account Number field.
You can also move fields using the Field List dialog. The Field List dialog includes the column Display Order. You can change the column order of your table by simply renumbering the column order and then closing the dialog. Lets try doing this. Well move the columns Order Number and Ship Date back to their original positions.
Steps: 1. In the Home tab, Data Definitions group, select Field List The Field List dialog displays. .
In the original table, Order Number and Ship Date occupied columns 3 and 5, respectively. In the Field List dialog, they occupy column positions 4 and 5. Lets change them to the original positions. 2. Locate the field name Order Number, click after the number on the Display Order row to its left, press backspace once so the number 4 is deleted, and then type in 3. Press ENTER when you are finished to accept your changes. Repeat Step 2 above to change the display order of Ship Date to 4. You will notice that the field Account Number now has a display order of 5. 4. Exit the Field List dialog. The table is rebuilt to show your changes. Note that the columns Order Number and Ship Date are back in their original positions.
3.
Hiding Fields
Frequently, you may want some data hidden from view but not deleted from the table.
Note
Hidden table fields are not available for export and copy operations (see Chapter 8).
For example, the Report Date and Page fields contain useful information, but you may not want them visible while working with data from the other fields. Lets hide these fields. To do so:
Steps: 1. Select Hide/Display Fields from the Table tab, View group.
The Hide/Display Fields dialog displays.
The Hide/Display Fields dialog includes two columns: a Displayed Fields column, showing all displayed fields, and a Hidden Fields column, showing all hidden fields. 2. Select the Report Date field from the Displayed Fields column, and then click Hide >>. The Report Date field is displayed in the Hidden Fields column. 3. Select the Page field from the Displayed Fields column, and then click Hide >>. The Page field also displays in the Hidden Fields column. 4. Click OK to close the dialog box.
The selected fields are now hidden (see Figure 7-12).
Figure 7-12. The Report Date and Page fields are now hidden.
Finding Information in a Table

During the course of working with Monarch, you may need to locate information in the Data View window. Monarch presents an easy way to do this via the Search panel, which may be accessed by clicking Search in the View group of the Home tab. The Search panel for the Data View window varies significantly from the Search panel for the Report window in that it includes a search option for expressions.
Figure 7-13. The Search panel for the Data View window.
Since we already know how to do basic keyword searches, lets try searching through the Data View window using the Expression Builder. Before we do so, ensure that the first field of the first column is selected, signaling the beginning of the table.
Steps: 1. Select the Search button from the Home tab, View group.
The Search panel displays to the right of the Data View window. 2. 3. Check the box for Use Expression. Click on the link Expression Builder to launch the Expression Builder dialog box.
Figure 7-14. The Expression Builder dialog box.
Lets say we want to search for all records with Amount values greater than or equal to $100. 4. In the Fields list, select Amount and then click Insert. The word Amount displays on the Expression box. 5. 6. 7. Select >= from the Operators list and then click Insert. In the Expression box, ensure that the cursor is located after the operator, and then type in 100. Click OK to close the dialog box. The expression you just built displays on the search field of the Search panel. 8. Click the Search icon.
Record 49 in the Data View window is highlighted. If you scroll all the way to the right of this record, you will see that it has a total amount of 307.20. A quick look through all other amounts preceding Record 49 shows no other record with an amount greater than or equal to 100.
Figure 7-15. Record 49 is highlighted, signifying the first record for which the expression Amount >= 100 is true as a search parameter.
9.
Click the Search
icon once more.
This time, Record 78 is highlighted, signifying the second record with an Amount value greater than or equal to 100. 10. Close the Search panel by clicking on the Close upper right-hand corner of the panel. button located on the
Assigning the Field Type

When Monarch builds a table, it automatically assigns a field type to each field based on the first instance of that field in the report. If Monarch finds a number when it first extracts a field, it assigns numeric format, etc. Usually, the first instance of a field is representative of other instances, but when it is not, you must manually override the assigned format to assign the proper format. In our lesson, Monarch has made an incorrect format assignment. An examination of Records 28, 29, and 30 reveals null entries in the Order Number field. Monarch provides an easy way to investigate the source of this problem.
Displaying the Source of a Record

To locate the source of Record 28, well use the Find in Report button.
Steps: 1. Scroll down in the Data View window until Row 28 is visible. Notice that Record (Row) 28 has a value of null entered in the Order Number column. 2. In the Table tab, View group, select Find in Report .
The Report window displays, showing the report for Record 28 in the table. The line from which the detail fields were extracted is highlighted (see Figure 7-16).
Figure 7-16. The details for Record 28 in the Data View window are located in the Report window.
When we examine this particular order in the Report window, we see that the Order Number field contains a Return Authorization code, which begins with the letters RA.
Note
Using the Find in Table
Button
You can also locate the record number (table record) of any item within a report, along with pertinent information regarding the item (the amount of information you can obtain depends on the templates youve set up) from the Report window. To do so, click on the detail item of interest in the Report window and then select Find in Table from the View group of the Report tab. The Data View window displays the record number, as well as other information, for the detail item.
Now, lets examine the properties of the Order Number field in the Data View window. 3. Return to the Data View window and double-click on Record 28 in the Order Number field that contains the value null. The Field Properties dialog displays.
Note that a field type of Numeric was assigned to this field because the first instance of the field contained only numbers. Since we have discovered that a later instance includes the characters RA, we must override the automatic assignment and specify a Character field type.
4. 5.
Click the down-arrow on the Type box and select Character as the new field type, then click OK. Scroll back to Row 28 to confirm that the field values are displayed. The Order Number fields on Lines 28, 29, and 30 now show the correct values (see Figure 7-18).
Figure 7-18. Viewing the correct values in Rows 28-30.
Saving Your Work

You have just completed Chapter 7. We recommend that you save your work in a Monarch model file.
Steps: 1. 2. 3. 4. Select File, click on the arrow on the Save As Model from the options that display. menu, and then select
Type Classic in the File name box of the Save Model As dialog that displays, and then choose Save. Choose Yes to replace the existing model. Select File, and then click Exit Monarch.
Summary
In this chapter, we modified some characteristics of the table and adjusted Monarchs automatic formatting to more closely fit our needs for this table. For further reading, see the following sections of the Monarch Help file: Chapter 3 - Tables Viewing the Table Searching the Table Working with Fields
Many other operations are possible within the Data View window, including filtering, sorting, exporting, copying, and printing. The next few chapters will cover these subjects.
[8] Printing, Copying, and Exporting

In this chapter, you will learn how to print, copy, and export data from the Data View window. The methods described in this chapter also apply to data in the Summary window. The lesson topics include: Printing table data Copying data to other applications Exporting data to a file Adding data to an existing file
This chapter assumes you are familiar with importing and viewing report files, creating data extraction templates and working in the Data View window.
Starting the Lesson

To start the lesson, load Monarch and open Classic.prn and Lesson6.xmod.
Steps: 1. 2. 3. 4. 5. 6. 7. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open Report from the options that display. Open Classic.prn. Select File, click on the arrow of the Open Model from the options that display. Open Lesson6.xmod. Expand the Table node in the Document Explorer and then select Data View to open the Data View window. Click Autosize Columns to view all the fields properly. menu, and then select menu, and then select
The Data View window displays the records created from the Classic report and the Lesson 6 model. For this lesson, weve extracted most of the detail fields and a single field from the highest sort level in the report.
Figure 8-1. Extracted data displayed in the Data View window.
Printing Table Data

The table is usually much wider than the report as Monarch combines fields from various levels in the report to create each row of the table. When Monarch prints data from the table, it prints as many fields as will fit across the page. If all of the fields will not fit on a single page, the remaining fields are wrapped to the next page. The amount of information that will fit on each page is determined by several factors: The font size has a dramatic effect on the amount of data that fits on each page. To fit all the fields across the page, you may need to select a smaller font size. The Paper orientation setting specifies the direction that text will print. For wide tables, set the paper orientation to Landscape to print the text sideways on the page. Margins affect how close text is allowed to print to the edge of the page. You can use the margins to make small adjustments to the amount of text that will fit on each page. Monarch lets you print just the data you need. You can print the entire table or any selection from the table. Lets print the records for a single customer, Bettys Music Store.
Adjusting the Font Size

Monarch uses the same font and font size for both displaying and printing. Lets select a smaller font size to ensure that all of the fields fit across the page.
Steps: 1. Click the down arrow on the toolbars Font Size box in the Table tab and select 8. If the 8 point size is not available for the current font, use the toolbars Font box to select a different font.
Page Setup Options

Several page setup options are provided that you can use to establish margins, add a page header to each page, print column titles and row numbers, and print the grid lines that you see on-screen. To edit the page setup options, well use the Page Setup dialog.
Steps: 1. Select File, and then click Page Setup The Page Setup dialog displays. 2. Select the Data View tab. .
Figure 8-2. The Page Setup dialog.
The dialog displays two tabs, one for reports and another for tables. Because we are printing a table, the dialog box opens with the Data View tab in focus. The tab shows the current margin settings and print options for the table. 3. Ensure that the Show Row Numbers box is unchecked to turn off the row numbers in our printout.
CREATING A PAGE HEADER

Monarch allows you to add a descriptive title to the top of each printed page via the Page Header dialog. The topmost section of the Page Setup dialog for tables is labeled Page Header. It is divided into three sections, Left, Center, and Right, all of which represent locations in which you may want to add information to your header. The Page Header provides several functions that you can use to align text and dynamically add information to the page header, including page numbers, the print date and time, the names of the active filter and sort, as well as the First Value of any field in the table. You can also add any text you wish, and even specify the font to use in the header.
Steps: 1. Double click on the first cell of the column labeled Left. A menu options arrow displays on the cell.
Figure 8-3. Adding a page header to the printout.
Note
Refer to the topic Printing Table Data, Setting Margins and Print Options in Chapter 3 Tables of the Monarch Help file for a complete list of the available functions.
Note
The Page Header can accept up to three lines of text.
2. 3. 4. 5. 6. 7. 8. 9.
Click the arrow that appears and then select Date from the options that display. Double-click on the first row of the column labeled Center. Type Classical Music Distributors into the cell. Double-click on the first row of the column labeled Right. Type Page into the cell, followed by a space. Click the arrow on the cell and then select Page Number from the options that display. Double-click on the second row of the column labeled Left. Type Report: followed by a space, and then click the arrow on the cell. Select Input Name from the options that display.
10. Double-click on the third and last row of the column labeled Left. 11. Type Model: followed by a space, and then click the arrow on the cell. Select Model File Name from the options that display. This is how the Page Header section of your Page Setup dialog should look when you are finished.
Figure 8-4. A fully set up page header.
12. Click OK to accept your changes and close the Page Setup dialog. Here is an example of how the page header might appear on your printout. 04/01/2011 Report: Classic.prn Model: Lesson6.xmod Classical Music Distributors Page 1
Selecting and Printing the Data

Now lets select and print the first page of the report. Before printing, lets use the Print Preview feature to preview the effect of the font and print settings.
Steps: 1. Select File, and then click on the arrow of the Print Print Preview from the options that display. menu. Select
The Print Preview window displays.
Figure 8-5. The Print Preview window (zoomed in).
Examine the Print Preview window to see if your settings for font size, margins, page orientation and page header are appropriate to produce satisfactory output. If not, you can change the settings until everything looks just right. For now, lets see how the Print Preview window works. 2. Use the Zoom In/Zoom Out bar located at the lower right-hand corner of the Preview window to zoom the table in or out. You could also use the Zoom button located on the upper left-hand corner of the Print Preview window.
Note
Windows sometimes substitutes fonts in the Preview window, causing columns to appear misaligned. Columns will align correctly in the actual printout.
When viewing more than a single page, you can use the Next Page and Previous Page 3. buttons to move forward or back a page at a time.
Click the Close button to return to the Data View window. Finally, lets print the first page of the table.
4.
Select File, and then click on the arrow of the Print Print from the options that display.
menu. Select
The Print dialog displays.
Figure 8-6. The Print dialog.
5.
Select the printer you would like to use to print the first page of the report if it has not been selected yet. In the above screenshot, for example, the printer Samsung ML-1610 Series was selected. In the Page Range section, select the button for Pages, and then type the number 1 in the box beside it. Choose OK to print the page. The first page of the report is printed.
6. 7.
Copying Data to Other Applications

You can use the Windows clipboard to copy table data to other applications. When you copy data to the clipboard, Monarch creates both a text image and a worksheet image (with separate rows and columns). When you subsequently paste the data to another application, the application selects the format it needs. Lets copy the records for Bettys Music Store to your spreadsheet.
Steps: 1. If the first 9 records in the table arent still selected, select them by clicking down on the row selector to the left of Record 1, then dragging down to Record 9. Click Copy on the Clipboard group of the Home tab.
2. 3.
Launch your spreadsheet. (If you dont have a spreadsheet, just follow along in the book.)
4.
Position the cell pointer where you want the data to appear and use your spreadsheets Paste command to paste the data. For some spreadsheet applications, you may need to use the Paste Special command. Adjust the column fields as necessary to view all of the data.
5.
Figure 8-7. Pasting to a spreadsheet.
6.
Adjust column widths and cell formats in your spreadsheet as necessary.
Note
Monarch does not set the column widths automatically because this might alter an existing worksheets column widths when the data is pasted. If the spreadsheet that you are pasting into is empty, you may prefer to export the data using the Export command. When data is exported, the column widths are set appropriately for the data.
7.
Exit your spreadsheet without saving.
Exporting Data to a File

You can export the table (or a summary) to a number of popular file formats. Most applications either use one of the supported formats as their native file format or are able to import one or more of these formats. When exporting data, Monarch determines which format to use based on the file extension you provide.
Note
The Monarch Help file contains a complete list of the supported file formats.
Lets export the records for Big Shanty Music to an XLS file then examine it in your spreadsheet.
Note
When you export data, Monarch exports all the data in the Data View window or Summary window; it does not allow you to export only a portion of it, with the exception of hidden summary columns when exporting to Excel with formatting enabled, which are then exported as hidden columns to Excel.
To export only the data for Big Shanty Music, well start by applying a filter to display only those records then well export the table.
Note
Filters are discussed in detail in Lesson 10.
Steps: 1. 2. Select the Filters tab. drop-down button from the Data group of the Table
From the Current drop-down list that displays, select the Big Shanty Music filter to apply the filter to the table. The Data View window displays only the records for Big Shanty Music.
3.
In the Home tab, select Export
The Export Wizard opens, displaying the General screen.
4.
If the Add This Job to Project Exports check box is selected, click in the check box to deselect it, and then click the Next button.
Note
Refer to the Chapter 9 Project Files, Creating Project Exports portion of the Monarch Help file for information on project exports.).
The Table View screen of the Export Wizard displays. Note that the Big Shanty Music filter will be applied to the table during export.
Figure 8-9. The Table View screen of the Export Wizard.
5.
Click Next to display the Output File screen of the Export Wizard.
Figure 8-10. The Output File screen.
6.
Type Music.xlsx in the Save as: box, and then click the Next button.
Note
Most file types are available in multiple versions. For example, the Microsoft Excel (XLS) format is available in Excel 3.0, 4.0, 5.0/95, and 97+ formats. Each file type is initially set to the latest version supported. To view or modify the version for each supported file type, select File, click on Options Export and Clipboard tabs. , and then select the
The Table Info screen displays.
Figure 8-11. Viewing the Table Info screen.
If we wanted to, we could change the default name of the table by entering a new name in the Table name box, but lets stick with the default name. 7. Click Next to display the Excel Advanced Features screen. Well discuss Monarchs Excel Advanced Features in later lessons, so leave the settings as they are for now. Click the Run button. A progress dialog displays, indicating the progress of the export operation, including the percent complete and the number of records exported. When Monarch has finished exporting the table data, Export(s) Completed displays at the top of the progress dialog. 9. Click the Close button to close the progress dialog.
8.
10. Click the Close button once more to close the Export Wizard and return to the Data View window.
We can now view the exported table in Excel. 11. Launch Excel. (If you dont have Excel, just follow along in the book.) 12. Navigate to the Monarch Exports folder (for example, C:\Users\Public\Documents\Monarch\Exports) and double-click on the Music.xls file. Adjust the columns so that you can see the contents of each field properly.
Figure 8-12. Viewing the exported table data.
13. Exit your spreadsheet.
Adding Data to an Existing File

When exporting to an existing file, you can append data to a table in the file, add a new table to the file or replace an existing table in the file. Lets export the records for Bluegrass Records to the Music.xls file we created in the previous section. Well add the records as a new table in the XLS file. The new table will be displayed as a separate worksheet.
Steps: 1. 2. Select the Filters tab. drop-down button from the Data group of the Table
From the Current drop-down list that displays, select the Bluegrass Records filter to apply the filter to the table. The Data View window displays only those records for Bluegrass Records.
3.
Select Export
from the Home tab.
The Export Wizard displays (see Figure 8-8). 4. Click the Next button to advance to the Table View screen, and then click Next again. The Output File screen displays.
5. 6.
Click the Folder icon next to the Save as field to display the Export Table dialog. From the Files of Type drop-down list, select the Excel 20072010(*.xlsx;*.xlsm) option. A list of all of the .xls files in the Monarch Exports folder displays.
7. 8.
Select the Music.xlsx file, then click the Save button. Under the When Output File Exists heading, select the Add data to file option, and then click the Next button.
Note
If the Add data to file option is grayed, this indicates that the file type you are exporting to does not support adding data. For information on which file formats and versions support adding data, see the section entitled Chapter 3 Tables, Exporting Table Data in the Monarch Help file.
The Table Info screen displays.
Figure 8-13. The Table Info screen.
Note that Monarch has specified a default name of Bluegrass_Records for our export table. Although we could choose to rename it, lets use the default name. 9. Click Next to go to the Excel Advanced Features screen. Leave the settings as they are for now.
10. Click the Run button. Monarch displays a progress dialog, indicating the progress of the export operation.
11. When the export operation is complete, click Close to close the progress dialog, and then close the Export Wizard. 12. Launch Excel (if you dont have Excel, just follow along in the book), open the Music.xls file, and select the Bluegrass_Records worksheet.
Figure 8-14. Music.xls file with separate worksheet tabs for Big Shanty Music and Bluegrass Records.
13. Exit your spreadsheet.
Saving Your Work

You have just completed Chapter 8. You can save the page header and print options that you established in the first part of this lesson to a model file.
Steps: 1. 2. Select the Filters drop-down button.
From the Current drop-down list that displays, select No Filter to restore all the records to the Data View window. Select File, click on the arrow of the Save As Model from the options that display. menu, and then select
3. 4. 5.
Type Export in the File name box of the Save Model As dialog that displays, and then choose Save. Select File, and then click Exit Monarch.
Summary
In this chapter, you printed data from Monarchs Data View window, used the Windows clipboard to copy data to another application and exported the table data to a file. These procedures can also be used to print, copy and export data from the Summary window, which well discuss in Chapter 12. For further reading, see the following sections of the Monarch Help file: Chapter 3 - Tables Copying Table Data Printing Table Data Exporting Table Data
In the next chapter, youll learn how to sort the table data.
[9] Sorting the Table

In this chapter, you will learn how to sort data in the Data View window. The lesson topics include: Sorting the table Creating a sort definition Sorting on multiple fields Duplicating a sort order definition Restoring the original sort order Saving your work
This chapter assumes that you are familiar with importing and viewing report files, and working in the Data View window.
Sorting the Table

When you extract data from a report file, the information is arranged in the Data View window in the order that it appears in the report. But you may want to view, print, or export the data in another order. Sorting allows you to reorder the table to suit your needs. To sort the data in the Data View window, you create a sort order definition, which specifies the parameters for sorting. The sort order definition includes the fields to sort on, the sort order for each field (ascending or descending), and the hierarchy for the sort. You can create multiple sort order definitions and switch between them to see different views of your data.
Starting the Lesson

To start the lesson, load Monarch and open the Classic.prn and Lesson7.xmod.
Steps: 1. 2. 3. 4. 5. 6. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open Report from the options that display. Open Classic.prn. Select File, click on the arrow of the Open Model from the options that display. Open Lesson7.xmod. Expand the Table node on the Document Explorer, and then select Data View. Select Autosize Columns fields properly. from the Table tab to display all menu, and then select menu, and then select
The Data View window displays the records created from the Classic report and the Lesson 7 model. For this lesson, weve extracted all of the detail fields and a single field from each sort level in the report.
Note
If the Data View window font size is still set at 8, you may want to enlarge it to 9 or 10.
Creating a Sort Order Definition

The Classic report is sorted by Customer and Ship Date. When the report data is extracted and assembled as records in the Data View window, the records appear in the same order. This is evident in Figure 9-1. Records for Bettys Music Store appear first, then records for Big Shanty Music, and so on. Within each customer, the records are sorted by ship dates. While this sort order is useful, you may want to view the data in another order. Lets sort the table based on the Description field. To sort the table, you use the Sort Order Definition dialog to create a sort definition.
Steps: 1. From the Home tab, Data Definitions group, select the Sorts down button, and then select New. The Sort Order Definition dialog displays. drop-
Figure 9-2. The Sort Orders Definition dialog.
From this dialog, you can create multiple sort definitions and switch between them to see different views of the table. The Available Fields box displays the fields from the table. The Selected Fields box displays the fields used in the sort definition, from highest sort level to lowest. To add a field to the definition, select the field from the Fields box, then click Add >>. To remove a field, select the field from the Selected Fields box, then click << Remove. 2. 3. Type Product Description in the Name box. Select the Description field from the Available Fields box, and then click the Add >> button. The Description field appears in the Selected Fields box. When a field is added to the Selected Fields box, its initial sort order is Ascending. To change the sort order of a field, right-click on the field to display a context
menu and select Change Order from the menu. Since we want to sort the field values in ascending order, well leave the sort order as it appears. 4. Uncheck the box labeled Make this the current sort.
Note
Sort definition names may be up to 31 characters in length and may contain uppercase and lowercase characters, spaces, and punctuation. However, periods (.), exclamation points (!), accent graves (`), and brackets ([ ]) may not appear in any part of the name. As well, names may not begin with spaces. If a name is entered with leading spaces, the name is accepted but the leading spaces are trimmed.
5.
Choose OK to accept the sort definition and close the Sort Order Definition dialog. drop-down button once more. On the Current dropClick the Sorts down list that displays, select Product Description. The data in the Data View window is rearranged according to the Product Description sort order.
6.
Figure 9-3. Data View window sorted by the Description field values.
Sorting on Multiple Fields

In the previous section, we sorted the table on a single field. Monarch also lets you sort on multiple fields. Lets sort the table again, this time on two fields, Media (in ascending order) and Amount (in descending order).
Steps: 1. From the Home tab, Data Definitions group, select the Sorts down button, and then select New. The Sort Order Definition dialog displays. 2. 3. 4. Type Descending Sales by Media in the Name box. Select the Media field from the Available Fields box, and then choose Add >>. Select the Amount field from the Available Fields box, and then choose Add >>. Lets change the sort order of the Amount field to sort the values in descending order (largest values to smallest value). 5. Right-click on the Amount field, then select Change Order from the context menu. If you dont provide a name, Monarch will attempt to name the sort definition using the name of the first sort field you specified, in this case, Media. If this name is already in use by another sort definition, you will be prompted to enter a name. 6. 7. Uncheck the box labeled Make this the current sort. Choose OK to accept the sort definition and close the Sort Order Definition dialog box. Click the Sorts drop-down button once more. On the Current dropdown list that displays, select Descending Sales by Media. The data in the Data View window is sorted, first by Media, then by Amount. The Amount field values for each media type are sorted in descending order. drop-
8.
Figure 9-4. Data View window sorted by ascending Media and descending Amount.
Duplicating a Sort Order Definition

When creating several similar sort orders, you can save time by using the first sort order definition as the starting point for subsequent definitions. Monarch makes this process easy. Lets duplicate the Descending Sales by Media sort order definition to create a new definition that sorts both fields in ascending order.
Steps: 1. 2. From the Home tab, Data Definitions group, select the Sorts down button. drop-
From the list of available sorts, select the Descending Sales by Media sort order, and then click the Duplicate button. The Sort Order Definition dialog displays and is populated with the Descending Sales by Media definition. You will notice, however, that the sort name is appended with 1. Monarch requires that all sort order names be unique, so you must either enter a new name for the new sort order or accept the appended name.
3. 4. 5.
Right-click on the Amount field, then select Change Order from the context menu. Uncheck the box labeled Make this the current sort. Type Ascending Sales By Media in the Name box, then choose OK to accept the new sort order definition and close the dialog box.
6.
Select the Sorts drop-down button, and then select Ascending Sales by Media from the Current drop-down list that displays.
Figure 9-5. Data View window sorted by ascending Media and ascending Amount.
Restoring the Original Record Order

You can turn off sorting and restore the original record order by selecting the No Sort option in the Sort Orders Definition dialog.
Steps: 1. 2. From the Home tab, Data Definitions group, select the Sorts down button. From the Current drop-down list, select No Sort to disable all sort definitions. The Table data is re-displayed in its original order. drop-
Saving Your Work

You have just completed Chapter 9. We recommend you save your work in a Monarch model file.
Type Sort in the File name box, and then choose Save. Select File, and then click Exit Monarch.
The model contains each sort definition along with other information about your Monarch session. Each model can hold an unlimited number of sort definitions.
Summary
In this chapter, you used Monarchs sorting capability to change the order that data appears in the Data View window. For further reading, see the following section of the Monarch Help file: Chapter 3 - Tables Sorting the Table
In the next chapter, youll learn about record selection filters, which you can use to display a subset of the table.
[10] Record Selection Filters

In this chapter, you will learn how to use a filter to display a subset of the table data. The lesson topics include: A discussion of what a filter is Creating a filter expression Testing a filter expression Using functions Restoring the original table data Working with value-based filters
This chapter assumes that you are familiar with opening reports, creating a data extraction template, and working in Monarchs Data View window. Should you need more help, the Monarch Help file contains additional information on filter expressions, including the creation of compound ones. For more information refer to Chapter 3 Tables Window, Calculated Fields, Creating Expressions.
What is a Filter?
Record selection filters provide a means of selecting specific records and filtering out the rest. You can use a filter to explore data by limiting the available records to only those that match your specifications. You can also create multiple filters and switch between them to see different views of your data. Value-based filters offer simple filters that are typically used to obtain records with only one or two specifications. In Classic.prn, for example, you could use a value-based filter to return details for records with descriptions of Mozart and Chopin. Creating a value based filter entails identification of a single field on which your filter will be based, and then specifying the exact value(s) of the filter. Formula-based filters, in their simplest form, function very much like valuebased filters. However, formula-based filters offer increased filter-making versatility. Working with Classic.prn again, lets say you want to filter a table for all CD sales amounting to $50.00 and above. Using a value-based filter here is out of the question because only one field name can be specified with such filters. The filter you want to create specifies conditions for two fields, Media and Amount. Here, a formula-based filter would be more appropriate. Compound filters function as extensions of formula-based filters, and help you create longer filters with two or more component filters.
Starting the Lesson

Steps: 1. 2. 3. 4. 5. 6. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open Report from the options that display. Open Classic.prn. Select File, click on the arrow of the Open Model from the options that display. Open Lesson8.xmod. Expand the Table node on the Document Explorer, and then select Data View. Click Autosize Columns fields properly. on the Table tab to display all the menu, and then select menu, and then select
The Data View window displays the records created from the Classic report and the Lesson 8 model.
The table in Figure 10-1 lists transactions for distributors of classical music recordings. Transactions are included for each customer and are broken down by media types, including CDs, LPs, DVDs, SACDs and Blu-ray discs. In all, there are 149 records in the table. By applying a filter, you can select only those records that interest you and filter out the rest. Lets use a filter to view only the records for a particular customer, Fandangos Records.
Creating a Values-Based Filter Expression

To filter table data, you create a filter expression via the Filter Definition dialog. In the following exercise, we will create a value-based filters such that only records for the customer Fandangos Records are returned.
Steps: 1. In the Table tab, Data group, select the drop-down button of the Filters tool, and then select New. The Filter Style dialog displays.
Figure 10-2. The Filter Style dialog.
With this dialog, we can specify whether we want to create a formulabased filter, a value-based filter, or a compound one. For now, lets create a value-based one. 2. Ensure that the radio button for Value-based is selected, and then click OK to display the Filter Definition dialog for value-based filters.
Figure 10-3. The Filter Definition dialog for value-based filters.
3.
In the Filter Name box, enter Fandangos Records.
4. 5.
Click on the Field drop-down list and select Customer. Click on the first cell in the Value (Character) box, and then enter the customer name Fandangos Records. If you wanted to enter more values into the Value box, simply press ENTER on your keyboard after typing in the first value to move to the next cell.
6. 7.
Ensure that the Make this the current filter box is checked. Click OK to accept the details you specified. The table is rebuilt to show only those records from Fandangos Records.
Figure 10-4. The Table data with a filter applied to it. Only those records for Fandangos Records display.
You have successfully created a value-based filter. 8. Remove the filter and restore the table to its original form by selecting the drop-down button, and then selecting No Filter from the Filters Current filter list that displays.
Lets go back to the Filter Definition dialog of the value-based filter and see what else you can do with it. In the Field Values tab of the Filter Definition dialog, several options display, each of which help you create your filter. Lets say you are creating a filter where only records for Fandangos Records and Musique Royale are to display. You would then select Customer from the Field drop-down list and specify values for Fandangos Records and Musique Royale. In the Row Selection section, you can specify whether to display all records for either Fandangos Records or Musique Royale or display all records that are not by Fandangos Records or Musique Royale (i.e., all records except those from Fandangos Records and Musique Royale will display). You can also select
whether or not records with blank customer names will display in your table after the filter is applied. The Value box displays the type of values you must enter for the selected field. For example, when you select the field Quantity, all the values to be entered into the box must be of the type Numeric. When you select the field type Description, all the values to be entered into the Value box must be of the type Character. If you enter a character string into the Value box for a numeric field, the filter will not be created. The Import values filtered by drop-down list allows you to easily select field values for the Value box, including values you entered for previous filters. When you click on this box, a list of previously created filters, including the No Filter option. Clicking on any option on the list and then selecting the Add Rows button automatically adds all available values for the selected field name. Again, values to be entered must match the type of field you selected. When you select the No Filter option and then click Add Rows, all possible values for that field display in the Value box. You may delete values added to the box by clicking on the value and then selecting the Delete button. In the Advanced tab, you can find options that allow you to either display all rows (records) that fit the filter description you specified or limit the display to a certain number of rows.
Creating a Formula-Based Filter

To display only transactions amounting to US$ 100.00 and above, well use the following expression:
Amount>=100
To build the expression above, which is a formula-based one, you could type the expression as it appears above, or you could build it by inserting components from the list boxes.
Steps: 1. 2. Select the Filters drop-down list, and then select New.
Select the Formula-based radio button from the Filter Style dialog box that displays and then click OK. The Filter Definition dialog box for formula-based filters displays.
Figure 10-5. The Filter Definition dialog for formula-based filters.
3. 4.
In the Filter Name box, enter Sales >=100. In the Fields list, select Amount and then click Insert. Amount displays on the Expression box.
Note
Double-clicking an item inserts it automatically.
5.
Double-click the greater than or equal to sign (>=) in the Operators box.
The equal sign is added to the expression. 6. Click on the Expression box right after the operator >= and then type in 100. Now we can use the Test feature that is common to both Filters and Calculated fields to test our expression before saving it. 7. Click the Test tab. The fields involved in the expression are shown, together with a test value, which is taken from the first record in the report. This row would contain the field Amount, the field type Numeric, and the value 35.96, representing the first value for the field in the table. We would expect the filter to give a value of 0 (False) for Bettys Music Store, which is the current test value. 8. Click the Test button. In the box to the right of the Test button, the text 0 appears, which is what we would expect. Bettys Music Store has no sales greater than US$ 100, so it would not appear in a table to which the Sales>=100 filter is applied. The filter expression is complete. 9. Ensure that the Make this the current filter box is checked.
10. Select OK to accept your new filter.
Note
Filter definition names may be up to 31 characters in length and may contain uppercase and lowercase characters, spaces, and punctuation marks. However, periods (.), exclamation points (!), accent graves (`), and brackets ([ ]) may not appear in any part of the name. As well, names may not begin with spaces. If a name is entered with leading spaces, the name is accepted but the leading spaces are trimmed.
The table automatically re-displays only those transactions amounting to US$ 100.00 or greater (see Figure 10-6).
Figure 10-6. The Data View window showing all transactions amounting to or over $100.00.
Creating a Compound Filter Expression

Compound filters allow you to create longer, more-complicated filters composed of previously created ones. Lets create a compound filter using the value- and formula-based ones we specified earlier. If you wanted to create a filter that will return all records from Fandangos Records AND all records with transaction amounts greater than or equal to US$ 100.00, you could create a formula-based filter such as the following:
Customer=Fandangos Records.AND.Amount>=100
If you wanted to create a filter that will return all records from Fandangos Records OR all records with transaction amounts greater than or equal to US$ 100.00, you would create the following formula-based filter:
Customer=Fandangos Records.OR.Amount>=100
While you can create either of the filters this way, selecting fields, operators, and values, as well as determining the correct way to link individual filter expressions may be a tedious task. Here, Monarch allows you to create compound filters whose components you can simply select from a list. The following steps will show you how to create a compound filter that will return all records that are either by Fandangos Records or have amounts greater than or equal to US$ 100.00.
Steps: 1. 2. Select the Filters drop-down button and then select New.
Select Compound from the Filter Style dialog that displays, and then click OK. The Filter Definition dialog for compound filters displays. This dialog will display all the filters you previously created, in this case, Fandangos Records and Sales>=100, in the Available filters box.
Figure 10-7. The Filter Definition dialog for compound filters.
3. 4. 5. 6.
In the Filter Name field, enter Fandangos and Sales>=100. Select Fandangos Records from the Available filters box by clicking on it and then move it to the Component filters box by clicking Add>>. Repeat Step 4 to move the filter Sales>=100 to the Component filters box. In the Select rows that satisfy section, select the radio button for any one of the component filters. This instructs Monarch to display records that have either Fandangos Records as the customer or transaction amounts greater than or equal to US$ 100.00. If you select all of the component filters, only those records that are by Fandangos Records and have sales amounts greater than or equal to US$ 100.00 will display in the Data View window. Of course, in this case, no records will display because none of Fandangos Records sales amounted to over US$100.00. Note that the component filters, as well as the type of link (AND/OR), display in the Component filter expression box.
7. 8.
Ensure that the Make this the current filter box is checked. Select OK to accept your new filter definition. The table is rebuilt and your filter is applied.
Figure 10-8. The Data View window with the newly defined compound filter applied to it.
Using Functions in Filters

Monarch provides a host of database you can use in your filter expressions. For example, to display only shipments of symphony recordings, you can use the expression:
Instr(Symphony,[Description])
The Instr() function searches a field for the occurrence of a string. In the above example, the expression returns only those records where the word Symphony appears anywhere in the Description field. The brackets surrounding the Description field name are required, as this field name is longer than 10 characters.
Figure 10-9. Using a filter to display Symphony recordings.
Monarch supports over 70 database functions. The Monarch Help file contains detailed information about each function, including examples of their use in filter expressions. Refer to Chapter 3 Tables, Functions in the Monarch Help file for more information. In addition, Monarch includes user-defined functions, where you can create your own functions to provide an easy way to store and use complex expressions that you develop. These can even be shared easily, if you and your co-workers use Monarch. For detailed information on userdefined functions, refer to Chapter 3 Tables, User-Defined Functions in the Monarch Help file.
Restoring the Original Data

You can turn off filtering and re-display all of the table data by selecting the No Filter option in the Filters menu.
Steps: 1. Select the Filters drop-down button, and then, from the Current dropdown list, select No Filter to disable all filters. The Data View window displays all of the original data.
Saving Your Work

Steps: 1. 2. 3. Select File, click on the arrow on the Save As Model from the options that display. menu, and then select
Type Filter in the File name box of the Save Model As dialog that displays, and then choose Save. Select File, and then choose Exit Monarch. The model contains each filter definition along with other information about your Monarch session. Each model can hold multiple filters, limited only by Monarchs internal table size (refer to Appendix C - Technical Specifications of the Monarch Help file for details).
Summary
In this chapter, you used a variety of filters to explore the data in the Data View window. For further reading, see the following sections of the Monarch Help file: Chapter 3 - Tables Filters Overview Creating a Formula-Based Filter Creating a Value-Based Filter Creating Expressions Using Filters in Auditing Using a Filter to Improve Performance When Building Models Functions Function Syntax Rules Function Reference Configuring a User-Defined Function
In the next chapter, youll learn about calculated fields, which you can use to derive new information from the table data.
[11] Calculated Fields

In this chapter, you will learn how to calculate new fields from the data in the Data View window. The lesson topics include: A discussion of what a calculated field is Creating a calculated field Making comparisons Hiding and deleting calculated fields Saving your work
This chapter assumes that you are familiar with opening reports, creating a data extraction template, and working in the Monarchs Data View window.
What is a Calculated Field?

A calculated field is a field whose value is derived from other fields in the same record. Calculated fields can be used to perform a variety of tasks, including arithmetical computations, concatenating character fields, and extracting information from a field. You can create and save multiple calculated fields. Once a calculated field is created, you can use it in filter, sort and summary definitions and copy, print and export it along with other fields.
Starting the Lesson

To start the lesson, well load Monarch and open Classic.prn and Lesson9.xmod.
Steps: 1. 2. 3. 4. 5. 6. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report from the options that display. Open Classic.prn. Select File, click on the arrow of the Open menu, and then select Model from the options that display. Open Lesson9.xmod. Expand the Table node on the Document Explorer, and then click on Data View. Click Autosize Columns fields properly. on the Table tab to display all the
The Data View window displays the records created from the Classic report and the Lesson 9 model.
The table in Figure 11-1 lists transactions for distributors of classical music recordings. Weve extracted the detail information for each transaction along with the customer name and address. By creating calculated fields, we can derive new information from the table data. For example, we can create a new field that calculates a 20% discount for all transactions. Lets give this a try.
Creating a Calculated Field

To define a calculated field, you use the Field Properties dialog to create a calculated field expression. You access this dialog through the Calculated Field Style dialog.
Steps: 1. On the Table tab, Data group, select the drop-down button of the Calculated Fields tool, and then select New.
The Calculated Field Style dialog displays.
Figure 11-2. The Calculated Field Style dialog.
This dialog provides four calculated field style (or type) options: Formula-based To create a formula-based calculated field, you create a calculated field expression. A calculated field expression is a formula that returns a value to a calculated field. Runtime Parameter Runtime parameter calculated fields are very much like formula-based ones. The major difference between them is that runtime parameter calculated fields do not contain a formula, just a simple string, number or date value. The user is prompted to supply values for each runtime parameter when the model is loaded. Lookup A lookup calculated field provides a convenient "table lookup" where the value of a designated input field is checked against a lookup table. They can be useful in avoiding the long and complicated IF statements that can occur in formula-based calculated fields. User-edited A user-edited calculated field is special in that it allows the user to enter text to annotate rows in the table or enter corrections for bad data.
Note
For more detailed information on calculated fields, refer to Chapter 3 Tables, Calculated Fields of the Monarch Help file.
2.
Ensure that the radio button for Formula-based is selected, and then click the OK button. The Field Properties dialog displays.
3.
Type Discount in the Name box.
Note
Field names may be up to 62 characters in length and may contain uppercase and lowercase characters, spaces, and punctuation marks. However, periods (.), exclamation points (!), accent graves (`), and brackets ([ ]) may not appear in any part of the name. As well, names may not begin with spaces or underscores (_). If a name is entered with leading spaces, the name is accepted but the leading spaces are trimmed.
Since this expression will result in a numeric value, we need to set the appropriate formatting for the field. 4. 5. 6. From the Type drop-down list, select Numeric. Enter 2 in the Decimals box. Select the Formula tab.
To define a calculated field, you build a calculated field expression that returns a value to the field. The calculated field expression appears in the Expression box near the bottom of the Formula tab. Several list boxes display the field names from the table, arithmetic and logical operators, and functions that you can use in your calculated field expressions. To calculate a 20% discount for all transactions, well use the expression,
Amount*0.2
To build this expression, you could type the expression as it appears above, or you could build it by inserting components from the list boxes. Lets start by inserting the Amount field. 7. Click on the Amount field in the Fields box, and then click the Insert button. The Amount field is inserted in the Expression box. Double-clicking an item inserts it automatically. 8. Double-click the multiplication sign (*) in the Operators box. The multiplication sign is added to the expression. 9. Type 0.2. The calculated field expression is now complete. If we wanted to, we could test the calculated field by using the Test tab, as we did previously in the Filters lesson. In this case, however, the test would show the resulting value of the calculated field, rather than whether it evaluates to True or False, as is the case with Filters. 10. Choose OK to accept the calculated field definition and close the dialog box. A new field name, labeled Discount, is added to the end (far right) of the table. Scroll right until the Discount field is visible.
Figure 11-4. Data View window with Discount calculated field.
Making Comparisons
In the previous section we created a calculated field using a simple arithmetical expression. Monarch lets you create more complex expressions involving comparisons. Lets use this feature to calculate a discount only for sales amounts of 75 or more. Rather than create a new calculated field, well edit the existing field to apply our new discount strategy.
Steps: 1. Double-click anywhere in the Discount field, then select the Formula tab on the resulting Field Properties dialog that displays. To apply the discount only for sales amounts of 75 or more, well use the IF() function in our calculated field expression:
If(Amount>=75,Amount*0.2,0)
In plain English, this expression reads, If the Amount field contains a value of 75 or greater, use the first expression (Amount*0.2) to calculate a discount, otherwise, use the second expression (0) to show no discount. 2. Click the Clear button then enter the calculated field expression so that it reads as shown above (see also Figure 11-5).
Figure 11-5. The modified Discount calculated field definition.
3.
Choose OK to accept the new calculated field definition and close the dialog box.
The field is re-calculated to show a discount only for transactions with an amount of 75 or more.
Figure 11-6. Updated Discount field.
Now lets use another Monarch feature to suppress the zero values so that our discount values will appear more prominent. 4. Select File, and then click Options The Options dialog displays. This dialog includes several options that control the way data appears in Monarch. 5. Select View to display the View options. .
Figure 11-7. The View Options dialog.
6. 7.
Check the Suppress zero values box. Choose OK to close the dialog and apply its settings to the table data. The table displays with zero values suppressed.
Figure 11-8. Zero values suppressed.
Hiding and Deleting Calculated Fields

You can temporarily turn off the display of a calculated field by hiding it. Monarch will still re-calculate the hidden field while you make other changes to the table. If your calculated field expression is computationally intensive, this can slow down the display of the table. Of course, you can get rid of a calculated field altogether by deleting it. When you delete a calculated field, the field is removed from the table and the calculated field definition is deleted. To delete a calculated field, select the Calculated Fields drop-down button, highlight the calculated field name from the list of available fields, and then select Delete. Be careful to select the correct field name Monarch has no undo facility for deleted objects.
Saving Your Work

Type Calculated Fields in the File name box of the Save Model As dialog box that appears, and then choose Save. Select File, and then either choose Exit Monarch to end your lessons. The model contains each calculated field definition along with other information about your Monarch session.
Summary
In this chapter, you created several calculated fields to add new information to the table and to extract information from existing fields. For further reading, see the following sections of the Monarch Help file: Chapter 3 - Tables Calculated Fields Functions
This chapter concludes our exploration of the Data View window. In the next chapter, well take a look at the Summary window, where you can perform a variety of analyses on your report data.
[12] Summaries
In this chapter, you will learn how to use the Summary window to analyze data from the table. The lesson topics include: A discussion of what a summary is Creating a summary Suppressing duplicate values Adding subtotals and blank lines Measure calculations Adding item fields Collapsing and expanding a summary Specifying summary design preferences Creating a quick summary Copying, printing, and exporting summaries
This chapter assumes that you are familiar with importing and viewing report files, creating data extraction templates and working in the Data View window.
What is a Summary?
A summary tabulates information for selected fields and presents the results in a one or two dimensional matrix. For example, the summaries shown here show sales totals for a fictional distributor of classical music recordings, first broken down by customer, then by media.
Figure 12-1. Sales by customer.
Figure 12-2. Sales by media type.
The summaries shown above tabulate information about a single key field. In the first summary the key field is the Customer field and in the second summary the key field is the Media field. The field that is tabulated (or summed) is called a measure. In both of the summaries shown above, the Amount field is used as the measure. Although only a single key field and a single measure are required, a summary may be quite complex, including multiple key fields and measures. Each measure can be used to calculate a variety of information about your data, including the sum total, average, percent of total, minimum, maximum, standard deviation, and variance. Monarch also provides a count function that can be used as a measure. Rather than performing a calculation on a selected measure, the count function counts the number of records for each key. For example, the summary in Figure 12-3 includes both the Customer and Media fields as key fields, and the count function and Amount field as measures. Separate Amount calculations are used to break down total sales versus average sales.
Figure 12-3. Sales broken down by customer and media type. The count column displays the number of transactions and the Amount and AVG(Amount) columns display the total sales and average sales (partial summary shown).
A summary can also include item fields that expand the summary to show individual transactions. Item fields provide an advantage over viewing individual transactions in the Data View window because you can use the summary to break down subtotals. The summary in Figure 12-4 includes the Customer and Media fields as key fields and the Quantity and Description fields as item fields. This summary displays individual transactions for each media type within each customer. Subtotals are generated for each customer.
Figure 12-4. Item fields are used to display individual transactions (partial summary shown).
Starting the Lesson

Steps: 1. 2. 3. 4. 5. 6. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report from the options that display. Open Classic.prn. Select File, click on the arrow on the Open menu, and then select Model from the options that display. Open Lesson10.xmod. Expand the Table node on the Document Explorer, and then select Data View. The Data View window displays the records created from the Classic report and the Lesson 10 model. 7. Select Autosize Columns properly. from the Table tab to view all fields
By creating summaries, we can analyze the data to reveal trends and relationships that would otherwise remain buried. Lets create a simple summary that tabulates sales quantities and totals for each media type (CDs, SACDs, DVDs, etc.) within each customer.
Creating a Summary
To create a summary, you select key fields and measures. Key fields are used to define the summary matrix. For example, the summary in Figure 12-1 uses the Customer field as the key field. Monarch examines the field and creates a list of all unique values found within the field (e.g., Bettys Music Store, Big Shanty Music, etc.), then uses these values to create the summary matrix. Measures are numeric fields that are summed for each unique key field value. For example, in Figure 12-1 the Amount field is broken down into separate totals for each customer. Monarch also provides a count function that can be used as a measure. Rather than calculating a result for a specific field, the count function simply counts the number of records that match each for each key field value (for example, there may be 10 records for Bettys Music Store and 8 for Big Shanty Music, etc.). Lets begin by creating a summary that displays sales broken down by customer and media type. Well use the Customer and Media fields as the key fields and the Amount field as the measure.
Steps: 1. On the Summary tab, Data group, select the drop-down button of the Summaries tool, and then select New. The Summary Definition dialog displays. Note that it contains five tabs arranged top-to-bottom on its left-hand side: General, Keys, Items, Measures, and Page Setup.
Figure 12-6. The Summary Definition dialog.
Adding Key Fields and Measures

Lets get right to adding key fields and measures by clicking on the Fields tab (well come back to the General tab shortly). The Fields tab displays a list of the fields that are available for use in a summary definition. This list includes all the fields available in the Data View window except for memo fields. To add a field to the summary definition, you select the field from the Table Field box, and then click the appropriate button (Key, Item, or Measure) to add the field to the corresponding box at the right. Our summary requires the Customer and Media fields as key fields, so lets select those fields.
Steps: 1. 2. Select Keys on the left-hand side of the dialog to display the Keys options. Click on the Add (+) drop-down arrow in the Keys screen to show a list of available key fields.
Figure 12-7. Selecting a key field.
3.
Select Customer from the list of available fields, and then select Add. A Customer subtab displays in the Keys screen.
4.
Select the Add (+)
drop-down arrow once more.
The Add Right or Add Left keys specify which key field is used first and which is used next. The first subtab on the screen is considered the first key field. If you want to change this setting later on, simply go to the subtab of the field you wish to move, right-click on your mouse, and then select either Move Right or Move Left. If you make a mistake adding key fields, go to the subtab of the field you wish to remove, right-click on your mouse, and then select Remove. 5. Select Media from the list of available fields that displays, and then select Add Right.
A Media subtab is added to the right of the Customer subtab.
Figure 12-8. Specified key fields.
Several properties are assigned to each field, as you can see from the entries in the corresponding subtabs in the Key screen. For now, well accept these default properties. (Well explore the properties for Key fields, Item fields, and Measures later in this lesson and in the next lesson.) The next step is to select the measures that we want to include in our summary definition. Since we want our summary to tabulate sales totals, well select the Amount field as a measure. 6. 7. 8. Select the Measures tab. Click on the Add (+) drop-down arrow to display a list of fields that may be used as measures. Select Amount from the list, and then click Add. An Amount subtab is added to the Measures screen.
Figure 12-9. The specified measure.
We have just finished specifying our key and measure fields.
Note
We wont concern ourselves with the Chart tab at this point, as Chapter 16 is devoted to charting summary data
Selecting a Filter to Apply when Building the Summary

When you create a summary definition, you may assign a filter to limit the records available to the summary. The Apply Filter box on the General tab provides several filtering options. You may select a filter to apply when building the summary. The filter you select is applied to the data only for the purpose of building the summary. It is not applied to the Data View window and it does not limit the data available to other summaries. When building and displaying the summary, the filter overrides any filter that you may have applied in the Data View window - it is not applied along with the active table filter. You may select No Filter. The No Filter option turns off filtering for the summary. The summary is built using all records. This option, like the previous option, overrides any filter that you may have applied in the Data View window. You may select Default Filter. The Default Filter option applies the filter that is currently applied to the Data View window. This option uses only the records that are available to the Data View window at the time the summary is built. If you switch the filter that is applied to the Data View window or modify its definition, the summary is recalculated to reflect the changes. We want to use all records to build our summary, so well select the No filter option to turn off filtering for this summary.
Steps: 1. On the General tab, click the drop-down button on the Apply Filter box, and then select the No Filter option from the drop-down list.
Naming and Displaying the Summary

Steps: 1. Type Sales by Media in the Name box on the General tab, and then choose OK to accept the summary definition and close the Summary Definition dialog.
The summary you created is automatically displayed in the Summary window. 2. Select Autosize Columns properly. from the Summary tab to view all fields
Figure 12-10. Summary showing total sales for each customer broken down by media type.
The resulting summary breaks out sales totals for each media type within each customer. Grand totals are displayed along the bottom row. This summary provides some interesting information about the music distribution business. We can see that sales of classical music recordings are dominated by compact disks (CDs). Sales of LPs and DVDs, once the dominant media types, have dwindled and sales of Blu-ray disks (BLU) and super audio CDs (SACDs) have not gained much momentum, at least in the classical music market.
Suppressing Duplicate Values

Most report writers include a facility to suppress duplicate values for selected fields. Suppressing duplicate values reduces the amount of visual clutter in a report and helps to emphasize details. Monarch also includes this facility, allowing you to suppress duplicate values for any key field in a summary (except for the rightmost key field, which never contains duplicate values). Lets suppress duplicate values for the Customer key field.
Steps: 1. Double-click on the Customer column to display the Summary dialog for Sales by Media. The Keys screen is focused. The Keys screen includes several subtabs that organize key field properties into logical groups. The Layout subtab includes key field properties that determine how the key field is displayed, including an option to suppress duplicate values. 2. 3. 4. Click the Layout subtab. Under the Key Values heading, click on the drop-down list that reads Show all and select Suppress duplicates. Click OK to accept the changes made to your summary. The summary is rebuilt, and the duplicate customer names are removed.
Figure 12-11. Duplicate values removed from the Customer column.
Adding Subtotals and Blank Lines

You can add subtotals and blank lines to a summary after each logical group of information. Subtotal lines and blank lines are added whenever the value of the selected key field changes. To illustrate this, lets add subtotals for each customer and add blank lines to separate each customer group.
Steps: 1. 2. Double-click on the Customer column to display the Summary dialog for Sales by Media. The Keys screen is focused. Select the Layout subtab for the Customer key.
3. 4. 5.
Under the After each key value change heading, check the Insert subtotal line check box. Select 1 from the Insert n blank line(s) after each key value change field drop-down list. Click OK to accept the changes made to your summary. The summary is rebuilt. A subtotal line and a blank line are added directly underneath each customer group.
Figure 12-12. Subtotals are displayed for each customer. Blank lines separate each customer group.
Adding Measure Calculations

Our summary report breaks out sales totals for each media type and each customer. While this is useful, you may want to perform other analyses on the data. Monarch supports several calculations you can perform when building a summary. For each measure you include in your summary definition, you can calculate the total, average, percent of total, minimum or maximum value, standard deviation, or variance. To perform multiple calculations, you add multiple copies of a measure to the summary definition, one for each calculation you want to perform. In addition to the measure calculations, a count function is included that you can use to tabulate the number of records matching each set of key field values and a special ratio calculation option that divides subtotals for one field by subtotals for another field. Lets edit the summary definition to add the percent of total sales calculation for the Amount measure.
Steps: 1. 2. Right-click at the bottom of the Sales by Media summary. Select the Properties command from the context menu that displays. The Summary dialog for Sales by Media displays. The Measures screen is focused. 3. Click the Add (+) drop-down arrow, select Amount from the list of available fields that displays, and then click Add Right. This adds a new Amount subtab to the Measures screen. 4. Select the new Amount subtab, and then click Formula.
Figure 12-13. The new Amount subtab showing the Formula options.
5.
Delete SUM (Amount) from the Expression field, and then enter PCT(Amount) instead.
Figure 12-14. Entering a new expression.
6.
Select the General options. Note that PCT(Amount) is suggested as the column title. Lets modify this title to read %Amount.
7.
Click on the Use default title check box to deselect this option (the check mark should be removed). When the Use Default Title option is unselected, the Title box becomes available.
8. 9.
Type %Amount in the Title box. Under Display Settings, click on the Custom radio button.
10. Select Percentage from the Format drop-down list. 11. Specify a Decimals setting of 1. 12. Choose OK to accept your changes and close the Summary Definition dialog. The summary is rebuilt with the new %Amount measure. 13. Click Autosize Columns to view all the fields properly.
Figure 12-15. Summary with a %Amount calculation.
Adding Item Fields

Item fields are optional fields that you can use to expand a summary to show individual transactions. Item fields provide an advantage over viewing individual transactions in the Data View window because you can use the summary to break down subtotals. Lets add the Quantity and Description fields to our summary definition as item fields.
Steps: 1. Right-click anywhere on the Summary window showing the Sales by Media summary and then select Properties from the context menu that displays. The Summary Definition dialog for Sales by Media displays. 2. 3. 4. 5. Click on the Items tab. Click the Add (+) drop-down button, select Quantity from the list that displays, and then select Add. Click on the Add (+) drop-down button once more, select Description from the list that displays, and then select Add Right. Choose OK to accept the modified summary definition. Monarch rebuilds the summary and displays the item fields. 6. Select Autosize Columns to view all the fields properly.
Figure 12-16. A newly constructed summary showing item fields.
Collapsing and Expanding a Summary

Expanding or collapsing (referred to as drilling down or drilling up) a summary provides a quick way of exploring summary data. Collapsing (Drilling up) Reduces the level of detail in the summary, revealing higher level information. To collapse a summary, Monarch removes the rightmost key field from the summary, then re-calculates and re-displays the summary. Expanding (Drilling down) Restores the previously removed key fields to the summary display. When item fields are included as part of the summary definition, you view the item fields by drilling down the itemized level. Lets start by drilling up to hide the item fields we added to our summary definition.
Steps: 1. From the Summary tab, View group, select Drill Up .
The item fields are removed, restoring the summary to its initial state. 2. Click Drill Up once more.
Monarch further collapses the summary by removing a key field; in this case, the Media field. The fully collapsed version of the summary displays sales totals broken down only by customer.
Figure 12-15. After drilling up twice, the summary breaks out totals only by customer.
3.
Select Drill Down
from the Summary tab, View group.
The summary is restored to its original state. Lets expand the summary even further to once again view the item fields we added. 4. Click Drill Down once more.
The summary is expanded to display the item fields. At this itemized level, each line in the summary corresponds to an individual transaction from the Classic report.
Figure 12-16. Completely expanded, the summary displays individual transactions (partial summary shown).
Specifying Summary Design Preferences

Monarch includes a Summary Design Preferences Wizard, which minimizes the amount of tweaking you need to do to summaries, and makes it easy for you to provide a uniform look to them. It enables you to specify design preferences that will be applied to any new complete summaries, and to any keys, items and measures that you add to existing summaries, such as the current one. Lets explore some of the features of the Summary Design Preferences Wizard.
Steps: 1. Select Preferences from the Summary tab, Design group.
The Summary Design Preferences Wizard appears, displaying the Start page.
Figure 12-17. The Start screen of the Summary Design Preferences Wizard.
2.
Click the Next button to display the General screen.
Figure 12-18. The General screen of the Summary Design Preferences Wizard.
With this screen, we can specify a default font type and size, as well as default styles for various parts of the summary, such as the title row and the total label. Lets make a few changes to the default settings to see how they affect subsequent summaries. 3. Click the Edit button to the right of the Title option.
New options to edit the title text display to the right of the label.
Figure 12-19. The Color Properties dialog.
4.
Select the Bold (B) check box. By doing so, were telling Monarch to bolden the text in the title row of the summary. Note that the Sample field displays what the title row text will look like when bolded. Although we could also change the text and fill colors, and specify a pattern and pattern color, lets specify some design preferences for a different part of the summary instead.
5.
Click the Edit
button to the right of the Total Label option.
New options to edit the title text display to the right of the label. 6. 7. Select the Bold (B), Italics (I), and Underline (U) check boxes. Select the Insert total line check box, enter Total in the Total Label text field, and then click the Next button. The Keys - Layout screen displays. With this screen, we could specify default style preferences for the key fields in summaries. 8. Click the Next button twice to display the Items screen. With this screen, we could specify default style preferences for the item fields in summaries. 9. Click the Next button to display the Measures screen. With this screen, we could specify default style preferences for the measures in summaries. 10. Click the Next button to display the Save and Apply screen.
11. Click the Save As button, then in the dialog that displays, use the Save In drop-down list to specify the location where you want to save the summary design preferences file (e.g., C:\Documents and Settings\All Users\Documents\Monarch\Models). 12. Enter Preference1 file in the File name field, and then click the Save button. 13. Select the Additionally apply preferences to check box, and then choose All Summaries from the adjacent drop-down list.
Note
The All Summaries option is used to apply the design preferences to all summaries, i.e., to all existing summaries, and to all subsequent ones.
14. Click the Finish button to close the Wizard. Now lets see how our summary design preferences have affected the Sales By Media summary. Monarch displays the summary we created earlier and applies the style preferences we specified.
Figure 12-20. The Sales by Media summary with the design preferences applied to it.
Note that, as we specified in our summary design preferences, the text in the title row (i.e., all of the column titles) has been bolded, and the total label text has been bolded, italicized, and underlined.
Creating a Quick Summary

Another of Monarchs convenient summary capabilities is its quick summary design feature, which enables you to quickly and easily create a simple summary, i.e., a summary with no more than one primary and secondary key, and no more than one measure. Lets see how this works.
Steps: 1. On the Summary tab, click the drop-down button of the Summaries tool, and then select Quick. Monarch displays the Quick Summary Design dialog box. These will be prepopulated with the keys and measures you last specified on a summary. Lets change these values to create a completely different summary.
Figure 12-21. The Quick Summary design dialog.
2. 3. 4.
From the Primary Key list, select Account Number. From the Secondary Key list, select Order Number. From the Measure list, select Amount, and then click OK. Monarch builds the new quick summary and displays it in the Summary window.
Note that the summary design preferences we specified earlier have also been applied to this summary. 5. Select Autosize Columns fields properly. from the Summary tab to view all the
Figure 12-22. The Quick Summary. The summary design preferences you specified earlier are applied.
Copying, Printing, and Exporting Summaries

You can copy summary data to other applications export the summary to a file or print it. You perform these operations in the Summary window the same way you perform them in the Data View window. (For a review of these procedures, refer to Chapter 8 - Printing, Copying, and Exporting.) When exporting summaries, however, note that, in addition to exporting a summary, you can also choose to export all summaries. To do so, when on the General screen of the Export Wizard, select the All Summaries option from the dropdown list next to the Summary radio button.
Saving Your Work

Steps: 1. 2. 3. Select File, click on the arrow of the Save As Model. menu, and then select
Type Summary in the File name box of the Save Model As dialog that displays, and then choose Save. Select File and then either choose Exit Monarch to end your lessons. The model contains each summary definition along with other information about your Monarch session.
Summary
In this chapter, you created a summary to analyze the report data. You also specified some summary design preferences, and you utilized the quick summary design feature. For further reading, see the following sections of the Monarch Help file: Chapter 4 - Summaries Creating Summaries Viewing Summaries Exporting Summary Data
In the next chapter, youll learn how to create more advanced summaries.
[13] Advanced Summary Capabilities

In the previous chapter, you learned how to define a summary report using Monarchs Summary window. In this chapter, youll continue your tour of the Summary window and learn how to create more advanced summary reports to reveal more information about your data. The lesson topics include: Displaying key field values across Sorting a summary Creating a top 10 analysis Specifying key field values
This chapter assumes that you have completed Chapter 12 and are familiar with the process of creating a summary definition.
Starting the Lesson

To start the lesson, well load Monarch and open Classic.prn and Lesson11.xmod.
Steps: 1. 2. 3. 4. 5. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report. Select Classic.prn from the Open Report dialog that displays, and then select Open. Select File, click on the arrow of the Open menu, and then select Model. Select Lesson11.xmod from the Open Model dialog that displays, and then choose Open. When the report and model are opened, the Document Explorer will show all objects/documents related to the model. This will include any and all associated tables and summaries. 6. Drill-down on the Summaries options on the Document Explorer. Note that the Document Explorer presents several summaries that break down information about a fictional music distributor, Classical Music Distributors. 7. Select the Sales by Media summary.
Figure 13-1. Summary window displaying Sales by Media summary.
Summary Displays
Summaries can be configured to show various details. The following sections outline the steps to do so.
Displaying Key Field Values Across

When more than one key field is included in a summary report, the values for the first key field are repeated (see the values for the Customer field in the preceding illustration). This can sometimes make for a crowded feel and does not offer the most efficient method of displaying data. While you could suppress duplicate values to provide a cleaner summary display, the summary still requires a large number of rows on screen. With only 20 customers represented in our summary, the summary extends 95 rows, or about two pages. To provide a more compact summary display, Monarch allows you to display the values for the rightmost key field (in this case, Media) across the top of the summary, creating a two dimensional matrix. This format, sometimes referred to as a crosstab summary, allows for quick comparisons of multiple data groups. For summaries like the one weve created, this format provides an ideal solution since it displays the entire summary matrix on screen at one time. Lets see how this works.
Steps: 1. Right-click anywhere on the Media column, and then select Properties from the menu (or double-click on the Media column). The Summary dialog for Sales by Media displays. The Keys options are focused.
Figure 13-2. The Summary dialog for Sales by Media.
2.
Under the Layout subtab, select the Across (row) radio button, then choose OK. The summary is rebuilt with the Media values spread across the top row.
Figure 13-3. Summary with customer values displayed down and media values displayed across.
Displaying Null Values

For some media types, such as BLU (Blu-ray discs) and LP (Long Play albums), no sales have been made to certain customers. For example, see the (Null) strings in the BLU column for the first ten customers. In this case, it would be incorrect to display a zero value in the summary report, since zero might suggest that some amount of sales were made to this customer. For example, consider the case where a customer places an order for several DVDs and in the same period returns several previously purchased DVDs. If the total purchase price of the returned media equals the price of the new media, the value for this customer would be zero, indicating a net purchase of zero. However, if the customer never placed any orders for digital compact cassettes, a zero would not be appropriate. In this case, the value of (Null) indicates that no sales or returns have been recorded for this media type. By default, Monarch displays null values as the string (Null), but you can specify another string, or a blank string, to represent null values if you like. You might find the string (Null) a bit distracting when youre viewing this type of summary report, so lets change the default string to something else.
Steps: 1. 2. Select File, and then click Options to launch the Options dialog.
Select the View tab from the left-hand side of the dialog to display the View options.
From this screen, you can set several options that affect the way data is displayed on screen in Monarch. 3. Click in the Display null values as edit box, change the (Null) string to ---, and then choose OK. The summary is redisplayed using the string you specified.
Figure 13-4. Null values represented by blank strings.
Freezing Panes
When you spread key values across the top of the summary matrix, the summary often becomes wider than the display width, especially if you are using a 640 x 480 display. Scrolling right to view more information often removes the most important information from the display, i.e., the highest level key fields, in this case the Customer field. To prevent columns from scrolling off screen, you can use Monarchs Freeze Panes command. Lets freeze the Customer column, then scroll right to view the rest of the summary columns.
Steps: 1. 2. Click on the column heading BLU so the entire column is highlighted. Select Freeze Panes from the Tool group of the Summary tab.
The Freeze Panes command acts just like the same named command in Microsoft Excel: it locks all rows above the cell pointer and all columns to the left of the cell pointer so that they remain on screen when you scroll the rest of the summary. This command also works in the Data View window, and is retained in Excel file exports. 3. Click on the arrow on the right side of the horizontal scroll bar to scroll right.
Note that the Customer column remains visible while you scroll. 4. 5. Use the arrow at the left edge of the scroll bar to scroll back to the left. Select the drop-down button of the Freeze Panes tool and then select Unfreeze Panes to unfreeze the Customer column.
Adjusting Column Widths

Sometimes, you can avoid scrolling altogether by reducing the column widths in a summary so that all of the information fits on screen. When Monarch builds a summary, it sets the column widths wide enough to handle large numbers, such as 1,000,000.00. If the resulting columns are wider than you need to display your summary data, you can adjust them to fit more columns on screen. The column widths in our crosstab summary are wider than we need them to be. Lets adjust the column widths so that everything fits on screen.
Note
Column Widths for Across Keys All of the columns generated from an across key field (in this case the CD, BLU, LP, DVD, and SACD) columns are based on the column width of the measure. To set the column widths, either use the mouse as described in the following procedure or double-click on the measure (the last column of the summary) and use the Measure dialog to change the width of the measure. All columns related to the key field will snap to the width assigned to the measure.
Steps:
1. Scroll right and double-click anywhere on the measure, i.e., the Total column. The Summary dialog of Sales by Media displays. The Measures options are focused. 2. Set the column Width setting to 8, then choose OK. The summary is rebuilt to show your changes.
Note
You can also use the mouse to set column widths. Using the mouse is less precise than using the dialog, however, requiring you to estimate the column width. To use the mouse to set a column width, position the mouse cursor at the right edge of the column title. The mouse cursor changes to a resizing handle. Click down and drag left or right until the column title is the desired width.
The summary should appear as shown in Figure 13-5.
Figure 13-5. Crosstab summary after column width adjustments.
Viewing Other Measures

You may have noticed that our summary displays only a single measure calculation, even though it displayed two measures when we started this lesson (see the Amount and %Amount columns in Figure 13-1). When you elect to display values for a key field across the top of the summary, Monarch forms a two-dimensional matrix that displays a single measure at a time by default. To display results for a different measure, you can select it from the Active Measure drop-down list on the Summary window. This list displays the names for all measures included in the summary definition. When you select a measure from this list, the summary is redisplayed to show the results calculated for that field.
Steps: 1. On the Summary tab, click the drop-down arrow of the Active Measure list box. This box currently displays Amount.
Figure 13-6. The Active Measure drop-down list in the Summary tab.
2.
Select %Amount from the list of available measures. The summary is re-displayed to show media sales as a percent of total sales. Note that the Summary line at the bottom of the summary displays the percentages represented by each media type across all customers and the rightmost column represents percentages for by each customer across all media types.
Figure 13-7. Viewing the %Amount measure.
Displaying Multiple Measures in Across Key Summaries

Monarch allows you to display multiple measures in across key summaries. Lets try doing this.
Steps: 1. Click the drop-down arrow on the Summary tabs Active Measure dropdown list and select All, by Key. Monarch displays both of the measures grouped by key.
Note
Choose a smaller font size and auto-size the column widths in order to display all of the data in the summary window (see Figure 13-6).
Figure 13-8. Displaying both measures grouped by key.
You may have to adjust the font size to 10 to reasonably view all the columns. We can also choose to view all of the measures grouped by measure. 2. Click the drop-down arrow on the Summary tabs Active Measure list box and select All, by Measure.
Monarch displays both measures grouped by measure.
Note
Choose a smaller font size and auto-size the column widths in order to display all of the data as in Figure 13-7.
Figure 13-9. Displaying both measures grouped by measure.
Lets return to our across key summary with only one measure. 3. Click the drop-down arrow on the Summary tabs Active Measure list box and select %Amount and restore the font size to 12.
Sorting a Summary
When you define a summary, Monarch initially displays the key field values in ascending alphabetical order. You can see this in the summary in Figure 13-5. Here, the Customer values are displayed starting with Bettys Music Store, proceeding to The Record Store. The same holds true for the values of the Media field (BLU, CD, DVD, LP, SACD). If the initial display order is not what you want, you can modify the order for any key field using the Key Field dialog. Lets change the sort order for the Customer key field to sort the customer field values in descending order.
Steps: 1. Double-click on the Customer column to display the Summary dialog for Sales by Media. The Keys screen is focused. You can click the Autosize Columns visible. 2. 3. tool if the Customer column isnt
In the Customer tab, click on the Sorting subtab. Under the Direction heading, select the Descending radio button, then choose OK. The summary is redisplayed sorted in descending order by customer.
Figure 13-10. Sorting a key field.
Sorting by Measure Values

Monarch can also sort a summary based on the values of a measure. Lets use this feature to sort our customers from largest to smallest. First, well select the SUM(Amount) measure calculation to show sales totals for each customer.
Steps: 1. Select Amount from the Active Measure drop-down list located on the View group of the Summary tab. Now well sort the Customer key field values based on the values of the SUM(Amount) field. This has the effect of ranking customers by sales totals. Well sort in descending order to rank customer sales from largest to smallest. 2. 3. 4. Double-click on the Customer column to display the Summary dialog for Sales by Media. The Keys options are focused. Click on the Sorting subtab of the Customer tab. Under the Sort By heading, select the Measure radio button. The SUM(Amount) field is already selected as the measure to sort on. 5. 6. Ensure that the Descending option is selected. Choose OK. The summary is redisplayed with customers sorted in descending order by sales totals. Note that Hopes Sweet Notes appears first because it has the largest total sales amount of any customer (661.97), followed by Musique Royale with a sales total of 595.43, and so on.
Figure 13-11. Sorting a key field based on the values of a measure.
Lets also sort the Media field values in the same manner. The result should show the media type with the largest overall sales first, followed by the second largest in terms of sales, etc. 7. Double click on any cell in the Customer column once more. The Summary dialog for Sales by Media displays. The Keys options are focused. 8. 9. Select the Media tab, and then click on the Sorting subtab. Select the Measure radio button under the Sort By heading. The SUM(Amount) field is already selected as the measure to sort on, but the sort order is set to Ascending. Well change this to Descending in order to display the media type with the largest sales first. 10. Select the Descending radio button under the Direction heading, and then choose OK. The summary is redisplayed with customers sorted in descending order by sales totals and Media types also sorted in descending order by sales totals.
Figure 13-12. Sorting both Customers and Media types by sales totals.
Note that CD appears first because it has the largest total sales amount across all customers (5403.11), followed by DVD with a sales total of 612.01, and so on. The sales totals appear in the Summary row at the bottom of the summary report.
Restoring the Key Field Direction

So far, weve shown how to create a crosstab summary that displays the values for a single key field across the top row, forming a two-dimensional matrix. This format is useful for displaying a lot of information, but it may not be ideal for all summary reports. With Monarch, you can switch between this format and a more traditional report format, choosing the appropriate format for your data and viewing requirements. Lets reset the Media field to display its values down rather than across.
Steps: 1. 2. 3. Double-click anywhere on the Customer column to display the Summary dialog for Sales by Media. The Keys options are focused. Click on the Media tab, and then select the Layout subtab. Under the Orientation heading, select the Down radio button. Now, lets suppress duplicate values in the Customer column to give a summary report a nicer look. 4. 5. On the Customer tab, click on the Layout subtab. From the Key values drop-down box, select Suppress Duplicates, and then choose OK. The summary is redisplayed suppressing duplicate customer names.
Figure 13-13. Restoring the summary to a more traditional display format.
Notice that the summary is still sorted in the same manner, with customer groups sorted in descending order by sales amounts (entire summary groups are sorted based on the subtotal values for each group). Within each customer, the media types are also sorted in descending order by sales. In this format (with no key field displayed across), the summary can display both the SUM(Amount) and %(Amount) measure calculations at the same time.
Top n Analysis
Top n analysis, typically referred to as Top 10 Analysis, is a common tool provided by data analysis applications. You can create a top n analysis or bottom n analysis of your summary data by first sorting the data in the appropriate order and then by selecting the first n values, whether n is 10, 3 or some other number. These values then represent either the top n or the bottom n analysis. Lets see how this works. Well create a top 3 analysis from our sales summary that shows the top three customers in terms of sales. All other customers will be represented using a single summary label, All Others. To create our top 3 analysis, we start by sorting the customers in descending order by sales totals. This places the top performing customers at the top of the summary display. Weve already applied this sort in our previous discussion on sorting. Next, we need to indicate that we want to see only the first three customers (the three customers with the largest sales totals).
Steps: 1. 2. 3. 4. 5. 6. Double-click on the Customer field to display the Summary dialog for Sales by Media. The Keys options are focused. Select the Matching subtab from the Customer tab. Select the First n Values option and enter 3 in the box (this is the box labeled Count of distinct values as sorted). Under the Accumulator for values after the First N heading, ensure that All Others is entered into the Label field. Select the Media tab. In the Layout subtab, select the Across orientation and then click OK. The summary is rebuilt to show only the top three customers. All other customers are grouped together and represented by the All Others label.
Figure 13-14. A Top 3 analysis showing the top performing customers in terms of total sales.
You can easily modify this summary to show the bottom 3 customers in terms of sales totals by sorting the customer key field in ascending order. Likewise, you can increase or decrease the number of customers that you explicitly break down for analysis by increasing or decreasing the value in the Customer fields First n Values option. A top n analysis can be defined at any level in the summary. Within each customer, you can define a top 3 analysis of each media type by sorting the media type in descending order and then selecting the First n Values option for the Media key field and entering the desired number in its spin box.
Specifying Key Field Values

While performing a top n analysis is useful when you want to determine which items (customers, salespeople, etc.) are the top performers, sometimes you already know which items you want to look at, whether they are the top performers or otherwise. For example, suppose you want to focus on just the digital media types (compact discs, digital versatile discs, and Blu-ray discs). You could do this by creating a filter in the Data View window with the filter expression:
Media="CD".Or.Media="DVD".Or.Media="BLU"
You could also write this expression as:
Media.In.(CD, DVD, BLU)

By applying the filter to the summary definition, the summary would display information about only the digital media types. However, Monarch provides an easier and, in some cases, more powerful method of achieving this result. For each key field, you can define a list of all values you want to use when building the summary. Further, you can accumulate all values not in your list as a single key value (a feature not available if you use a filter). To demonstrate how this works, lets create a list of values to use for the Media field.
Steps: 1. 2. 3. Double-click on the Customer field to display the Summary dialog for Sales by Media. The Keys options are focused. Select the Media tab, and then navigate to the Matching subtab. Check the Values from table option.
Figure 13-13. Displaying the Matching subtab of the Media tab.
4.
Click the
Add current values button.
All unique values found within the Media field (BLU, CD, DVD, LP, and SACD) are displayed.
Figure 13-14. Specifying sorting values.
5.
Select CD from the Key Values list, and then click the Add button to add this value to the Specified or Upper Limit Values list. Repeat Step 4 to add the DVD and LP values to the Specified or Upper Limit Values list. The selected values appear in the subtabs Click to sort values list.
6.
Click the Add current values button once again to close the Key Values list. Lets accumulate the old media types (BLU and SACD) under a separate heading.
7. 8.
Ensure that the Display box under the Accumulator for unmapped values heading is selected. Type All Others in the Label box, and then choose OK.
Monarch rebuilds the summary using only the selected media types. The two media types not specified (BLU and SACD) are combined under the label All Others.
Figure 13-15. Using specified values to break down specific digital media types versus all other media types.
Sorting by Key Field Values

Once you have specified key field values, you can sort the summary data by either the specified values themselves (i.e., their names) or by their order in the Specified or Upper Limit Values list on the Matching subtab. To do so, select the Key Field radio button on the Sorting tab, and then choose either the Field Value option (to order the data by the field value names) or the Position in values table option (to order the data by their position in the Specified or Upper Limit Values list).
Note
The Position in values table option is not available if the Values from table option on the Matching tab has not been chosen.
Upper Limit Values

While the Values from table option lets you select the exact values you want to use in your summary, sometimes you want to group values into ranges, such as weekly or monthly periods. This capability is provided via the Upper Limit Values option. This option lets you define an upper limit for each range of values. An example of this can be seen in the Sales by Ship Date summary.
Steps: 1. 2. Click on the Sales By Ship Date summary in the Document Explorer. Select Autosize Columns fields properly. from the Summary tab to view all the
The Sales by Ship Date summary displays. This summary breaks out sales for all customers across four weekly periods during the month of April, 2008. We created this summary by using the dates 04/07/2010, 04/14/2010, 04/21/2010, and 04/28/2010 to define the ending dates for each weekly period.
Figure 13-16. Using upper limit values to break down ship dates into weekly periods.
Summary Limit Values

Another way to analyze summary data is to set a limit against a measure. This feature works somewhat like a filter that is applied after the summary is built. For example, the Sales >=400 summary displays only those customers for which totals sales exceed 399.00.
Steps: 1. Click on the Sales >=400 summary in the Document Explorer. The Sales >= 400 summary displays. This summary breaks out only those customers for which total sales are at least 400.00 (There are seven such customers). All other customers are grouped together under an All Others label. To create this summary, simply double-click on the Customer field, and then select the Matching subtab on the Customer tab on the Keys options of the Summary dialog that displays. Select the Measure Limit radio button. Choose the SUM(Amount) field, the >= operator, and enter 400 in the adjacent field.
Figure 13-17. Specifying summary limits.
Figure 13-18. Using Summary Limit Values to break down large customers.
Saving Your Work

Steps: 1. 2. 3. Select File, click on the arrow of the Save As Model. menu, and then select
Type Advanced Summary in the File name box that displays, and then choose Save. Select File and then Exit Monarch. The model contains each summary definition along with other information about your Monarch session.
Summary
In this chapter, you learned about many of the advanced features of Monarchs Summary window. For further reading, see the following sections of the Monarch Help file: Chapter 4 - Summaries Creating Summaries Viewing Summaries
In the next chapter, youll learn how to create charts to represent the data in a summary
[14] Charting Summary Data

In this chapter, you will learn how to create charts to represent summary data. The lesson topics include: A discussion of how Monarch creates charts Displaying charts Navigating a series of charts Displaying more information on each chart Changing a charts type and display options Adding a title to a chart Specifying chart colors and fonts Copying a chart to another application Printing a chart Exporting a Chart
How Monarch Creates Charts

To create charts with most spreadsheet and charting packages, you are asked to manually select the chart data range and legends and you are given control over other settings, such as scale increments. In Monarch, these settings are determined automatically, based on the available summary data. The difference in approach is due to the nature of a Monarch summary. A summary, unlike a spreadsheet, has a dynamic size. If you apply a filter to the summary, or if the summary is re-built based on a new instance of a report, the summary data set may shrink or grow in size. As a result, any chart data range, legends, or scale increments that you defined would need to be redefined to accommodate the new data. By automatically selecting these chart properties each time the summary is built, this ensures that the charts match the available data set and saves you the annoyance of re-defining chart properties each time there is a change to the summary data. Monarch also recognizes that not every summary can be represented with a single chart. Depending upon the number of key fields and measures available, it will create either a single chart representing all of the summary data or a series of charts, with each chart representing a logical subset of the summary data. For a simple summary that includes one key field and one measure, Monarch will create a single chart to represent all of the data in the summary. An example of this type of summary is shown in Figure 14-1. The summary includes a single key field (MEDIA) and a single measure (AMOUNT). The entire summary can be represented on a single chart. This bar chart includes
a series of bars, with each bar representing the sales amount for a single customer. If the available display area is too small to accommodate the entire chart, a scroll bar is included with the chart.
Figure 14-1. Summary with associated chart.
For more complex summaries those with multiple key fields and/or multiple measures a series of charts is created, with each chart representing a logical subset of the summary data. For example, the summary shown in Figure 14-2 displays sales broken down by media type (CD, LP, DVD) for each of 20 customers (Bettys Music Store, Big Shanty Music, Bluegrass Records, etc.). For this summary, Monarch creates a series of charts: one chart for each customer.
Figure 14-2. A summary with two key fields produces multiple charts (first of a series of charts shown).
Starting the Lesson

Steps: 1. 2. 3. 4. 5. 6. 7. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report. Open Classic.prn. Select File, click on the arrow of the Open menu, and then select Model. Open Lesson12.xmod. Select the Sales by Media summary and open it to view its contents. Select Autosize Columns fields properly. from the Summary tab to view all the
Monarch builds and displays the Sales by Media summary. This summary displays sales quantities and amounts for music distributors broken down by customer and media type. Duplicate customer names have been suppressed and blank lines have been added to separate each customer group.
Figure 14-3. Summary displaying sales quantities and amounts by customer and media type.
Displaying Charts
Since Monarch automatically selects the chart data range, legend text and scale increments, all you need to do to create and display a chart is to select the Charts tool.
To create a new chart, simply click on the drop-down button of the Charts tool, and then select New. A copy of the first chart for the summary is created. Remember, Monarch automatically selects x- and y- axes points for you, so there is no need to worry about the initial values for your chart. From here, you can modify any chart property you wish until the desired chart is achieved.
Steps: 1. 2. In the Summary tab, select the drop-down button of the Charts menu in the Data group, and then click New. A Chart Style dialog displays, showing the general style for the new chart that will be created. Well learn how to format charts in a while so for now, lets simply click the OK button. A new tab opens, displaying the first chart available for the first customer, Bettys Music Store.
Figure 14-4. Displaying the first chart for the summary.
3.
To view multiple charts simultaneously, you may increase or decrease the values in the Columns and Rows options at the bottom of the tab. The lowest number of charts that can be viewed is 1.
In the next figure, for example, two charts are being viewed at the same time. This is done by changing the Columns value to 2 and retaining a Rows value of 1.
Figure 14-5. Viewing multiple charts simultaneously by columns.
In the next figure, for example, two charts are being viewed at the same time. This is done by changing the Rows value to 2 and retaining a Columns value of 1.
Figure 14-6. Viewing multiple charts simultaneously by rows.
4.
Return to the original chart display settings by entering values of 1 in the Columns and Rows boxes. The charts are displayed in the order by which records are arranged. For example, if the customers Bettys Music Store and Big Shanty Music have charts for the measures Amount and Quantity, the first chart that will display will be that of Bettys Music Store Amount. The second, third, and fourth charts that will display will be those of Bettys Music Store Quantity, Big Shanty Music Amount, and Big Shanty Music Quantity, respectively.
Note
To display a chart alongside a summary, right click on either the Summary or Chart window tab, and then select New Horizontal Tab Group from the options that display. To display a chart on top of a summary or vice versa, select New Vertical Tab Group. To go back to your previous display, right-click on either of the tabs and then select Move to Next (or Previous) Tab Group.
You can also view the charts in Compact mode by checking the box for Compact Mode located beside the Chart Selector. Doing so eliminates the X and Y axes values from the chart, resulting in a more streamlined chart.
Figure 14-7. Viewing a chart in Compact mode.
For the remainder of this Chapter, we will not view the charts in Compact mode. Ensure that the box for Compact Mode is deselected before you continue.
Navigating a Series of Charts

Depending upon the number of key fields and measures used in the summary, Monarch will create either a one dimensional series of charts or a two dimensional series of charts. For example, the summary in Figure 14-6 will produce a one-dimensional series of charts, with a single QTY chart for each customer.
Figure 14-8. A summary with a single measure produces a one dimensional series of charts.
Summaries that use more than a single measure produce a two dimensional series of charts, with a new series added for each additional measure. By adding a second measure to the summary shown above, Monarch will produce twice the number of charts, arranging them in a two dimensional series.
Figure 14-9. Each additional measure adds another series of charts.
In the case of a summary with only a single key field, like the one shown in Figure 14-1, each series includes only a single chart. However, for summaries with multiple key fields, each measure generates a series of charts, as is the
case in Figure 14-3, where there will be 20 charts per series, one for each customer. Navigating a series of charts, whether it is a one dimensional series or a two dimensional series, is simple. To view the next chart, click the Next Chart button located at the bottom of the chart. The name of the chart currently being viewed is displayed to the left of this button. To view the previous chart, click the Previous Chart button . For a two dimensional series, such as the summary shown in Figure 14-7, Monarch navigates across, then down. In other words, by pressing the Next Chart button, youll see the Qty chart, then the Amount chart, for each customer.
Locating a Specific Chart

Lets say were interested in viewing the chart representing sales quantities for Spinning Records. To do so, you would use the Chart Selector.
Steps: 1. On the Sales by Media Chart tab, click on the Chart Selector dropdown button. This displays a list from where you can select the chart for Spinning Records | Qty. The Qty chart for Spinning Records appears.
Figure 14-10. The Chart Selector drop-down button.
An indicator may be found to the right of the Chart Selector. This indicator lets you know how many charts were obtained from the previous summary and what chart number you are viewing.
Displaying More Information on Each Chart

To the left of the Chart tab is the Chart Properties panel. This panel allows you to edit most of the chart settings, including its title, chart type, X- and Yaxes, colors, and data points, among others.
Figure 14-11. The Chart Properties panel.
As weve seen, when more than one key field or measure is used in a summary, Monarch creates a series of charts, each representing a logical subset of the summary data. When multiple key fields and multiple measures are available, a potentially large number of charts may result. Initially, each chart includes information about a single key field (the rightmost key field) and a single measure. You may reduce the number of charts created by displaying more information on each chart. With Monarch you do this by clustering, i.e., including more key field information or more measure information on each chart. The Sales by Media summary that we are using for this lesson is an example of a summary that includes multiple key fields and multiple measures. Customer and Media are the key fields and Qty and Amount are the measures. Monarch initially produces a total of 40 charts for this summary, since there
are 20 unique values of the Customer field and 2 measures. By clustering key fields, we could reduce the number of charts to only two. The available cluster options are None, Key Fields, and Measures. By clustering measures, we could reduce the number of charts to 20. Lets experiment with each cluster option.
Steps: 1. Locate the General drop-down options in the Chart Properties panel of the first chart (i.e., Bettys Music Store Qty, and then select Key Fields from the Cluster drop-down box. The selected cluster is displayed. 2. 3. 4. Expand the Display Options drop-down list. In the Display Options drop-down, locate the sub-options for X-Axis, and ensure that the box for Rotate Text is checked. Ensure that the Label Width is set to 16 and the Line Height is set to 2. This will allow enough room to display the customer names. Names longer than 16 characters will wrap to the second line. The chart is redisplayed with key fields clustered. Each bar on the chart still represents a media type (CD, LP, and DVD), but now the chart includes a cluster of bars for each customer.
Figure 14-12. Clustering key fields.
Note that the status bar now indicates that only two charts are available. The first chart represents the Qty values from the summary and the second chart represents the Amount values. Now lets try clustering the measures.
5.
Select Measures from the Cluster drop-down box. The selected cluster is displayed.
Monarch recreates the series of charts. Now, each chart includes both the Qty and Amount values for a single customer. Since there are 20 customers in the summary, there should be 20 charts.
Figure 14-13. Clustering measures.
Although we were able to reduce the number of charts from 40 to 2 by clustering key fields and from 40 to 20 by clustering measures, its important to note that not all charts make sense. Since Qty and Amount are not on the same scale - one represents sales in dollars and the other represents sales in units - it doesnt make sense to use the Measures cluster option to group these values onto the same chart. However, if your measures represented forecasted sales versus actual sales, clustering the measures would provide an easy and convenient method to compare the values. Since this doesnt make sense for our data, lets reset the cluster option to None. 6. Select None from the Cluster drop-down box. The original series of charts is restored.
Changing Chart Type and Display Options

The Chart Type drop-down box displays the current chart type: Vertical Bar. The drop-down list includes Area, Horizontal Bar, Vertical Bar, Line, Pie, and Point charts. The default chart type is Vertical Bar. You change the chart type using the Summary Definition dialog.
Steps: 1. 2. Click the drop-down button on the Chart Type box. Select Pie from the drop-down list. The chart is redisplayed as a Pie chart.
Figure 14-14. Viewing the pie chart.
3.
Repeat Steps 13 to experiment with the other chart types. When you are finished, change the chart type back to Vertical Bar. This is the chart type we will use for the remainder of this lesson.
Viewing Chart Values

Monarchs Chart function allows you to identify specific chart entries so you always know exactly what youre looking at. In Figure 14-14, for example, one cannot tell simply by looking at the chart how many CDs and LPs were sold by Bettys Music Store. Monarch allows you to view chart values simply by hovering on the colored area of your choice.
Figure 14-15. Viewing the values of the pie chart.
Figure 14-15 shows that the number of CDs bought is 42. Hovering your mouse on the pink slice will show that the number of LPs purchased is 6.
Figure 14-16. Viewing the slices of the pie chart.
As an added presentation feature, you can even click on individual pie chart slices to separate it from the rest of the pie. As usual, hovering on either the separated slice of the rest of the pie displays their corresponding values.
Selecting Chart Display Options

Your chart display can be configured via the Display Options section of the Chart Properties panel. The Display Options section of the Chart Properties panel is displayed below.
Figure 14-17. The display options available for charts.
The following table describes each display property:

Property Description
Joined (only for Horizontal and Vertical Bar charts) Connector (only for Area and Line charts) Grid lines
Select remove padding spaces to remove spaces between series Specifies what figure to use to link individual data points. Select Vertical to display a vertical grid on the chart.
Select Horizontal to display a horizontal grid on the chart. Select Rotate Text to rotate the legends in the x-axis. Use the Label Width setting to adjust the display width of the xaxis legend text. Monarch initially truncates the legends to 5 characters to prevent overlapping. You may set the legend width to any value in the range 1 to n where n represents the column width of the key field from which the legend text is derived.
These are not used for Pie charts. X-Axis
Use the Label Height setting to wrap the x-axis legend text onto multiple lines. Wrapping legends prevents long legend text from overlapping. You may set the legend height to any value in the range 1 to 5. The Line Width setting is used to determine where the legend text should wrap. Use the Stagger setting to stagger the legends such that they do not all display on the same line. This will prevent long legend text from overlapping.
Note: Monarch cannot display both wrapped and staggered legends. If Stagger Legends is selected, the legend height is automatically reset to 1 and the Legend Height box is disabled. The Legend Height setting is also disabled for Horizontal Bar charts, where legends automatically wrap based upon the width of the legend boxes. Y-Axis Use the Zero Baseline setting to designate how Monarch should calculate the y-scale for a chart. A chart's y-scale range is based on the high and low values in the chart's data range. If the chart data range includes the values 100, 200, and 300, Monarch creates a y-scale from 100 to 300. However, you would most likely want the y-scale to begin at 0. To do so, select Zero Baseline to force Monarch to include a zero baseline on every chart. Otherwise, Monarch floats the yscale baseline above or below zero, based upon the values within the chart data range. Use the Same for All Charts in setting to designate whether Monarch should apply the same yscale to all charts in a series or dynamically adjust the y-scale for each chart within the series. When Apply Same Y-Scale to All Charts in Series is selected Monarch creates a uniform y-scale for all charts in a series based upon the smallest and largest data values that will be
Y-Axis (continued)
represented by the charts. When de-selected Monarch creates a unique y-scale for each chart based upon the data values represented by that chart. Raw Values Use the Display on Chart setting to display the actual chart values on the chart, beside the chart elements. Select None, Lines, or Stripes to display the measure limits.
Measure Limits
Adding a Title to a Chart

The Chart Title dialog can be used to add a title of up to three lines to your charts. If a series of charts exists for a summary, all charts in the series share the same title. Lets add an appropriate chart title for our series of charts.
Steps: 1. Expand the Title section of the Chart Properties panel. The Chart Title window displays.
Figure 14-18. The Chart Title dialog.
The Chart Title window includes a text box for the chart title. Special formatting functions let you align text and dynamically add information to the title, including the chart number, the print date and time, the name of the underlying summary and more.
Note
For a complete list of the available formatting functions, consult Chapter 4 Summaries, Charting Summary Data, Adding a Title to a Chart in the Monarch Help file.
2. 3.
Click in the Title box. Remove the default entry (&[Keys]) by highlighting it and then deleting. The default entry seen here specifies that the primary Key field in each chart should display as the title of the chart. Thus, the title of the first two charts is Bettys Music Store.
4. 5. 6. 7. 8.
Type Classical Music Distributors as the first title line. Press ENTER to move the cursor to the second line of the Title box. Type Key Field: followed by a space. Right click on your mouse and then, in the context menu options that display, select X-Axis Fields. Press ENTER to move the cursor to the third line of the Title box.
9.
Type Measure: followed by a space.
10. Right click on your mouse and then, in the context menu options that display, select Y-Axis Fields. The title is added to the chart.
Figure 14-19. Adding a title to a chart.
11. You can change font properties in your title, such as font type, size, color, and justification, among others, by highlighting the section of the title you want to format and then hovering your mouse over the selected text. A box with formatting controls appears over the text you highlighted. Select any of these controls to format your text as you wish.
Figure 14-20. Formatting the chart title.
Specifying Chart Colors and Fonts

The Chart Appearance dialog can be used to specify colors and fonts for various parts of a chart. Lets use it to change the appearance of the horizontal bar chart of Bettys Music Store - Qty.
Specifying Chart Colors

Steps: 1. Expand the Colors and Fonts node in the Chart Properties panel. The options that display allow you to change the colors and fonts of the title and chart background, the plot area, the measures, and even the axis labels and data values included in the chart. 2. Click on the Title button. The Chart Appearance dialog displays.
Figure 14-21. The Chart Appearance dialog.
3.
Select a color you wish to use as a title area color. Note that the current color of the chart area you are modifying (i.e., black) is indicated by the selected color cube in the upper left hand corner of the Basic Colors grid. Repeat Steps 2 and 3 to change the color of the chart background, the plot area, and the measures. The chart displays with the new colors you selected.
4.
The following figure shows a chart in which the original title area color is retained and the background, plot area, and measure colors are modified.
Figure 14-22. The Color dialog.
5.
Restore the default chart colors.
Specifying Chart Fonts

In addition to allowing you to specify the chart colors, the Colors and Fonts options allow you to choose chart fonts. Lets modify the charts current fonts. Under the Fonts heading, note that the current font name and font size of the charts title, X-Y labels, and values are listed.
Steps: 1. 2. To change the fonts on the chart axis labels, select the Axis Labels dropdown button on the Colors and Fonts options. In the drop-down box that displays, select the font you wish to use for the chart axis labels. For example, select the font Courier New and the font size 18. Tick the box for Bold. To change the fonts on the chart values, select the Values drop-down button on the Colors and Fonts options. In the drop-down box that displays, select the font you wish to use for the chart values. For example, select the font Courier New and the font size 18. Tick the box for Bold as well.
3. 4.
The font changes you made display on the chart.
Figure 14-23. Changing the font of a chart.
5.
Restore the chart titles previous font settings by clicking the corresponding Change button and specifying the appropriate settings (Arial, Regular, 12, Black) in the Colors and Fonts options.
Copying a Chart to Another Application

You can use the Windows clipboard to copy a chart to another application, such as a word processor or a slide presentation package.
Note
When you copy a chart to the clipboard, Monarch copies only that portion of the chart that is visible on screen. For large charts, maximize the chart display first.
Lets copy the current chart into your word processor.
Steps: 1. 2. 3. Right-click on the chart tab and select Copy. Launch your word processor. (If you dont have a Windows-based word processor, just follow along in the book.) Position the cursor where you want the chart to appear and use your word processors Paste command to insert the chart into your document.
Figure 14-24. Pasting a chart to a word processor.
If you were going to keep this document, you would then save it before returning to Monarch. (You dont need to do so for this lesson.) 4. Exit your word processor without saving.
Exporting a Chart
Monarch now features chart exporting abilities. Steps: 1. Select Export from the Home tab. The Export Wizard appears. You will observe that the Chart radio button has been selected for you. 2. Click Next.
The Export Wizard Chart View screen displays.
Figure 14-25. The Export Wizard Chart View screen.
From this screen, you can select the chart to export, its exported size, and the file extension to which the chart will save. 3. 4. For conflicts, select Overwrite file from the When output files exist options. Click Run. A progress dialog displays to inform you of the progress of the export. When the export has been completed, the message Export(s) completed is returned. 5. 6. Click Close on the progress dialog and then click Close on the Export Wizard. Navigate to the Exports folder (typically in C:\Users\Public\Documents\Monarch\Exports). Because you saved the charts with a .PNG extension, each chart can be viewed as an image. The exported charts can be embedded into your spreadsheet, for example, as regular images.
Printing a Chart
When you print a chart, the entire chart is printed, not just that portion visible on screen. If a chart is too large to fit on a single printed page, Monarch will break the chart across two or more pages. Lets print our chart. Since the chart extends horizontally beyond the limits of the screen, well start by setting the paper orientation to Landscape to ensure that we get as much of the chart on each page as possible. For Horizontal Bar charts, which extend in the vertical direction, a Portrait orientation would be a better choice. If you have a color printer or a black and white printer that converts colors to gray-scale well, you can print the chart as a color image. Otherwise you may want to convert chart colors to hatch patterns. For this lesson, well convert the chart colors to hatch patterns before printing.
Steps: 1. 2. On the Chart Properties panel, tick the box labeled Convert chart colors to hatch patterns. The chart is redisplayed using hatch patterns. Right-click on the chart tab and then select Print on the context menu options that display. The Print dialog displays. 3. 4. 5. Select Preferences and then click on the Layout tab. From the Orientation drop-down box, select Landscape and then choose OK. Right-click once more on the chart tab, select Print, and then select Print from the Print dialog box. Monarch prints the chart.
Saving Your Work

You have just completed Chapter 14. We recommend you save your work in a Monarch model file
Steps: 1. 2. 3. 4. Select File, and then click on the arrow of the Save As Select Model. Type Charts in the File name box of the Save Model As dialog box that displays, and then choose Save. Select File and then click Exit Monarch. menu.
Summary
In this chapter, you learned how Monarch creates charts from your summary data. You also learned how to modify chart properties, add a title to a chart, specify chart colors and fonts, and copy and print charts. For further reading, see the following sections of the Monarch Help file: Chapter 4 - Summaries Charting Summary Data
Congratulations! You have just completed Monarch basic training. The next chapters deal with advanced Monarch topics. If you had little difficulty up to now, you should forge ahead. Even if you struggled a little with the previous lessons, dont despair. The advanced topics arent actually more difficult they merely address product capabilities that fall outside of normal, everyday use. If you dont feel ready to tackle five more lessons at this point, take a break and come back to them later. You can also just follow along in the book to gain a better understanding of Monarchs full capabilities.
[15] Working with Multiple Instances of a Report

In the previous chapters, weve worked with only one report at a time. In this chapter, you will learn how to open and analyze data from multiple reports. The lesson topics include: Analyzing data from a series of reports Opening multiple instances of a report Extracting data Working in the Data View window Creating a summary to analyze data from multiple reports
Analyzing Data from a Series of Reports

In most organizations, reports are run on a regular basis -- typically weekly or monthly. Each instance of a report contains information about a specific period of time. In addition to producing a new report each week or month, some organizations produce a separate report for each department, division or region. For example, a retailer might divide its market into four distinct regions: Northeast, Central, South and West. A monthly sales report is generated for each region.
Figure 15-1. Each report includes information for a specific region.
Each instance of the report contains useful sales information for a single region, but it does not tell you how a particular product is selling nationally. For that, you will need to analyze data from all four regions. With Monarch, you can open all four regional reports and create a summary showing sales of the product within each region with a grand total for all four regions. The resulting summary might look like Figure 15-2.
Figure 15-2. Comparing product sales across four regions.
By analyzing data from all four regional sales reports, you can compare sales results for each region. Since each sales report is generated on a monthly basis, our analysis compares sales for only a single month. We can add another dimension by opening several monthly sales reports for each region. Figure 15-3 compares sales results by region broken down by month for a three month period.
Figure 15-3. Comparing product sales across four regions for a three-month period.
To create this summary, we opened a total of 12 reports 3 monthly reports for each of 4 regions (see Figure 15-4).
Figure 15-4. Each report contains information for a specific region and time period.
As weve seen, each report contains only a piece of the overall puzzle. But taken together, these same reports can reveal a wealth of information about your business.
Starting the Lesson

In the previous chapters, youve become familiar with the Classic.prn report. Classic.prn is a periodic report produced on a monthly basis. Each report contains shipments of classical music recordings for a single month. For this lesson, weve provided three instances of the Classic report representing shipments for the months of January, February and March.
Opening Multiple Instances of a Report

The procedure for opening multiple instances of a report is similar to that required to open a single report. Lets start by opening the January shipping report.
Steps: 1. 2. 3. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report. Click on ClassJan.prn, and then choose Open. Januarys shipping report displays in the Report window.
Figure 15-5. Classical Music Distributors January shipping report.
Next well open the February shipping report. 4. 5. Select File, click on the arrow of the Open menu, and then select Report. Click on ClassFeb.prn, and then choose Open.
For each additional report you open, Monarch displays the Retention Options dialog. This dialog prompts you to discard or retain any currently open reports and model parameters before opening the new report.
Figure 15-6. The Retention Options dialog.
To replace the existing report and model with a new report, you would leave the Retain Current Reports and Retain Current Model check boxes unchecked. Since we want to open a new report without closing the currently open report, well choose to retain the current reports. 6. Select Retain Current Reports, and then choose OK. The February shipping report replaces the January report in the Report window. Note that Monarch does not create a separate window to display each report. Instead, all open reports are viewed in one Report window, with a single report displayed at a time. When two or more reports are open at the same time, Monarch displays the most recently opened report. You can select another report to view by clicking on it in the Document Explorer. The Document Explorer lists all of the open reports, tables, and summaries in each Monarch session. So far we have opened two reports: ClassJan.prn and ClassFeb.prn. ClassFeb.prn was the last report opened so that report is currently displayed in the Report window. Now lets open the March shipping report. 7. 8. Select File, click on the arrow of the Open menu, and then select Report. Click on ClassMar.prn, and then choose Open. This time the Retain Current Reports check box is already selected.
Figure 15-7. The Retention Options dialog retains your previous settings.
By preserving your most recent dialog settings, Monarch saves you a step each time you open a new instance of the report. 9. Choose OK. The March shipping report is opened and displayed in the Report window.
Extracting Data
When you load a model file or create a new model, Monarch applies the model parameters to all open reports. Data extracted from each report is concatenated in the Data View window.
Note
Although you can open multiple report files at the same time, Monarch allows you to open only a single model file. The model file is automatically applied to all open reports. Thus, all reports must have the same format. For example, although the shipping reports used in this lesson all contain different data, they all share the same structure. You cannot extract data from two different types of reports at the same time.
Steps: 1. 2. Select File, click on the arrow of the Open menu, and then select Model. Open Lesson13.xmod. Monarch applies the model to the January shipping report and updates the Report window.
Figure 15-8. The Lesson 13 model applied to the March shipping report.
Even though Monarch displays only one report at a time, it always applies the model parameters to all open reports. Data extracted from each report is concatenated in the Data View window to form a single database. 3. On the Document Explorer, expand the Table node and select Data View.
The Data View window appears, including records extracted from all three shipping reports.
Figure 15-9. The Data View window includes data from all open reports.
4.
Select Autosize Columns properly.
from the Table tab to view all the fields
Working in the Data View Window

Working with data from multiple reports is virtually the same as working with data from a single report. You can view, sort, filter, export and print the data just as though it were extracted from a single report. There are, however, a few differences that should be examined.
Sorting
Initially, the data is arranged in the order that the report files were opened. Since we opened the January report first, the January records appear first in the table followed by February and then March. This initial order has no particular relevance. In fact, it may not be desirable at all. By arranging the records this way, Monarch may be violating the natural sort order of the reports. For example, each of our shipping reports is sorted by customer and then by ship date. By concatenating the records from all three reports, weve added a higher sort level. Now the data is sorted by period (month) and then, within period, by customer and ship date. Lets restore the original sort order found in the reports.
Steps: 1. From the Home tab, Data Definitions group, click on the drop-down button of the Sorts tool and then select New from the Sorts menu.
The Sort Order Definition dialog displays.
Figure 15-10. The Sort Order Definition dialog.
2.
Type Original Sort Order in the Name box.
3. 4.
Select the Customer field from the Available Fields box then click the Add >> button. Select the Ship Date field from the Fields box then click the Add >> button. The table is rebuilt, sorted by customer and then by ship date.
5. 6.
Select OK to accept your changes. Select Autosize Columns properly. from the Table tab to view all the fields
Figure 15-11. Sorting by Customer then Ship Date.
By using Customer as the first sort field, Monarch groups all the records from each customer together, regardless of which report each record was extracted from (note the highlighted ship dates for Bettys Music Store which range from January 10th through March 4th - spanning all three reports).
Identifying the Source of Each Record

There are times when you will need to know from which report each record is extracted. You may want to apply a filter that excludes some or all of the records from one or more reports or create a summary that generates subtotals broken down by period. In either case, Monarch will need to be able to identify the source report for each record. Sometimes, this information is available within the reports. In our shipping reports, each page header contains the date the report was printed. By extracting this information as a field, you can easily identify the source of each record.
Figure 15-12. The report run date is included in the page header.
For reports that lack a run date or other unique identifying information, Monarch provides a pair of functions - File() and ID() - that you can use to identify the source of each record.
THE FILE() FUNCTION

The File() function returns the path and filename of the report file from which each record was extracted. For example, for records extracted from the Classjan.prn file, the File() function would return the value "C:\Reports\Classjan.prn", assuming that Classjan.prn is located in the \Reports folder on drive C. Lets create a calculated field using the File() function.
Steps: 1. In the Home tab, Data Definitions group, click on the drop-down button of the Calculated Fields tool, and then select New.
The Calculated Field Style dialog displays. 2. Select the Formula-based radio button, then press OK.
The Field Properties dialog displays.
3. 4. 5. 6.
In the Name box, type Filename. From the Type drop-down list, select Character. Enter 60 in both the Display Width box and the Data Length box, and then select the Formula tab. Select the File() function from the Functions list, then choose Insert (or double-click on the File function). The File() function is inserted into the Expression box.
7.
Choose OK to close the Field Properties dialog. The new field is added to the end of the table. To view the field, scroll to the right by clicking twice in the horizontal scroll bar to the right of the slider.
Figure 15-14. The Filename field, including the path.
For each record, the Filename field returns the entire path and filename of the report from which the record was extracted. Lets use another function to reduce the field values to only the file name.
Steps: 1. Double-click anywhere on the Filename field. The Field Properties - Filename dialog displays. 2. 3. 4. Enter 12 in both the Display Width box and the Data Length box. Select the Formula tab, and then click the Clear button. Type the following expression in the Expression box:
RSplit(File(),2,"\",1)
This expression separates the path from the filename and returns the filename by itself. 5. Choose OK to accept the new field definition. Now the Filename field displays only the name of each report file.
Figure 15-15. The Filename field, excluding the path.
The ID() Function

The ID function assigns a numeric designation to each report at the time the report is opened. The first report opened is assigned 1, the second 2 and so on. If a report is subsequently closed, the list of designations is collapsed to fill any gap in the assigned numbers. Lets create another calculated field using this function.
Steps: 1. In the Home tab, Data Definitions group, click on the drop-down button of the Calculated Fields tool, and then select New.
The Calculated Field Style dialog displays. 2. Selected the Formula-based radio button, then press OK. The Field Properties dialog displays. 3. 4. 5. 6. Type File ID in the Name box, and then select Numeric from the Type drop-down list. Select the Formula tab. Select the ID() function from the Functions list, then choose Insert. Click OK to close the Field Properties dialog. The File ID field displays as a new column in the table.
Figure 15-16. The Filename and File ID fields.
Since ClassJan.prn was the first report we opened, it is assigned 1. ClassFeb.prn was the second report opened, so it is assigned 2 and ClassMar.prn is assigned 3. By opening the reports in another order, we could change the designations. You can use the ID function to order records in the table or in a summary based on the order in which you open the files, rather than alphabetically by file name.
Creating a Summary to Analyze Data from Multiple Reports

The Summary window provides a powerful tool for analyzing information from multiple reports. Since Monarch summarizes data from all open reports, its easy to draw comparisons, perform roll-ups and spot trends in the data. For this lesson weve included a summary definition - Sales By Month - that generates sales totals for each customer broken down by month. Lets build the Sales by Month summary.
Steps: 1. From the Document Explorer, select the Sales by Month summary. The Sales by Month summary is built and displayed.
Figure 15-17. Summary showing sales for each customer for three monthly periods.
This summary shows sales for each customer broken down by period. The right-most column shows the total sales for each customer for the entire three-month period and the bottom row shows the total sales for all customers for each month.
You could use this same model with a single instance of the shipping report or with any number of instances of the report. You could also roll forward a single month by closing the January report and opening the April report. The summary would then show subtotals for February through April.
Saving Your Work to a Model File

You have just completed Chapter 15. We recommend you save your work in a Monarch model file. The model file can be used with the same reports or with future instances of the report to reproduce the summary analysis.
Steps: 1. 2. 3. Select File, click on the arrow of the Save As Model. menu, and then click
Type Q1 in the File name box of the Save Model As dialog displays, and then choose Save. Select File once more, and then click Exit Monarch.
Summary
In this chapter, you learned how to extract and analyze data from multiple instances of a report. For further reading, see the following sections of the Monarch Help file: Chapter 2 - Reports Working with Reports Opening Multiple Instances of a Report Closing Reports Chapter 3 - Tables Functions
In the next chapter, youll learn what multiple line fields are and how to extract them from reports and work with them in Monarch.
[16] Extracting Multiple Line Fields

In many reports, the majority of fields are single line fields - character, numeric and date fields that occupy space on a single. But some reports contain large fields that span multiple lines. These multiple line fields are variously known as comment fields, description fields, memo fields, text blocks or word wrapped fields. In this chapter, youll learn how to extract a multiple line field from a report and work with it in Monarch.
Starting the Lesson

To get started, load Monarch and open Homes.prn and Lesson14.xmod.
Steps: 1. 2. 3. 4. 5. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then choose Report. Open the Homes.prn report. Select File once more, click on the arrow of the Open menu, and then choose Model. Select Lesson14.xmod, and then choose Open. The report and model are displayed in the Report window.
Figure 16-1. The Homes real estate report contains new property listings.
Homes.prn is a weekly real estate report describing properties that were offered for sale during the previous week. For each property, the report includes the address, style, price, listing broker and numerous other points of interest to prospective buyers. Chief among these is a description of the property provided by the listing broker. The Remarks field is typical of multiple line fields found in many reports. Each instance of the field contains several lines of descriptive text - some of the descriptions are only a few lines long and several are as many as nine lines.
The Lesson 14 model contains a detail template that captures several fields from each property listing. The first line of the multiple line Remarks field is included in the template, but that field is not yet captured. Well capture the Remarks field together to illustrate how to capture a multiple line field. 6. 7. On the Report tab, Templates group, click on the drop-down button of the Templates tool. Highlight the Detail template on the menu by clicking it once, and then select Edit. The Template Definition dialog appears, which displays several lines of the detail template (The detail template contains a total of 16 lines).
Figure 16-2. The Template Definition dialog displays several template sample lines.
We can scroll the template sample to view the remaining sample lines. The last line of the template contains the beginning of the Remarks field. Lets scroll down until that line is displayed. 8. Scroll down in the Template Definition dialog until the Remarks field is visible, as in Figure 16-3.
Figure 16-3. The last template sample line represents the beginning of the Remarks field.
Lets also scroll the report to display the first instance of the Remarks field. Having this field displayed on screen will provide valuable feedback during the capture process.
9.
Use the Report windows vertical scroll bar (the lower of the two vertical scroll bars that are visible on screen) to scroll down so that the Remarks field is fully visible on screen, as shown in Figure 16-4.
Figure 16-4. Displaying the multiple line Remarks field in the Report window to provide feedback.
Now were ready to capture the multiple-line Remarks field.
Capturing a Multiple-Line Field

To capture a multiple-line field, you need to tell Monarch where the field begins and ends. Typically, locating the beginning of a multiple line field is easy - most multiple line fields begin at an absolute offset from the first line of the template. Locating the end of a multiple line field is sometimes more difficult. Since the number of lines in the field often varies, you cant tell Monarch how big the field is in absolute terms. Instead, you must describe some characteristic that is common to the end of the field. For example, the field may end when a blank line is reached or when another field is encountered. Monarch includes these and several more options that you can use to indicate where your multiple line field ends. Start by highlighting the first line of the field.
Steps: 1. In the sample edit box, use the mouse or the keyboard to highlight only the first line of the Remarks field. Highlight column positions 16 through 78. The column position will be displayed in the status bar when you click down in the Template Definition dialog. The field highlight should look like that shown in Figure 16-5.
Figure 16-5. The last template sample line represents the beginning of the Remarks field.
Note that the first line of the field in the report is now highlighted.
Figure 16-6. The Template Definition dialog displays several template sample lines.
You might be wondering why we highlighted only the first line of the field. Why not highlight the entire field? For a multiple line field, we must indicate the horizontal size of the field and the vertical size. The horizontal size is indicated by highlighting the first line of the field. However, the vertical size cannot be indicated by highlighting multiple lines because the number of lines occupied by each instance of the field varies. What would be correct for the first instance of the field may be incorrect for the others. To indicate the vertical size of a multiple line field, we must tell Monarch where the field begins and where it ends. To do this, well use the Field Properties dialog. 2. Double-click on the field highlight in the Template Definition dialog. The Field Properties dialog appears. Initially, the General tab displays the sample field value for the selected field along with the current field name. The Field Properties dialog also contains an Advanced tab, which includes options for defining a multiple line field. 3. Click the Advanced tab.
Figure 16-7. The Advanced tab.
The Advanced tab includes options for indicating the beginning and ending locations of the field. Lets begin by examining each of the Start Field On options. These options tell Monarch how to locate the beginning of a multiple line field. The Start Field On options are: Line Number The Line Number option indicates that the first line of the field begins at an absolute offset from the top of the template. The line number represented by this setting is determined by the position of the field highlight in the template sample. Since we painted the first line of the Remarks field at Line Number 16, this option displays as Line Number 16.
Note
The Line Number option is almost always the correct choice for indicating the beginning of a multiple line field. The only time the Line Number option would not be the correct choice is when the multiple line field begins after another multiple line field. In this case, the first multiple line field, if it contains a varying number of lines, would cause the beginning position of the second multiple line field to vary (see Figure 16-8). Only then would you need to employ one of the other Start Field On options.
String The String option indicates that the first line of the field begins on the next line down from (i.e., the line below) a line containing a string. This option is particularly useful when trapping HTML data. To use this option, enter the string in the adjacent box. Preceding String The Preceding String option indicates that the first line of the field begins to the right of a string, such as a label. To use this option, you simply enter the string in the adjacent box. Figure 16-8 shows a modified version of the Homes report where the Remarks field falls after another multiple line field, the Directions field. Here the Line number option would not work because the varying length of the Directions field would cause the beginning of the Remarks field to bob up and down. In this case, the preceding string option could be used by entering the label "Remarks:" (without the quotation marks).
Figure 16-8. Offset of Remarks field varies due to preceding multiple line Directions field.
After Last Defined Field in Template The After Last Defined Field option indicates that the first line of the field begins two lines below a previous multiple line field. Figure 16-9 is an excerpt from Figure 16-8, except this time there is no identifying label next to the Remarks field. In this case, you could use the After Last Defined Field option to indicate that the Remarks field begins immediately after the Directions field. To do this, you must also capture the Directions field so Monarch will know where it ends.
Figure 16-9. Use After Last Defined Field if no label exists to mark the beginning of the second multiple line field.
Since the Remarks field always begins on the same line in the template (Line Number 16), well use the Line Number option to indicate where the field starts. Line Number is the default option, so its already selected. Next, we need to indicate where the multiple line field ends. To do this, well select an End Field On option. The End Field On options tell Monarch how to locate the end of a multiple line field. Lets examine each option. Line Count The Line Count option terminates a multiple line field after a fixed number of lines. This option should be used only when every instance of the field has the same number of lines. This wont do for our Remarks field, as each instance contains a different number of lines. Nonblank Preceding String The Nonblank Preceding String option terminates a multiple line field when any nonblank character appears within a specified number of columns to the left of the field. Typically, the character is part of a label that appears to the left of a subsequent field. As an example, lets suppose our fictitious Directions field appeared immediately after the Remarks field rather than before (see Figure 16-10). With no blank line to separate the fields, it is difficult to know where the Remarks field ends. But there is a label, "Directions:", preceding the first line of the Directions field. We can use this label to terminate the Remarks field by selecting the Nonblank Preceding String option and specifying 3 or more columns.
Monarch will look for any nonblank character in these columns, terminating the field when it encounters the colon (:) in the "Directions:" label.
Figure 16-10. Use Nonblank Preceding String to terminate a field when another field is encountered.
The Nonblank Preceding String option cannot be used to indicate the end of the Remarks field in the report that we are using for this lesson because there is no label in an appropriate position near the end of the Remarks field. Blank Field Values The Blank Field Values option terminates a multiple line field when Monarch encounters a single blank line or a pair of blank lines. Monarch does not require the entire line to be blank, only the column positions immediately under the field. Since our Remarks field is always followed by a blank line, this option is a good choice. In the case of a large text block containing multiple paragraphs, like the one shown in Figure 16-11, you would indicate 2 blank lines - to prevent Monarch from terminating the field when it encounters the blank line that falls between the first and second paragraphs.
Note that using two blank lines to terminate a multiple line field is possible only when the entire text block is followed by at least two blank lines, as is the case in our example below.
Figure 16-11. Example of a multi-paragraph text block with a single blank line between paragraphs.
End of Left Justification This option terminates a multiple line field when Monarch encounters a line with a blank in the first column position of the field or any nonblank character in the column immediately preceding the field. Either condition indicates that left justification within the field has ended. This option is useful for capturing left aligned text blocks. However, if the text block contains a blank line, such as that found between paragraphs, Monarch will consider the blank line an end to left justification and will therefore terminate the field. Although this option could be used to terminate the Remarks field, the Blank Fields option is preferable as it better describes the way that the field is actually terminated. None of the Above This option terminates a multiple line field only when Monarch encounters another template, including another instance of the template in which the multiple line field resides. Monarch will also terminate a multiple line field after it extends two pages. The field will be terminated on the second page where the page break character (ASCII 12) is encountered. This prevents a field from continuing without end if the selected End Field On action is not appropriate to end the field. This option should be used only when none of the other End Field On options are suitable. By employing the minimum actions, you might capture more data than is actually occupied by the field.
Note
The minimum actions described above are always enforced by Monarch, even when the None of the Above option is not selected. Select this option only when none of the other options would apply.
From our review of each of the End Field On options, we find that either the Blank Field Values option or the End of Left Justification option will properly terminate the field. We recommend using the Blank Field Values option in such cases, since it is more descriptive of how the field ends. 4. Select the Blank Field Values option to indicate to Monarch that the field will end when a blank line is encountered. Lets also name the field Remarks. 5. 6. Click the General tab and type Remarks in the Name box. Select OK to accept your selections and close the dialog. Note that Monarch has correctly highlighted the first instance of the Remarks field.
Figure 16-12. Testing the multiple line field definition.
Lets double check the field definition by inspecting several more instances of the multiple-line field. 7. Scroll down in the report to the next Remarks field.
Note that the second instance of the Remarks field is also correctly highlighted.
Figure 16-13. Double checking the multiple line field definition.
8.
Select OK to accept your changes and close the Template Definition box.
Displaying a Multiple Line Field in the Data View Window

Now that we are satisfied that the multiple line field is captured properly, well extract the field and display it in the Data View window.
Steps: 1. Expand the Table node in the Document Explorer, and then select Data View. The Data View window appears. The Remarks field displays at the far right. 2. Click once in the body of the horizontal scroll bar to display the Remarks field.
Figure 16-14. Displaying the Remarks field in the Data View window.
The initial field type assigned to the Remarks field is character with a data length of 254 - the maximum data length for a character field. The column width is set to the fields template width, which in this case is 62 characters. These default settings are appropriate for multiple line fields that extract a small amount of data, i.e., when the largest field value is less than the 254 character maximum for a character field. For multiple line fields that extract large blocks of text, however, such as our Remarks field, the 254character limit will cause some of the field values to be truncated and the display settings dont let us see very much of each field value on screen.
Memo Fields
To accommodate multiple line fields that contain more than 254 characters, Monarch includes support for memo fields which can handle up to 65,536 characters (64KB). Lets change the field type of the Remarks field to Memo.
Steps: 1. Double-click any cell in the Remarks field. The Field Properties dialog appears.
Figure 16-15. The Field Properties definition dialog.
2. 3.
Click the drop-down button on the Type box and select Memo. Choose OK to close the Field Properties dialog. The Remarks field is redisplayed as a memo field.
Viewing Memo Fields

To view more information for each memo field, Monarch lets you adjust the height of each row. Lets try doing this.
Steps: 1. In the left hand side of the Data View window, click on the border between Rows 1 and 2, then drag down to expand the height of the rows to at least six lines. Even using a row height of 6 lines, we still may not be able to see all of the text in some of the Remarks fields. To view more information, we could increase the width of the field, but this may still not allow us to view the entire contents of each cell. Although we could adjust the row height again (Monarch allows a maximum row height of 12 lines), lets try something new instead. Lets give Monarchs Show Field Contents feature a try, which displays the entire contents of a cell. 2. Select Show Field Contents from the View group of the Table tab.
Monarch opens a small window at the top of the display. This window, called the Field Contents window, displays the entire contents of the selected table cell. Initially, the window height is only a few lines, but you can increase the height to show more lines and you can scroll within the window to view the entire contents of a cell.
Figure 16-16. The Field Contents window displays the entire contents of the selected cell.
3.
If Field Contents window is too small, position the mouse cursor on the border between the Field Contents window and the Data View window. The mouse pointer should become a resizing handle (a pair of horizontal bars with an up facing arrow and a down facing arrow).
4.
Click down on the border, then drag down to expand the Field Contents window so that it is at least 6 lines tall.
5.
Release the mouse button to drop the border in place. As you move the cell pointer from cell to cell in the Data View window, the Field Contents window will display the contents of the selected cell. You can try this by using the down arrow key on the keyboard to scroll through the Remarks field cells.
6.
Press the down arrow several times to view the entire contents of each of the Remarks field cells.
Using Memo Fields

Monarch does not allow a memo field to be used in a sort or summary definition. If you intend to include the field in a sort or summary definition, you must leave the field type set to Character, or on rare occasions, change it to Numeric or Date. In addition, there are differences in the way a memo field is exported and printed versus a character, numeric or date field. These differences may or may not provide the results you expect or want. Well touch on the export and print issues later in this lesson.
USING MEMO FIELDS IN CALCULATED FIELD AND FILTER EXPRESSIONS

As stated above, you cannot use a memo field in a sort or summary definition. However, you can use a memo field as part of a calculated field expression. By creating a calculated field, you can extract a subset of the memo field that you can then use in a sort or summary definition. You can also use a memo field in a filter expression. Well illustrate this using the Remarks field. Lets suppose the Real Estate agent has a client interested in purchasing a new home and, among the clients other criteria, she wants a home with a fireplace. Some of the property descriptions mention a fireplace and others do not. You could manually review all of the Remarks cells in the table to find the ones that mention a fireplace, but this is a tedious process, especially if there are hundreds of homes for sale. To find out which homes have a fireplace you can create a filter that will search the entire Remarks field and return only those records where a fireplace is mentioned.
Steps: 1. On the Table tab, Data group, select the drop-down button of the Filters tool, and then select New. The Filter Style dialog displays. 2. Select Formula-based, and then click OK. The Filter Definition dialog appears. 3. 4. Type Homes with a Fireplace in the Filter Name box. Type Instr("fireplace",Remarks)>0 in the Expression box.
The Instr() function will return the character position of the word "fireplace" in the Remarks field. By setting the filter to >0, we are capturing all cases where it appears anywhere in the description text. 5. Choose OK to accept your filter and close the Filter Definition dialog. Monarch applies the filter, which returns 19 records. That may still be more property descriptions than you want to read through, so lets narrow the choices by adding to the filter definition. While were already supposing, lets further suppose that the client refuses to live in any town other than Tewksbury. 6. Click the drop-down button of the Filters tool, select Homes with a Fireplace from the Filters list box, and then select Edit to edit the filter definition. The Filter Definition dialog appears. 7. Add .And.Town="Tewksbury" to the filter expression. The entire expression should now read:
Instr("fireplace",Remarks)>0.And.Town="Tewksbury"
Figure 16-17. Viewing the edited filter expression.
8.
Choose OK to close the Filters dialog.
This time Monarch returns only three records representing homes with fireplaces in the town of Tewksbury. Lets use the Display Source of Record feature to browse the original descriptions in the report.
Figure 16-18. Memo fields used in filter expressions.
9.
Click on any cell in the first row of the table. button from the Table tab, View group.
10. Select the Find in Report
Monarch displays the associated property listing in the Report window.
Figure 16-19. Displaying the source of a multiple line field.
11. On the Document Explorer, click on Data View. You may repeat Steps 910 to view each of the other property listings. In Step 9, click any cell in the second row to display the second listing or any cell in the third row to view the third listing.
Exporting Memo Fields

Now that you have found three homes that meet your criteria, you might want to extract this information to a text file or print it. Well start by exporting the table to a text file.
Steps: 1. While viewing the Data View window, go to the Home tab, Export group and then select the Export tool.
The Export Wizard appears, displaying the General screen.
2.
Click the Next button. The Table View screen of the Export Wizard displays.
Figure 16-21. The Table View screen.
Note that under the Filtering heading, the filter currently applied to the table Homes with A Fireplace is displayed. 3. 4. 5. Click the Next button to display the Output File screen. In the Save As field, enter Homes with a Fireplace.txt. Click the Run button. Monarch displays a progress dialog when the export is complete. 6. Select Close to close the progress dialog, and then select Close once more on the Output File screen to close the Export Wizard. Lets examine the file using the Windows Notepad utility. 7. 8. 9. Select the Notepad item from the Windows start menu. Select File, and then click Open to display the Open dialog. Navigate to the Monarch\Exports folder. This folder is typically C:\Documents and Settings\All Users\Documents\Monarch\Exports, but it may be another folder if you changed the default folder settings for Monarch.
10. Ensure that Text Documents (*.txt) is selected in the Files of Type dropdown list. 11. Select Homes with a Fireplace.txt, then choose Open. Notepad displays the text file.
Figure 16-22. Viewing the export file in WordPad.
When Monarch exports a memo field to a text file, it starts the memo field on its own line. The field appears in the export file in the same format as it appeared in the original report. By filtering and exporting the extracted data, we were able to create a personalized subset of the original report.
Monarch handles other export formats differently, according to their capabilities. For example, the XLS format cannot accept a field larger than 255 characters, so Monarch accommodates this file type by exporting a memo field as a character field. If the field contains more data than the export file type can hold, Monarch exports the maximum amount of data for that file type, and then truncates the rest. When exporting to DBF, DB, or MDB formats, Monarch exports separate memo files along with the database file. For more information about exporting fields to each supported export file type, refer to the Monarch Help file, Chapter 3 - Data View window, Exporting Table Data. 12. Select File, and then click Exit to quit Notepad.
Printing Memo Fields

When you print a memo field, Monarch treats the field as though it were a character field: it prints only that portion of the field that is visible in the Data View window. This produces a print image that reflects the data you see on screen. To print the entire contents of a memo field, you can export the table to a text file, and then use a text editor or a word processor to print the file.
Saving Your Work

You have just completed Chapter 16. We recommend you save your work to a Monarch model file.
Steps: 1. Select File, click on the arrow of the Save As Model. The Save Model As dialog displays. 2. 3. Type Homes into the File name box, and then choose Save. Select File, and then click Exit Monarch. menu, and then select
Summary
In this chapter, you learned how to extract a multiple line field from a report. You also learned how to set the field type to Memo and how Monarch exports and prints memo fields. For further reading, see the following sections of the Monarch Help file: Chapter 2 - Reports Creating Data Extraction Templates Capturing a Multiple Line Field Chapter 3 - Tables Working with Fields Changing a Fields Type Exporting Table Data Text File Export Rules Delimited Text File Export Rules Database File Export Rules Spreadsheet File Export Rules
[17] Importing Data from HTML and External Databases

This chapter, and the ones that follow, deal with advanced capabilities found in Monarch. Monarchs data access capabilities include data from external data sources, such as MS-Excel, MS-Access, dBASE, and Paradox files, ODBC data sources, and delimited text files. It also enables you to import PDF, XPS and HTML files. In this chapter, you will learn how to import data from an external data source to begin a Monarch session. You will also learn how to import HTML into Monarch. The lesson topics include: Accessing database data with Monarch Importing data from an external database Adjusting fields Storing import parameters in a model file Storing import parameters in a project file Importing data from an HTML file
Accessing Database Data with Monarch

For most of its history, Monarch has been the leading report mining tool available in the market, allowing access to data buried in computer generated reports. Monarch transforms static report data into live data that you can explore, analyze, and export to other applications. While its ability to re-deploy data locked inside reports has been the major factor distinguishing Monarch technology from other data access technologies, Monarch also includes powerful data manipulation, analysis, and transformation tools that potentially make it valuable for use with other data sources. Monarch reads report files and data from database files, spreadsheet files, delimited text files, HTML, ODBC databases, PDF and XPS files, and provides report extraction, data manipulation, analysis, and transformation capabilities. As well, data access from additional sources, including dBase, Paradox, MSExcel, and MS-Access files, OLE DB and ODBC compatible data sources, such as SQL Server, Oracle, DB2, and others, is possible.
Importing Data from an External Database

To illustrate Monarchs database import capability, well import data from an MS-Access database file called Employ.mdb. This file contains information about the employees of a fictitious company. Well use Monarch to open the file and import data from its Employee Roster database table.
Steps: 1. 2. 3. Select the Monarch item from the Windows Start menu. Select File, and then click on the arrow of the Open menu. Select Database from the Open options that display. The Database Wizard opens, displaying the Data source selection screen. This screen allows you to select the data source, either a data file from a local or network drive or an OLE DB/ODBC data source, such as an SQL Server, Oracle, or other OLE DB/ODBC compatible database server.
Figure 17-1. Data Source Screen Selection of Monarchs Database Wizard.
The first Wizard screen, shown in Figure 17-1, prompts you for the database where your data resides. From this screen, you can browse to locate a database or select a previously used database from the Data Source drop-down list. Lets use the browse button to open Employ.mdb. 4. Click the Browse button to the right of the Data Source field and select Local or network file to indicate the data source type. This option refers to files that you can access locally via the standard Windows File Open dialog. The Open Database File dialog displays.
You use this dialog to select the database or spreadsheet file that holds the data you wish to import.
Figure 17-2. Using the Select File dialog to open a local database or spreadsheet file.
Monarch can import data from the following local file formats:
File Format Versions Extension
MS-Access
1.0, 1.1, 2.0, 3.0, 4.0 (Access 2000 format), 2007 3.0, 4.0, 5.0, 8.0 2007 III, IV, 5.0 3.X, 4.X, 5.X, 7.X (requires Paradox 7 installed) WKS, WK1, WK3, WK4
MDB
MS-Excel MS-Excel dBASE Paradox
XLS XLSX, XLSM DBF DB
Lotus Delimited text files HTML PDF files XPS
WKx CSV, TAB, ASC HTM, HTML, ASP
1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6 1.0
PDF XPS
If you wanted to import from an OLE DB compatible database (and you have the driver installed), you would select the OLE DB connection option, then use the Data Link Wizard to make the connection to your database, or select the Data Link File to use an existing data link file to use as your connection information. Note that OLE DB is usually faster than ODBC, so if you have a choice of connections to your database, choose OLE DB. If you have existing projects and models that use ODBC, then you may want to change the connection method to benefit from enhanced performance.
If you wanted to import data from an ODBC compatible database you would select the ODBC connection option, and then use the Select a DSN dialog that displays to select the ODBC data source name (DSN) that connects to your ODBC data source. Monarch can import data from ODBC data sources for which you have established an ODBC Data Source Name definition (referred to as a DSN). Monarch does not create a DSN for you you must create a DSN using the Windows Control Panel ODBC Data Sources applet or a similar utility. 5. 6. Select the Employ.mdb file from the Monarch\Data folder (typically C:\Documents and Settings\All Users\Documents\Monarch\Data). Click the Open button to open the data source. Monarch displays the path to the Employ.mdb file in the Database Wizards Data Source box.
Note
This screen also includes an Open Model button which you can use to open a model file to apply to the database. Applying a model to a database is similar to applying a model to a report. The model holds parameters that indicate which fields to extract from the database, along with field properties for each field and filter, sort, calculated field, and summary definitions. Later in this lesson, well show you how to save a model that includes import parameters and how to use the model in a subsequent Monarch session.
7.
Click the Next button to advance to the next Wizard screen. The Wizards Dataset selection screen appears. This screen displays all of the database tables and queries that are available within the selected database. The list does not include system tables or queries, which hold information about the database structure.
Figure 17-3. Use this screen to select the database query or table from which the data will be imported.
Our sample Employ.mdb database file holds only a single database table, called Employee Roster, which is already selected. 8. Click the Next button to advance to the next Wizard screen. The Wizards Row definition screen appears. This screen displays a preview of the table that has been chosen for importing.
Note
When importing from a delimited text file, this page also provides controls for specifying parameters.
Figure 17-4. The Row Definition screen provides a preview of the table.
Note
If you wanted to apply a filter to the data, you could do so by selecting the Apply Filter check box, clicking the Change button, and then specifying a filter via the Source Filter dialog.
9.
Click the Next button to advance to the next Wizard screen.
The Wizards Column selection screen appears. This screen lists the names of the database columns that are available in the selected table or query.
Figure 17-5. The Column Selection includes all available database columns that you can import to Monarch.
Well import all of the database columns. 10. Click the Select All button to select all of the available columns. Default names, taken from the source column names, are assigned to the import columns and are displayed under the Data View window Name heading. These are the names that will be assigned to the columns when you import them into Monarch.
Note
You can manually edit the import column names by clicking on them. You can also add a name prefix to one or more of the import column names. To do so, however, you must enter the prefix in the Name Prefix box before selecting any of the columns to import. After specifying the prefix, you can then either click the Select All button or select individual columns via their check boxes. The names for the columns you chose to import will then be displayed, preceded by the name prefix (e.g., if you specified a Name Prefix of Test, for example, the import column name for the Department source column would be TestDepartment.
11. Click the Finish button.
Monarch opens its Data View window and populates it with data imported from the Employ.mdb files Employee Roster table.
Figure 17-6. Monarchs Data View window is populated with the data imported from Employ.mdb Employee Roster.
Note
The Data View window may be initially populated using data extracted from report files or using data imported from an external database. You cannot draw data from both sources at the same time to populate the table. If you start a Monarch session by opening a report file, you will not be allowed to import data from an external database in that Monarch session. Conversely, if you start a Monarch session by importing data from an external database, you will not be allowed to open any report files in that Monarch session. These two means of initially populating Monarchs Data View window are mutually exclusive.
Adjusting Fields
Once you have imported data into the Data View window, you can sort, filter, and export the data, create new calculated fields, and use the data in the Summary window where you can create summary reports that perform analyses on the data. In short, you can do anything with imported data that you can do with data extracted from a report file. Typically, the first thing that youll want to do when importing data is to inspect each field to ensure that its column width, type and other properties are set correctly. For instance, column widths for imported field are set according to the data in each column, without regard for the width of the column title. You can see an example of this in Figure 17-6, where the Gender columns values (M or F) cause this columns width to be set to just one character. While you could inspect each column to determine if the appropriate width is established, a quicker way to deal with this situation is to use the Autosize Columns command to set appropriate widths for all columns in one operation. Lets use the Autosize command to set column widths for our imported data.
Steps: 1. On the Home tab, View group, click Autosize Columns .
Each column is resized according to the columns data and its column title. Note that the widths for the Employee ID column and the Gender column have been modified to match the width of the column title, as these titles are longer than the data held by each column.
Figure 17-7. Column widths adjusted to fully display column titles.
After adjusting the column widths, you should briefly inspect each field to ensure that Monarch has established the appropriate field type, whether
character, numeric, date, or memo. When importing data, Monarch sets the type for each field according to its type in the source database. However, only a subset of the field types available in many database applications is supported, so it maps subtypes to the corresponding base type. For example, MS-Access supports several numeric field types, including Byte, Integer, Long Integer, Single, Double, etc. Monarch supports only Numeric, with General, Thousands, Currency, Percentage, and Time Span formats that determine how a number is represented on screen. When you import a numeric field from an MDB file, Monarch will always set the field type to Numeric and will assign to it a format type of General. You should inspect each numeric field to ensure that its type and other properties are set appropriately. The Net Income field is the only numeric field in our imported data set. Lets inspect this fields properties. 2. Double-click on the Net Income field to display the Field Properties dialog.
Figure 17-8. Field properties for the Net Income field.
Note that the field type is set to Monarchs basic numeric type (e.g., General) and that the decimal setting is set to zero. Lets change the field format to Thousands and the decimals to 2. 3. Click the drop-down button on the Format list to display the available field formats. Note that Monarch displays only numeric field formats. For fields imported from a database, Monarch does not allow you to change the base field type (from Numeric to Character for example).
4. 5.
Select Thousands as the new field format. Change the Decimals setting to 2. While were here, lets change the name of this field to something more intuitive.
6. 7.
Change the field name to Salary. Click the OK button to accept the changes to the field properties. The Table is updated to reflect the changes.
Figure 17-9. Salary field adjusted.
Storing Import Parameters in a Model File

After making adjustments to the imported fields, its a good idea to save your changes to a Monarch model file. The model file records the original name for each imported field, along with the Monarch field name and properties you assigned. By applying the model the next time you import data from the same database, Monarch preserves the work you did in this session. Lets save a model file and then reload the session using the model.
Steps: 1. 2. 3. 4. Select File and then click on the arrow of the Save As menu.
Select Model, and then type Import in the File name box of the Save Model As dialog that displays. Click the Save button to save the model file. Select File and then click Exit Monarch to exit the Monarch session.
Opening the Model File

Now lets restart Monarch and import the data along with the model file. Steps: 1. 2. Select the Monarch item from the Windows Start menu. Select File, and then click on the arrow of the Open menu. Select Database from the Open options that display. The Database Wizard appears. 3. 4. Click the drop-down button on the Data Source list to display a list of recently opened databases. Select the Employ.mdb file from the list. Now well open the model file to apply its parameters to the database. 5. Click the Open a Model Browse button and select Local or network file to indicate the data source type. This option refers to files that you can access locally via the standard Windows File Open dialog. The Open Model dialog displays. Select Import.xmod from the list of model files, and then click the Open button. Click the Next button to display the Wizards Dataset selection screen. Note that on the Dataset selection screen, the Employee Roster table is already selected (since this is the only table available). 8. Click the Next button to advance to the Row definition screen. This screen displays the columns in the Employ.mdb file. 9. Click the Next button to advance to the Column selection screen.
6. 7.
Monarch applies the model parameters to the Column selection screen. Note, for example, that in the Data View window Name column, Salary has been assigned to the Net Income source field in the Source Name column.
Figure 17-10. The Column Selection screen displays the effects of the Import model.
10. Click the Finish button to import the data and apply the model to it. Monarch displays the imported data in its Data View window (as in Figure 17-9). 11. Select File and then click Close All.
Importing Data from an HTML File

One of the features that distinguishes Monarch from other is its ability to utilize HTML files. With Monarch, you can import an HTML file and then create templates to extract data from it just as you would with a typical report. When you import an HTML file, Monarch parses it into a Monarch-friendly format and adds markup you may find helpful when trapping fields.
Note
Monarch is best suited to work with dynamically generated HTML pages, such as those resulting from database queries.
Lets explore Monarchs HTML capabilities.
Steps: 1. 2. 3. Select File, click on the arrow of the Open menu, and then select Report to display the Open Report dialog. From the Files of type drop-down list, select HTML files (*.htm;*.html;*.asp). Select the Airlines.htm file, and then click the Open button. Monarch parses the HTML file and displays it in the Report window. Note the markup that Monarch has added (e.g., "<Table00"). This markup can be used when trapping fields.
Figure 17-11. Viewing the Airlines.htm file.
Airlines.htm is a list of flights from Boston, MA to Manchester, England. It contains information such as the price of the flights, the airline providing them, and their earliest and latest travel and return dates. Lets scroll down the report and look for an appropriate line with which to create a detail template. Remember, a detail template is used for extracting information from the lowest report level. Notice that for each flight listed, the Latest Return information is the last given. This should do for our detail template, so lets begin by trapping the Latest Return information. 4. Locate one of the Latest Return lines in the report. In the line selection area to the left of it, click and drag so that the Latest Return line and the two lines below it are highlighted.
Note
You can easily locate the Latest Return information via the Search function.
Figure 17-12. Highlighting the Latest Return information.
5. 6.
Select the Templates drop-down button on the Templates group, Table tab, and then click New to display the Template Definition dialog. Select Detail from the Role drop-down list.
7.
In the Trap box, type Latest Return: directly above its occurrence in the Sample box, then highlight the latest return information (e.g., "1 months") in the Sample box. Extend the highlight by one more character to include instances of two-digit months.
Figure 17-13. Specifying trap characters and highlighting a field.
8.
Double-click on the highlighted field in the Sample box to display the Field Properties dialog.
9.
Type Latest Return in the Name field and then click OK to close the Field Properties dialog.
10. Replace Detail with Latest Return in the Name field on the Template Definition dialog and then click OK. The Template Definition dialog closes and the Report window displays. Scroll down the report and note that all instances of the Latest Return information are now highlighted. Now that weve defined our detail template, lets define some append templates to capture additional information from the HTML file. 11. Scroll down the report until you come to one of the price lines (e.g., USD 276.00), and then click in the line selection area to highlight it.
12. Select the Templates drop-down button on the Templates group, Table tab, and then click New to display the Template Definition dialog. 13. Select Append from the Role drop-down list. 14. In the Trap box, type USD directly above its occurrence in the Sample box, and then highlight the price in the Sample box.
Figure 17-15. Trapping the Price field.
15. Double-click on the highlighted field in the Sample Line box to display the Field Properties dialog. 16. Type Price in the Name field, and then click OK to close the Field Properties dialog. 17. Replace Append 1 with Price in the Name field on the Template Definition dialog, and then click OK. The Template Definition dialog closes and the Report window displays. Scroll down the report and note that all instances of the flight price information are highlighted. Lets see how the information weve trapped will look in the Data View window. 18. Expand the Table node on the Document Explorer and then click Data View. 19. Select Autosize Columns to view all the fields properly.
Monarch displays the two fields weve trapped as columns in the Data View window.
Figure 17-16. Viewing the fields in the Data View window.
20. Return to the Report window. 21. Using the same techniques that we used to trap the Latest Return and Price fields, try trapping some of the other fields in the HTML file (e.g., Earliest Return, Earliest Travel, Latest Travel, etc.). When youve finished trapping as many fields as you want to, view them in the Data View window to see how successful you were.
Saving Your Work

You have just completed Chapter 17. We recommend you save your work to a Monarch model file.
Steps: 1. Select File, click on the arrow of the Save As Model. The Save Model As dialog displays. 2. 3. Type Airlines1 into the File name box, and then choose Save. Select File, and then click Exit Monarch. menu, and then select
Summary
In this chapter, you learned how to import data from a database using Monarch, how to use model files with imported data and how to use a project file to streamline the import process. You also learned how effective Monarch can be at extracting data from HTML files. For further reading, see the following sections of the Monarch Help file: Chapter 5 - Accessing Database Data with Monarch Importing Data from an External Database
Chapter 9 - Project Files Using Project Files to Import Data
[18] Performing Lookups from an External Database

In this chapter, well discuss external lookups, which allow you to add columns from an external database to the Monarch table. The lesson topics include: A discussion of what an external lookup is Creating an external lookup from a report Storing lookup parameters in a model file Creating a lookup from two different reports Creating a lookup from two external databases
What is an External Lookup?

An external lookup (in technical terms, a left outer join) combines information from two or more tables into a single table. External lookups are both powerful and easy to use. In Monarch, a lookup can be used to link two or more database sources together or to link a report to one or more database sources. As an example, consider the tables on the following page. The first table lists employees in the Marketing department of a fictional company. This table includes each employees name and the city where he or she lives. The second table lists employee compensation. Both tables share a common column, called Emp_ID, designating a unique ID assigned to each employee. From the Marketing table, you could create a lookup, based on the Emp_ID column, to the Compensation table to find the salary and bonus for each employee in the Marketing department.
Marketing Table
Emp_ID Dept Lastname Firstname City
1597429 1607768 1593642 1605798 1588509
Marketing Marketing Marketing Marketing Marketing
April Banning Bartholemew Bradford Carlson
Anne David Anne Eugene Stephen
Worcester Burlington Brookfield Brookline Boston
Compensation Table
Emp_ID Salary Bonus
1587390 1588509 1593642 1596792 1597429 1604375 1605798 1607768
38,480.00 49,452.00 42,640.00 38,480.00 55,900.00 48,620.00 67,990.00 72,150.00
200.00 5,000.00 4,000.00 200.00 5,000.00 500.00 8,500.00 10,000.00
Figure 18-1. Marketing and Compensation tables with a common Emp_ID column.
To link data from two tables, you start by defining a lookup between the tables. A lookup connects the tables based on one or more columns that appear in both tables. These columns, referred to as link columns, must include at least some values that are common to both tables. In our example, the Emp_ID column would be suitable as a link column, as it contains values that are common to both the Marketing and Compensation tables.
To link these two tables, you would start with the Marketing table, and then create a lookup to the Compensation table on the Emp_ID column. Then you select the Salary and Bonus columns to add to the Marketing table. The resulting table is shown in Figure 18-2.
Marketing Table
Emp_ID 1597429 1607768 1593642 1605798 1588509 Dept Marketing Marketing Marketing Marketing Marketing Lastname April Banning Barthole mew Bradford Carlson Firstname Anne David Anne Eugene Stephen City Worcester Burlingto n Brookfield Brookline Boston Salary 55,900.00 72,150.00 42,640.00 67,990.00 49,452.00 Bonus 5,000.00 10,000.00 4,000.00 8,500.00 5,000.00
Figure 18-2. Marketing Employees table with Salary and Bonus columns linked from Compensation table.
Note that only those records from the Compensation table that have corresponding Emp_ID values in the Marketing table are included in the resulting table. Since the lookup was initiated from the Marketing table, only the records that appear in the Marketing table are included. Records in the Compensation table that have no corresponding Emp_ID values in the Marketing table are ignored. This behavior is unique to a lookup. In other types of joins, records from both tables are combined, with the resulting table having at least one record for each record in the contributing tables. Monarch doesnt support these types of joins, so no new records are added when joining data in Monarch.
Creating an External Lookup from a Report

To illustrate Monarchs database linking capability, well provide a simple example that starts with a slightly modified version of our familiar Classic report file, this one called Orders.prn. Well apply a model file to extract data from the report into the Data View window, and then well create a lookup to an external database to add several columns to the table. To get started, open Monarch and load Orders.prn and Lesson16.xmod.
Steps: 1. 2. 3. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report. Open the Orders.prn report.
Figure 18-3. The Orders.prn report does not include customer address information.
Orders.prn is a modified version of Classic.prn. Since youre familiar with Classic.prn, youll notice that weve removed the address information below each customer name. For the sake of this lesson, lets imagine that this report is always produced using only the customer name and that the customer addresses reside in a corporate database. After extracting data from the report using a standard Monarch model file, well use a lookup to retrieve the customer addresses from the database. 4. 5. Select File once more, click on the arrow of the Open menu, and then select Model. Select Lesson16.xmod from the Open Model dialog that displays and then choose Open.
6.
Expand the Table node on the Document Explorer and then select Data View. You may select Autosize Columns to properly view the table.
Figure 18-4. Extracted Report data displayed in Monarchs Data View window.
The Data View window includes all of the columns that weve extracted from the report, including each customers name and account number. Well use the Account Number column to link to the Customer.mdb database, which holds the customer addresses. 7. On the Table tab, Data group, click the drop-down button of the External Lookups tool from the menu, and then click New on the External Lookups menu that displays. The External Lookup Wizard appears, displaying the Data source selection screen. This Wizard is similar to the Open Database Wizard we used in Chapter 17.
Figure 18-5. The Data Source Selection screen of the External Lookup Wizard.
The first Wizard screen prompts you for the database to join to. 8. 9. In the name field, enter External Lookup 1. Click the Data Source Browse button and then select Local or network file to indicate the data source type. The Open Database File dialog appears. 10. Select the Customer.mdb file. (This will typically be in C:\Documents and Settings\All Users\Documents\Monarch\Data). 11. Click the Open button to select the data source. Monarch displays the path to the Customer.mdb file in the External Lookup Wizards Data Source box. 12. Click the Next button to advance to the next Wizard screen. The Wizards Dataset selection screen appears. This screen displays all of the database tables and queries that are available within the selected database. The list does not include system tables or queries which hold information about the database structure.
open
Figure 18-6. The Dataset Selection screen. Use this screen to select the database table or query from which the data will be imported.
Our sample Customer.mdb database file holds only a single database table, called Customer, which is already selected. 13. Click the Next button to advance to the next Wizard screen. The Wizards Row definition screen appears. This screen displays a sample of the table data.
Note
When importing from a delimited text file, the Row definition screen allows you to specify parameters for it, such as a delimiter character.
Figure 18-7. The Row Definition screen.
Note
If we wanted to apply an import filter to the data, we could do so by selecting the Apply Filter check box, clicking the Change button, and then specifying a filter via the Source Filter dialog.
14. Click the Next button to advance to the next Wizard screen. The Wizards Linking columns assignment screen appears. Well use this screen to establish an external lookup between the external database and Monarch.
Figure 18-8. The Linking Columns Assignment screen. Use this screen to link the Monarch table and the external database.
15. Click the Add button to specify the columns to use in the external lookup. The Select Columns to Link dialog displays (see Figure 18-9).
Figure 18-9. The Select Columns to Link dialog.
16. Select Acct_Num from the Source Column drop-down list. 17. Select Account Number from the Data View window Column drop-down list. The Acct_Num and Account Number columns are actually the same column. Each column holds the unique account number assigned to each customer. The column names are not required to be the same, but they must be of the same type (character, numeric, or date), and they must contain matching values that can be used to create a relationship between the two tables. 18. Click the OK button to close the dialog. The selected columns are displayed in the Linking columns assignment list.
Figure 18-10. Columns to be linked together display on the Linking Columns Assignment screen.
19. Click the Next button to advance to the Wizards Column selection screen.
Figure 18-11. The Import Columns screen includes all available database columns that can be imported into Monarch.
Use this screen to select the columns to import into Monarch (i.e., the linked columns). This screen lists the names of all columns that are available in the selected database table or view, except for those columns you used to define the lookup. Since we want to import the entire address for each customer, well use the Select All button to select all of the available columns. 20. Click the Select All button to import all columns. Default names, taken from the source columns, are assigned to the import columns and are displayed under the Data View window Name heading. These are the names that will be assigned to the columns when you import them into Monarch.
Note
You can manually edit the import column names by clicking on them. You can also add a name prefix to one or more of the import column names. To do so, however, you must enter the prefix in the Name Prefix box before selecting any of the columns to import. After specifying the prefix, you can then either click the Select All button or select individual columns via their check boxes. The names for the columns you chose to import will then be displayed, preceded by the name prefix (e.g., if you specified a Name Prefix of Test, for example, the import column name for the Address 1 source column would be TestAddress 1.
21. Click the Finish button to apply the Wizard settings and close the Wizard, then click the OK button on the External Lookups dialog. Monarch links the external database table to the Monarch table, adding the selected columns to the Data View window as linked columns. 22. Scroll right to view the linked columns.
Figure 18-12. The customer address columns are added to each record in the Monarch table (columns widths have been autosized).
You can use linked fields just like any other fields in Monarch. Linked fields can be used in filter, sort, calculated field, and summary definitions and can be printed or exported along with fields that you extract from a report.
Storing Lookup Parameters in a Model File

Lookup parameters can be saved in a Monarch model file along with other information about your Monarch session. When you load the model file, the lookup parameters are used to re-establish the lookup with the external database. Storing lookup parameters in a model file is easy. You simply save the model file. Thats all there is to it. The model file records the name of the lookup database along with the fields used to establish the lookup and the fields you linked to the Monarch table. By applying the model in a future Monarch session, Monarch preserves the work you did in this session. Lets save a model file and then reload the session using the model.
Steps: 1. Select File, click on the arrow of the Save As Model. The Save Model As dialog appears. 2. 3. Type Lookup in the File name box of the dialog, and then click the Save button to save the model file. Select File, and then click Close All to end the current Monarch session. menu, and then select
Opening the Model File

Now lets load the report and model file. Steps: 1. 2. 3. 4. 5. Select File, click on the arrow of the Open menu, and then select Report. Open Orders.prn from the Open Report dialog box that displays. Select File, click on the arrow of the Open menu, and then select Model. Open Lookup.xmod from the Open Model dialog box that displays. Expand the Table node in the Document Explorer, and then select Data View. Monarch uses the model to extract fields from the report file, then opens the Customer.mdb file and establishes the lookup to the Customer Addresses table, importing the address fields into the table (see Figure 1612). 6. Select File and then choose Close All to close the report and model.
Creating a Lookup from Two Different Reports

While Monarchs linking capability is very useful if you happen to have access to a database that includes the information you need, what if your data resides in two different reports? You may have already guessed the answer: Using Monarchs export capability, you can create a database file from one report, start a new Monarch session to extract data from the second report, and then create a lookup to the exported database file. While this involves a two-pass approach, its relatively easy to do. Lets imagine that we dont have access to the database of customer address information that we used in the previous example. Instead, we have the Orders.prn report and another report, called Customer.prn that lists all of our customers. Lets use Monarch to link information from these two reports.
Using a Report to Create a Lookup File

Well start by opening the Customer report, applying a model to extract the customer address and account number fields, and then exporting these fields to a database file. Well use this file as a lookup file which well link to the Orders.prn report.
Steps: 1. 2. 3. 4. Select File, click on the arrow of the Open menu, and then select Report. Select the Customer.prn file from the Open Report dialog box that displays and open it. Select File, click on the arrow of the Open menu, and then select Model. Select Customer.xmod from the Open Model dialog box that displays and open it.
5.
Expand the Table node in the Document Explorer and then click Data View. Select Autosize Columns to view all the columns properly.
Figure 18-13. Customer address fields extracted from report.
The Data View window includes all of the columns that weve extracted from the report, including each customers name, account number, and address. Now well export the table to a database file. 6. On the Home tab, select the Export tool.
The Export Wizards General screen displays, and the Table radio button is selected.
7.
Click the Next button. The Export Wizards Table View screen displays.
8.
Click the Next button to display the Output File screen. Type Lesson16.mdb in the Save as box and then click the Next button The Table Info screen displays. Use this screen to name the database table where the customers addresses will be stored.
9.
Type Customer Addresses in the Table Name field, and then click the Run button. A progress dialog displays. Monarch displays the following message box when the export is finished.
Figure 18-15. The message box that displays after successfully completing the export.
10. Click Close to exit the progress dialog. 11. Click Close once more to exit the Export Wizard.
Linking to the Lookup File

Now that the first report has been exported to a database file, its time to load the second report and link to it.
Steps: 1. 2. 3. 4. 5. Select File, and then click Close All to reset the Monarch session. If prompted to save the model, select No. Select File, and click on the arrow of the Open menu, and then select Report. Select Orders.prn report from the Open Report dialog box that displays. Select File, click on the arrow of the Open menu, and then choose Model from the options that display. Open Lesson16.xmod.
6.
Expand the Table node in the Document Explorer, and then click Data View. Select Autosize Columns all the data properly. in the Table tab, View group to view
Figure 18-16. Orders.prn report data to be linked with data exported from Customer.prn.
Now were ready to link to the database file we created and pull in the address information for each customer. 7. On the Home tab, Data Definitions group, click on the drop-down button of the External Lookups tool, and then select New.
The External Lookup Wizard displays. 8. In the Name box, type in External Lookup 2. Click the Browse button beside the Data Source field and select the Local or network file option to display the Open Database File dialog. Select the Lesson16.mdb file from the Monarch\Exports folder then click the Open button. Monarch displays the path to the Lesson16.mdb file in the Wizards Data Source box. 10. Click the Next button to advance to the Wizards Dataset selection screen. The Customer Addresses table is already selected. 11. Click the Next button to advance to the Wizards Row definition screen. 12. Click the Next button to advance to the Wizards Linking columns assignment screen. 13. Click the Add button to display the Select Columns to Link dialog. 14. Select Acct_Num from the Source Column drop-down list. 15. Select Account Number from the Data View window Column drop-down list.
9.
16. Click the OK button to close the dialog. The selected columns are displayed in the Linking columns assignment list. 17. Click the Next button to advance to the Column selection screen. 18. Click the Select All button to import all columns, and then click the Finish button to apply the Wizard settings. Monarch links the Customer Addresses table to the Monarch table, adding the address columns to the Data View window as linked columns. You may need to scroll to the right to view the address columns.
Figure 18-17. Customer addresses linked to Orders.prn report data (column widths autosized).
We started with two disparate reports and ended with data from both reports linked together in the Data View window. 19. Select File, and then click Close All to reset the Monarch session. Select No when prompted to save changes to the model.
Creating a Lookup from Two External Databases

In the previous sections, we demonstrated how you can link data from an external database to data extracted from a report. This is useful when a single report doesnt include all of the data you need. But you can also use Monarch to combine data from two databases, without ever loading a report file. To see how this process works, lets return to the example from the beginning of this lesson. In that example, we showed how you could link fields from an employee compensation table to an employee roster table. The employee roster table is called Marketing since it stores information only about employees in the Marketing department. The employee compensation table is called Compensation. This table stores salary and bonus information for all employees for the entire company.
Importing Database Data

Well start by importing data from the Marketing table. This table resides in the Beantown.mdb file (the name of our fictional company is Bean Town Investments). The compensation table resides in a file called Payroll.mdb. Well create a lookup to this table in order to add the Salary and Bonus field for each employee.
Steps: 1. Select File, click on the arrow of the Open menu, and then select Database. The Database Wizard appears. 2. Click the Data Source Browse button and select the Local or network file option. The Open Database File dialog appears. 3. 4. Select the Beantown.mdb file from the Monarch\Data folder (typically C:\Documents and Settings\All Users\Documents\Monarch\Data). Click the Open button to open the data source. Monarch displays the path to the Beantown.mdb file in the Data Source box. 5. Click the Next button to advance to the Wizards Dataset selection screen.
This screen displays all of the database tables and queries that are available within the selected database.
Figure 18-18. Beantown.mdb database tables.
6. 7. 8.
Select the Marketing table from the list of available tables. Click the Next button to advance to the Wizards Row definition screen. Click the Next button to advance to the Wizards Column selection screen. This screen lists the names of the database columns that are available in the selected Marketing table.
9.
Click the Select All button to select all of the available columns.
10. Click the Finish button to apply the Wizard settings. Monarch opens the Data View window and populates it with data imported from the Beantown.mdb files Marketing table. 11. Select Autosize Columns to display all fields properly.
Figure 18-19. Monarchs Data View window is populated with the data imported from Beantown.mdb, Marketing.
Linking Database Data

Now well link to the Compensation table to add the salary and bonus information for each employee.
Steps: 1. From the Home tab, Data Definitions group, select the External Lookups drop-down button and then select New. The External Lookup Wizard appears (see Figure 18-5). 2. 3. In the Name box, enter External Lookup 3. Click the Browse button next to the Data Source field, and select the Local or Network File option. The Open Database File dialog appears. 4. Select the Payroll.mdb file from the Monarch\Data folder, and then click the Open button. Monarch displays the path to the Payroll.mdb file in the Wizards Data Source box. 5. Click the Next button to advance to the Wizards Dataset selection screen. This screen displays all of the database tables and queries that are available within the selected database. The Compensation table is the only table available, so it is already selected. 6. 7. Click the Next button to advance to the Wizards Row definition screen. Click the Next button to advance to the Wizards Linking columns assignment screen (see Figure 18-18). Monarch automatically links the two Emp_ID columns and adds them to the Linking Columns list.
Figure 18-20. Emp_ID fields are used to form the join relationship.
8.
Click the Next button to advance to the Wizards Column selection screen.
9.
Check the boxes to import both the Salary and Bonus fields.
10. Click the Finish button to apply the Wizard settings and close the External Lookups dialog. Monarch links the external database table to the Monarch table, adding the selected columns to the Data View window as linked columns.
Figure 18-21. Salary and Bonus information are added to each record in the Monarch table.
11. Select File, click on the arrow of the Save As Model.
12. Type Compensation in the File name box of the Save Model As dialog box that displays, and then click the Save button to save the model file. 13. Select File, and then click Exit Monarch.
Note
If prompted to save changes to the project file, select No.
The model file stores the lookup parameters along with the import parameters. However, the model file does not store the name of the import database. To restore the session, use the Open Database Wizard to select the Beantown.mdb file and the Compensation model on the Data Source screen, click the Next button three times, then click the Wizards Finish button to display the Data View window. Note that on the Dataset selection screen, you may select any of the three employee roster tables (Accounting, Data Processing, or Marketing). The model will work equally well regardless of which table you select. Thats why the data source is not stored in the model file -- the model can be used with any compatible data source. This is similar to how models
store information about a report. The report name is not stored in the model so that the model can be used with multiple reports as long as the reports all have a compatible format.
Summary
In this chapter, you learned how Monarch links data from an external database to add columns to the Monarch Data View window. You also learned how lookup parameters are stored in a model file and how to use a lookup to link data from two different reports. For further reading, see the following sections of the Monarch Help file: Chapter 5 - Accessing Database Data with Monarch Performing Lookups
Chapter 9 - Project Files
[19] Working with PDF and XPS Files

In this chapter, well explore Monarchs ability to utilize PDF and XPS files. The lesson topics include: Importing a PDF or XPS file into Monarch Customizing the PDF or XPS import options Exporting to a PDF or XPS file
Importing a PDF or XPS File into Monarch

Monarch has the ability to convert PDF/XPS files into ANSI text so that they can be utilized like any normal text report in Monarch. You can then create data extraction templates to extract information in exactly the same way as you would approach a text report file. PDF/XPS is an open standard, so there are thousands of different applications that create PDF/XPS files using many different techniques. This has the side effect of having PDF/XPS files that may look identical on screen, but are structurally very different internally. Monarch uses very sophisticated techniques to analyze and reformat the data, in order to produce the best possible "Monarch-friendly" results to enable accurate trapping after importing the data. However, there may be some badly formatted and unusual PDF/XPS files that do not convert accurately enough to trap easily in Monarch. Files that do not convert perfectly may still be processed very effectively by Monarch via features such as the floating trap and calculated fields. Lets begin this lesson by importing a PDF file into Monarch.
Steps: 1. 2. 3. 4. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select the Report option to display the Open Report dialog. Select the PDF files (*.pdf) option from the Files of Type drop-down list, select the Classic.pdf file, and then click the Open button. Monarch opens the PDF in the PDF Import Options window. Click OK.
Figure 19-1. The opened PDF file.
5.
Select the File tab, and then click Close All to close the Classic PDF report.
As we can see, Classic.pdf is a PDF version of the Classic.prn report, which we are already familiar with. To import an XPS file into Monarch, follow the same procedures outlined above, but choose the XPS files (*.xps) option from the Files of Type dropdown list instead. When opening a PDF/XPS file, Monarch performs an analysis of the file to try and determine the optimum method of transforming the data accurately. In most cases, Monarchs auto-detection routines will produce the best results, but under certain conditions, adjustments to the PDF/XPS import options may be necessary. For example, the following conditions may require the PDF/XPS import options to be adjusted: The PDF/XPS contains tables with tightly compacted columns. The PDF/XPS contains multiple font sizes and the data of interest is in a smaller font than most of the other text in the PDF, thereby causing the auto-calculated font size to be too large. Mixed mono- and variable-spaced fonts exist in the PDF/XPS where the data of interest uses mono-spaced fonts. Mixed freeform and tabular data exist in the PDF/XPS.
Lets examine the PDF/XPS import options more closely, and see how adjusting them will affect the data transformation.
Stretch Option
The stretch option governs how much spacing is used during the conversion process. When Monarch analyzes the PDF/XPS file, it tries to match the spacing as far as possible to the original document, but there are many factors that can make it necessary to introduce more spacing into the conversion than appears to exist in the original PDF/XPS file. Such factors can include hidden data in the PDF/XPS, i.e., data which is not visible on screen but still exists within the PDF/XPS file itself. This can be the result of columns that truncate the data, for example. At first glance, it is not apparent that any data is missing, but Monarch will convert all the data in the PDF/XPS file, not just what might be visible in a PDF/XPS viewing application. In this case, in order to try and maintain proper column justification, Monarch will have to recalculate and pad the spacing, as the original column spacing may not be enough to hold the data safely. In general, Monarchs behavior is to use a larger amount of spacing (i.e., a higher stretch value) than in the original document. This can make the document look like it is stretched wider than the original PDF/XPS, but Monarch errs on the side of caution, so that columns do not run into each other in the current document. This is also done so that if a later iteration of the same report (or a similar one) contains wider data values, the model will still work with it.
If you know your reports well, then you can decrease the stretch value to make the reports look more presentable by avoiding horizontal scrolling or very small font sizes in the Report window. Stretch values may be increased or decreased via the PDF/XPS Import Options dialog, which is launched when you select the PDF/XPS Options the Report tab. tool in
Mono-spaced Option
Mono-spaced refers to the fact that a mono-spaced font was used in the PDF/XPS. The Monarch Report window uses mono-spaced fonts, which are fonts in which each character has the same width. For example, in a monospaced font, the o and i characters would have the same width (i.e., they would take up the same amount of horizontal space on a line). Other terms for mono-spaced are fixed-width and non-proportional.
Note
The opposite of mono-spacing is proportional spacing, in which different characters have different widths, e.g., in a proportionally spaced font, the letter o would be wider than the letter i.
Note that the Mono-spaced option is already selected on the dialog. When you import a PDF/XPS file into Monarch, Monarch tries to detect when monospaced fonts are used and optimizes the conversion accordingly. In some cases, Monarch may not detect that mono-spaced fonts were used for the PDF/XPS file. When this failure occurs, it is usually due to a mix of monospaced and proportional fonts existing in the same PDF/XPS file. If you know that the PDF/XPS file uses mono-spaced fonts, and the Mono-spaced option is not selected during the import analysis, you can select this setting to force Monarch to optimize for mono-spaced fonts. While proportionally spaced fonts look more appealing, mono-spaced fonts are superior for tabular data because the uniform width of each character makes alignment of columns easier.
Note
In general, PDF/XPS files generated using mono-spaced fonts will convert more successfully, so if you are trying to optimize your PDF/XPS producing application for Monarch, use mono-spaced fonts. Some of the more common ones are: Andale Mono, Anonymous, Crystal, Bitstream Vera Sans Mono, Courier, Courier New, Elronet Mono-space, Everson Mono Latin 6, Fixedsys, Lucida Sans Typewriter, Lucida Console, and PrestigeFixed.
Freeform Option
The Freeform option tries to optimize text that is more freeform than columnar or grouped columnar. A columnar document is a simple table format, where grouped columnar might be something similar to one of the Monarch sample reports. such as Bettys Music Store (Classic.pdf). A typical document that might benefit from using this setting would be an academic report that is 95% text, but which contains a few tables you want to extract.
Note
This setting will sometimes work effectively on columnar documents when the default settings do not produce good results.
When you select a PDF/XPS file from the Open Report dialog and then click Open, the PDF/XPS is automatically imported into Monarch as a report file. If we wanted to, we could now select a template sample and begin creating templates to extract the PDF/XPS files data. Since weve done plenty of this already, lets try importing a PDF/XPS file into Monarch that will require us to modify the PDF/XPS import options. For now, close the PDF report by selecting File, clicking on the arrow of the Close menu, and then selecting Report.
Customizing the PDF/XPS Import Options

Lets begin by selecting another PDF file to import into Monarch.
Steps: 1. 2. 3. Select File, click on the arrow of the Open menu, and then select Report from the options that display. In the Files of type drop-down box, select PDF files (*.pdf). Select the CustomerSummary.pdf file, and then click Open. You can also perform the next steps with an XPS file. In this case, open CustomerSummary.xps instead. Monarch opens the PDF Import Options dialog and displays a sample of the PDF files data. Lets enlarge the dialog so that we can view more of the sample data. 4. Click and drag the lower right corner of the dialog down and to the right until the dialog is the desired size.
Figure 19-2. Viewing the resized dialog.
Notice that for Bluegrass Records, the value in the Amount 1 column begins one space too far to the right, so that it doesnt line up properly with the Amount 1 values for the other three customers. Lets assume that we know the font used to generate this PDF was Tahoma, which is a proportional font. A quick visual investigation tells us this is not a freeform document, but is more of a columnar report. Lets try adjusting the Stretch setting to see if this improves the conversion. In the Stretch size indicator, click Increase until the size reads 7.0. Monarch applies the stretch setting and shows the results on the sample page. Note that the Amount 1 value for Bluegrass Records now lines up correctly with the Amount 1 values of the other customers.
Figure 19-3. Viewing the refreshed sample page.
5.
Click the OK button to close the PDF/XPS Import Options dialog and open the PDF in the Report Window. If we wanted to, we could now define templates to extract the data from the report. Since weve done plenty of this already, lets attempt something we havent done yet instead. Another of Monarchs PDF capabilities is its ability to export extracted report data to PDF files. Lets see how easy this is to do.
Exporting to a PDF File

Lets begin by opening a report were familiar with, Classic.prn.
Steps: 1. 2. Select File, click on the arrow of the Open menu, and then select Report from the options that display. Open Classic.prn. Monarch displays the Classic.prn report in the Report window. Now lets apply a model with which to extract the reports data. 3. 4. Select File, click on the arrow of the Open menu, and then select Model from the options that display. From the Open Model dialog that displays, select Lesson8.xmod and click Open. Monarch applies the model to the report in the Report window. 5. Expand the Table node on the Document Explorer and then select Data View to view the extracted data in the Data View window. Select Autosize Columns to expand the columns if necessary. Now were ready to export the data as a PDF file. 6. 7. 8. 9. In the Home tab, select Export Export Wizard. to display the General screen of the
Click the Next button to display the Table View screen. Click the Next button to display the Output File screen. In Save as field, enter the name Classic1.pdf.
Figure 19-4. Naming the PDF export.
10. Click the Next button, leave the PDF Security settings empty, and click the Run button to execute the export operation. When the export operation has completed, Monarch displays Export(s) completed in a progress dialog. 11. Click the Close button to close the dialog. 12. Select Close once more to exit the Export Wizard. 13. Exit Monarch by selecting the File tab, and then clicking Exit Monarch. Select No when prompted to save the report. 14. Navigate to the Monarch Exports folder (C:\Documents and Settings\All Users\Documents\Monarch\Exports) and open the Classic1.pdf we just generated.
Figure 19-5. Viewing Classic.pdf.
There may be instances when not all the columns of the table are exported to the PDF file. Because PDF is a page-oriented format, the width of the PDF file is dictated by Monarchs Page Setup settings. If this happens, select File, click on the Page Setup option, and then adjust the necessary setting in the Page Setup dialog box that displays. For example, you could modify the Pagination for Excel and PDF or Orientation settings of the file. After modifying page setup settings, you may reexport the table to a PDF file.
Note
Make sure you close the original Classic.pdf export file before doing so. Also, when repeating Step 9, select Overwrite File when prompted to specify a When output files exist option.
There are other ways we could modify the PDF export. For example, we could use the Field List dialog to hide any columns we dont need to export in the table. That way, only the essential columns would appear in the PDF file.
Summary
In this chapter, you were introduced to Monarchs PDF and XPS handling capabilities. You learned how to import a PDF file into Monarch, how the PDF import options can affect the PDF conversion process, and how to export extracted data to a PDF file. Furthermore, you learned how to import XPS files into Monarch. For further reading, see the following section of the Monarch Help file: Chapter 6 Working with PDF and XPS Files
[20] Digital Signatures

In the previous chapter, you learned how to use Monarchs PDF and XPS handling capabilities. This chapter introduces a Monarch feature designed to authenticate files, thereby enhancing security. Here, you will learn about digital signatures. The chapter assumes you are familiar with exporting Table or Summary data into XLSX or XLSM formats.
What are Digital Signatures?

Digital signatures are used to authenticate files (i.e., the Excel files that are produced via Monarch Export) by using computer cryptography. Digital signatures help to establish the following assurances: Authenticity The digital signature helps to assure that the signer is who he or she claims to be. Integrity The digital signature helps to assure that the content has not been changed or tampered with since it was digitally signed. Non-repudiation The digital signature helps to prove to all parties the origin of the signed content. "Repudiation" refers to the act of a signer's denying any association with the signed content.
To make these assurances, the content creator must digitally sign the content by using a signature that satisfies the following criteria: The digital signature is valid. The certificate associated with the digital signature is current (not expired). The signing person or organization, known as the publisher, is trusted. The certificate associated with the digital signature is issued to the signing publisher by a reputable certificate authority (CA).
Starting the Lesson

To start, lets open the Classic.prn report and Lesson11.xmod model.
Steps: 1. 2. 3. 4. 5. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report. Open the Classic.prn report. Select File, click on the arrow of the Open menu, and then select Model. Open Lesson11.xmod. The report is opened and the model you selected is applied to it.
Figure 20-1. Loading the Classic.prn report and Lesson11.xmod model.
Adding Digital Signatures to an Excel File

Steps: 1. Expand the Table node on the Document Explorer, and then select Data View. The Data View window for the report displays. Select Autosize Columns from the Home tab to view all the data properly.
Figure 20-2. The Data View window for Classic.prn report and Lesson11.xmod model.
2.
On the Home tab, click on Export
to launch the Export Wizard.
The Export Wizard displays, showing the General Screen. 3. 4. 5. 6. Click the Next button twice to display the Output File screen. In the Save as field, click on the folder Table dialog. icon to display the Export
From the Files of Type drop-down list, select the file type Excel 20072010 (*.xlsx; *.xlsm). In the File name box, enter Digital1, and then click Save. The file name you specified, as well as the file type extension, display on the Save as: field of the Export Wizard Output File screen.
7. 8.
Click Next to move to the Table Info screen of the Wizard. You may specify a name for the table you are exporting. Lets do this now by entering DigiTable into the Table name field.
9.
Click Next to advance to the next screen. The Excel Advanced Features screen of the Export Wizard displays.
Figure 20-3. The Excel Advanced features screen of the Export Wizard.
10. Check the Digital Signature box. When you do this, the Certificate drop-down list is activated. This list contains options that allow you to select where the digital certificate you are applying is located. Lets say that your digital certificate is stored in the same machine in which Monarch is installed. 11. Select Change to Certificate from File from the Certificate drop-down list. 12. Select the digital certificate you wish to apply to the export, and then click OK. 13. Depending on the settings of your digital signature, you may or may not be asked to set a password for the certificate. If prompted to do so, simply provide one. 14. Click Run. A progress dialog displays. When the export is completed, the message Export(s) completed displays on the dialog. 15. Click Close twice to close the progress dialog and the Export Wizard. 16. Select File, and then click Exit Monarch. Select Yes when prompted to save the changes in the model.
Viewing Digital Signatures

Digital signatures display when you open the spreadsheet containing the certificate. Lets do this.
Steps: 1. 2. 3. Launch your spreadsheet. If you dont have a spreadsheet, just follow along. Select File, and then click Open. Navigate to your Monarch Exports folder (typically in C:\Users\Public\Documents\Monarch\Exports), and then Open the Pivot1 worksheet. Open the Digital1.xlsx worksheet. The worksheet displays in your screen.
4.
Figure 20-4. The exported table with a digital signature.
Note the following features of the file that are not present in ordinary worksheets or workbooks: An alert with an Edit Anyway button displays on the upper portion of the table to warn you that the workbook has been marked final to discourage editing. Should you choose to click Edit Anyway to continue editing the workbook, the following dialog displays:
Figure 20-5. Selecting Yes removes any digital signatures applied to the workbook.
Selecting Yes on the dialog removes any digital signature applied to the workbook. As well, on the lower left-hand corner of the workbook, you will find a certificate icon. Hovering on this icon with your mouse yields a callout that informs you that a digital signature has been applied to the workbook.
Figure 20-6. The digital signature callout.
If you click on this icon, a list of all digital signatures applied to the workbook display on the right-hand side of the workbook. You can hover on any of these signatures to display a drop-down button that, upon clicking, will allow you to view the properties of that signature.
Figure 20-7. A list of digital signatures displayed on the right-hand side of the workbook.
5.
Close the spreadsheet.
[21] Pivot Tables

In the previous chapter, you learned about a special Monarch feature you can use to increase the security and authenticity of your report: digital signatures. In this chapter, you will learn about another special Monarch feature: pivot tables. Pivot tables are interactive tables within Monarch that allow you to further manipulate and view data. They can only be created for XLSX files; they cannot be created for XLS files. If you are using a version of Excel earlier than 2007, you can download the Microsoft Office Compatibility Pack from Microsoft, which will allow Excel 2000 and higher to open XLSX files. The option to create a Pivot Table is not available for a Summary export, since pivot Tables use tabular raw data as a source, not summarized data. Monarch will export the table data as a worksheet and the pivot table as a separate worksheet, which refers to the exported data.
Starting the Lesson

To prepare Monarch to export to pivot tables, the following steps are taken.
Steps: 1. 2. Select the Monarch item from the Windows Start menu. Select the File tab, and then click Options. The Options dialog displays. This dialog contains general options for you Monarch files. 3. 4. Select the Export tab on the left-hand side of the dialog. Navigate to the Excel files subtab, and ensure that the boxes Apply formatting to XLS and XLSX files and Apply the Export Wizards Advanced Excel Features are checked.
Figure 21-1. The Excel Files subtab of the Export tab of the Options dialog.
5.
Close the dialog box by selecting OK.
Exporting to a Pivot Table

To start, lets open the Classic.prn report and the Lesson11.xmod model.
Steps: 1. 2. 3. 4. Select File, click on the arrow of the Open menu, and then select Report. Open the Classic.prn report. Select File, click on the arrow of the Open menu, and then select Model. Open Lesson11.xmod. The report is opened and the model you selected is applied to it. 5. Display the Data View window of the open report.
Figure 21-2. The Data View window of Classic.prn.
6.
Select Export a pivot table.
from the Home tab to begin exporting the table data to
The Export Wizard displays, showing the General screen. 7. 8. 9. Click Next button twice to display the Output File screen. In the Save as field, click on the folder Table dialog. icon to display the Export
From the Save as type drop-down list, select the file type Excel 20072010 (*.xlsx; *.xlsm).
10. In the File name box, enter Pivot1, and then click Save.
The file name you specified, as well as the file type extension, display on the Save as: field of the Export Wizard Output File screen. 11. Click Next to move to the Table Info screen of the Wizard. 12. You may specify a name for the table you are exporting. Lets do this now by entering PivoTable into the Table name field. 13. Click Next to advance to the next screen. The Excel Advanced Features screen of the Export Wizard displays. 14. Check the box Include Pivot Table sheet. A series of fields displays below the Pivot label.
15. Click on the Options tab that displays and then enter Pivot Table 1 into the Pivot Table name field. You may leave the other settings in this tab as they are for now. 16. In the Fields tab, click on the New drop-down list of the Row Labels field and select Customer. 17. In the Column Labels field, select the New drop-down button, and then select Media from the list that displays. 18. In the Values field, select the New drop-down button, and then select the values Amount and Unit Price from the list that displays. 19. Click Run. A progress dialog displays. When the export is completed, the message Export(s) completed displays on the dialog. 20. Click Close twice to close the progress dialog and the Export Wizard. 21. Select File, and then click Exit Monarch.
Viewing a Pivot Table

Steps: 1. 2. 3. Launch your spreadsheet. (If you dont have a spreadsheet, simply follow along.) Select File, and then click Open. Navigate to your Monarch Exports folder (typically in C:\Users\Public\Documents\Monarch\Exports), and then Open the Pivot1.xlsx worksheet. The worksheet displays in your screen.
Figure 21-4. The exported Pivot1 worksheet.
The PivoTable tab shows an Excel file version of the Classic.prn table. 4. Click on the PivotTable1 tab.
The pivot table displays on your spreadsheet.
For more information regarding pivot tables, you may access the Monarch Help file by double-clicking on the MAIN.chm file in the Monarch Program folder (C:\Program Files\Monarch\Program). To access the file while within Monarch, select File, click on the drop-down button of the Help and then select Help Topics from the options that display. menu,
Working with Pivot Tables

Lets discuss the features of the pivot table worksheet. When we selected pivot table fields in the earlier part of this chapter, we were specifying settings similar to those used when creating a summary. For example, the Row Labels specify down keys, the Column Labels specify across keys, and the Values specify measures. Clicking on the drop-down buttons on either the Row Labels or Column Labels fields displays a set of options that allow you to sort and filter your data.
Figure 21-5. Options for sorting and filtering pivot table data.
To display the unit prices and amounts for only the media types DVD and BLU, for example, simply click on the drop-down button of Column Labels, deselect the boxes for CD, LP, and SACD, and then click OK. When you do this, the Select All checkbox is automatically deselected.
A new table is rebuilt and displayed in your spreadsheet.
Figure 21-6. The new pivot table showing only unit prices and amounts for the media types DVD and BLU.
Now that were done discussing pivot tables, close the spreadsheet and return to Monarch.
[22] Monarch Context

The Monarch Context Add-in for Excel 2007 allows you to reference the source data that was used to produce an Excel file (XLSX/XLSM) via an export operation in Monarch. By including the source data, you can ensure absolute integrity for files which only need to be viewed and/or archived. When working with Monarch Context, remember that the default maximum file size of the file or report that you can include as a source is 50MB. This can be increased or decreased, however, by changing your Monarch settings. Consult your system administrator for more information.
Starting the Lesson

To start, lets open the Classic.prn report and the Lesson8.xmod model.
Steps: 1. 2. 3. 4. 5. Select the Monarch item from the Windows Start menu. Select File, click on the arrow of the Open menu, and then select Report. Open the Classic.prn report. Select File, click on the arrow of the Open menu, and then select Model. Open Lesson8.xmod. The report is opened and the model you selected is applied to it. 6. 7. Display the Data View window of the open report. Select Autosize Columns fully view all the fields. from the View group of the Table tab to
Applying Monarch Context

Steps: 1. Select Export a pivot table. from the Home tab to begin exporting the table data to
The Export Wizard displays, showing the General screen. 2. 3. 4. 5. Click Next button twice to display the Output File screen. In the Save as field, click on the folder Table dialog. icon to display the Export
From the Save as type drop-down list, select the file type Excel 20072010 (*.xlsx; *.xlsm). In the File name box, enter Context1, and then click Save. The file name you specified, as well as the file type extension, display on the Save as: field of the Export Wizard Output File screen.
6. 7. 8.
Click Next to move to the Table Info screen of the Wizard. You may specify a name for the table you are exporting. Lets do this now by entering Context into the Table name field. Click Next to advance to the next screen. The Excel Advanced Features screen of the Export Wizard displays.
9.
Check the box Include Report view text in XLSX file.
Figure 22-2. Applying Monarch Context to a table.
10. Click Run. A progress dialog displays. When the export is completed, the message Export(s) completed displays on the dialog. 11. Click Close twice to close the progress dialog and the Export Wizard.
Working with Monarch Context

Monarch Context allows you to work with Excel spreadsheets and refer to the reports from which the details in your table were obtained. This functionality allows you to view report sources up to 50 MB.
Steps: 1. 2. 3. Launch your spreadsheet. (If you dont have a spreadsheet, simply follow along.) Select File, and then click Open. Navigate to your Monarch Exports folder (typically in C:\Users\Public\Documents\Monarch\Exports), and then open the Context1.xlsx worksheet. The worksheet displays in your spreadsheet.
4.
Select the Monarch tab.
Several tools display on the upper right-hand corner of the worksheet.
Figure 21-5. The Monarch Context tools.
5.
Click on any detail in the worksheet (e.g., Cell E11 the cell containing the detail Stravinsky, Dumbarton Oaks Concerto) and then click Display Source .
The detail source is displayed and highlighted in a Monarch Context window.
Figure 21-6. The detail source displays on a new window.
The window has several tools you can work with:

Click this icon To
Save the detail source (report) as a new report file. By default, the new report file will be saved in the Monarch Exports folder and will have a file extension of .prn. Print the report. Copy a selection to the clipboard. Search for a detail item within the report. Search for the next instance of the details item within the report. Go to the previous reports (in case more than one report was used to build the table). Go to the next report (in case more than one report was used to build the table). Go to the previous page of the current report. Go to the next page of the current report. Go to a specific page in the report. Add a bookmark to the report. Delete a bookmark from the report. Go to the previous bookmark. Go to the next bookmark. Use color banding on the report. Select a font for the report. Select a zoom setting for the report.
Clicking on Report List worksheet table.
displays the list of reports used to create the
Clicking on Remove Reports directs Excel to close all detail sources (reports) associated with the worksheet. When you do so, a dialogue box displays, asking you to confirm removal of all reports associated with the worksheet. If you click Yes in this dialogue box, removal of report(s) proceeds and is completed. This removes Monarch Context functionality from the current worksheet. Functionality will be restored when you close and then reopen the worksheet without saving changes. If you want to permanently remove Monarch Context from the worksheet, save your work first before reopening the sheet.
launches a window from which you can learn Clicking on Discover more about Monarch Context.
Figure 21-7. Learn more about Monarch Context by clicking on the Discover button in your worksheet.
Conclusion
Congratulations! You have just completed the lessons in the Monarch Learning Guide. We hope you have found them to be a helpful introduction to Monarchs capabilities. For additional information not covered in the Learning Guide, we suggest you thoroughly explore Monarchs help system. To do so, select File, click on the drop-down button of the Help menu, and then select Help Topics from button on any dialog within the main menu. You may also click the Help Monarch to access context-sensitive help.
We also encourage you to visit the online Monarch discussion forum. To do so go to the Datawatch Monarch Forums home page, www.monarchforums.com. In addition, Datawatchs Technical Support department provides free technical assistance to registered Monarch owners. Refer to the Appendix E - Contacting Technical Support section of the Monarch Help file for more information.

Monarch 11 Learning Guide

Uploaded by

Copyright:

Available Formats

Monarch 11 Learning Guide

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Monarch 11 Learning Guide

Uploaded by

Copyright:

Available Formats

Monarch 11

MONARCH LEARNING GUIDE

MONARCH HELP FILE

Monarch Model Building Service

[1] Introducing Monarch

Monarch Learning Guide 1

Figure 1-1. The Report window.

Figure 1-2. The Data View window.

2 Monarch Learning Guide

Figure 1-3. The Summary window.

Monarch Learning Guide 3

Monarch Data Flow

Figure 1-4. Monarch flow chart.

4 Monarch Learning Guide

Monarch Learning Guide 5

[2] Monarch Lessons

6 Monarch Learning Guide

Monarch Learning Guide 7

Before You Begin

Installing Lesson Files

8 Monarch Learning Guide

The Options Dialog

Figure 2-1. The Options dialog.

SETTING FOLDER DEFAULTS

Monarch Learning Guide 9

SETTING INPUT OPTIONS

Range for 2-digit years

10 Monarch Learning Guide

Text Files Subtab

Specifies the trap characters to use: English Latin Lower ASCII

Traps are case-sensitive

Make these the default settings

Restore from default settings

Monarch Learning Guide 11

SETTING VIEW OPTIONS

Restore from default settings

SETTING EXPORT OPTIONS

Include field names as the first row of output

Get column names for summary exports to database formats from

12 Monarch Learning Guide

Excel Files Subtab

Apply formatting to XLS and XLSX files

Apply the Export Wizards "Advanced Excel Features"

Text Files Subtab

Date format (Delimited Text Files)

Encoding Append DOS end-of-file character (x1A)

Monarch Learning Guide 13

Make these the default settings

Restore from default settings

SETTING CLIPBOARD OPTIONS

Include field names as the first row

Make these the default settings

Restore from default settings

SETTING SECURITY OPTIONS

Save passwords from imported databases in projects and models

Save passwords from PDF files in projects

14 Monarch Learning Guide

Restore from default settings

SETTING TIME INTERVAL OPTIONS

Interval Formatting Mask Sample Value