Turn printed reports into data sources

Have you ever run a report and created a print spool file, only to find out later that you deleted your database and now need to repopulate it from the spool file? Do you routinely find yourself running dozens of reports that contain 95 percent identical information, with minor fields omitted here and there depending on the recipient?

Monarch 3.0 from Datawatch is a data access and analysis tool that lets you collect data from spooled report files. With Monarch, you can take a common report such as a payroll listing and either convert the information into another output report or import the report data into a file format such as a generic DBF (xBase) or CSV (comma-separated) file. Datawatch refers to this process as report mining.

Prospecting
Suppose you have a large healthcare installation and run a nightly patient-billing job to print invoices for a portion of your patients. If the facility has a comprehensive billing system, a typical report might take hours to complete. Running several reports to allow for minor variations in output can be costly because of the time and processor resources it requires. With Monarch, you can instead run one billing report and feed the spooled output file from that report into Monarch to create additional customized reports.

Monarch also lets you convert other databases to SQL Server. For example, converting a UNIX FilePro, AS/400 DB2/400, or VAX/VMS Oracle database to SQL Server is not easy. Monarch lets you write a small application program to print the contents of each record in your database and convert the printed report into a database format you can easily load into a SQL Server database.

Reducing your report distribution costs is also an issue. Rather than distribute hundreds or thousands of pages of output to your employees, why not distribute reports to them electronically? With Monarch, you can convert your printed report into a compact porTable report format (.PRF) file that you can attach to an email message. Monarch registers the .PRF file type in the Windows NT Registry; when you double-click the attachment in your mail reader, it launches the appropriate report viewer so you can review the report.

Of course, these benefits do not come without a price. Although simple in concept, Monarch does have a moderate learning curve, especially if your reports are complex.

Staking a Claim
Installation of Monarch components is standard and uneventful. The base software package includes three components. The first component is the Monarch application program. This program lets you write the templates you will use on various input files to perform redistribution and repackaging functions. This component arrives on three 3.5" floppy disks and consumes less than 10MB of disk space when fully installed.

The other two components are two versions of the Monarch Report Explorer. This module lets users view and print the contents of .PRF files you create using the main Monarch application. The Report Explorer program arrives in a standalone version and as a Netscape Navigator plugin. The standalone program lets users work from any Intel-based Windows machine, and the plugin lets users view .PRFs from the Intel-version of Netscape Navigator and Microsoft Internet Explorer and see .PRF files across the Web.

Panning for Gold
Suppose you want to create a .PRF file, or export the data from a spooled report to another data format. You must use Monarch with a three-step process.

First, after running the main Monarch application, select the input file you want to process. You accomplish this task through a series of file and directory selection dialogs.

In the second step, you create a data extraction template, which is a blueprint for how Monarch will interpret your report's data. At the simplest level, you have a detail template, which extracts data from detail lines on a report.

To create a detail template, select a sample detail line in your report. Screen 1 shows a sample template. To define a detail template, press \[ALT\]-\[T\] to select the template menu-bar, and \[D\] to chose the Detail option on the menu bar. This process initiates a Template Definition Dialog. Here, you create a series of traps, or points where Monarch will collect data. Traps help Monarch differentiate detail data from other data (such as header or footer data) in your output file.

Output files are rarely formatted simply. You're more likely to have reports with multiple section breaks, such as detail reports sorted on various criteria, where reprinting the common data from each detail record is redundant. To properly extract data from these reports, you use Monarch's "1st-append" and "2nd-append" templates. With these template definitions, you tell Monarch which data on the report is acting as a control-break for the printout, and have Monarch automatically recapture that data for you.

Once you've defined your data template, in the final step you can view the Monarch Table Window to examine how Monarch will interpret your report. As Screen 2 shows, from the Table Window, you can examine the data from the report in a tabular format, similar to a data sheet view in a Microsoft Access database. For each column in the Table, Monarch extracts data values from your output report and stores them in the database. You can manipulate the order of the fields in the Table, provide names for the individual columns, and establish a data type (character, date, numeric) for each column.

Once you conFigure your Table, you can print your output or export the data to another application. Monarch can export the data it collects to xBase DBF file format; Excel spreadsheet XLS format; Lotus spreadsheet WKS, WK1, and WK3 formats; and delimited and non-delimited ASCII text files (CSV).

Before exporting your data, you can massage it. For example, you can filter out certain records or key fields, or you can change the output's sort order. To filter certain records, you need to create a filter expression, a Boolean operation that uses the names of each column with different operators and conditional statements. You can create simple filters, such as "order_amt > 0," or complex filters with AND, OR, and NOT conditions.

To sort your Table's data, you create a sort definition with this simple process, you select fields in the order in which you want to sort the Table's data and select each field's sort order, either ascending or descending. For example, if you want to sort a Table of customer orders by customer number and most recent order date, you might select the customer number (in ascending sequence) as the first sort field and the order date (in descending order) as the second sort field. To restore the original order, delete the sort definition.

Monarch includes several advanced features that you can use to manipulate your Table's data and presentation. For example, you can add subtotals and blank lines to a summary printout. For this process, you define the key field and the summary field. When the key value in the key field changes, Monarch prints a subtotal of the summary field for all the records with the same key value. You don't need complex COUNT or SUM functions.

You can also create drill-down reports that let you view a summary record in a Table. When you select an individual record, all the supporting detail associated with that record expands on the screen.

Finally, Monarch lets you create charts and graphs. With your detail or summary data, you can instruct Monarch to build line, bar, and pie charts showing various relationships among components of your data.

For instance, if your company sells different categories of items, you can use Monarch to create a bar chart that details your company's yearly sales of each category. You don't need to export the data to Excel to create the report.

Finding a Nugget
Monarch is an unusual program that lets you realize additional value from the investment you've made in printed reports or legacy databases. In these days of data mining and data warehousing, companies are looking for every possible source of information to analyze business practices. Monarch lets you extend your search for meaningful business information to the printed reports sitting on your desktops.

Monarch 3.0
Contact: Datawatch * 508-988-0697 or 800-445-3311
Web: http://www.datawatch.com
Email: sales@datawatch.com
Price: $399