Real-World Printing Problems & Solutions

By David Adams and Dan Beckett

Adapted from Programming 4th Dimension: The Ultimate Guide.

 You can download a PDF version of this article (2.1 MB) for easier reading and printing.

Introduction

4D includes several built-in reporting tools that make some reports easy to produce while others remain quite difficult. Many difficult reporting problems can be solved by taking a fresh look at reporting as well as the reporting tools available.

The purpose of this session is to explore the various reporting options available to 4D through real-world reporting problems. We do not discuss, for instance, the mechanics of how to print a report using PRINT FORM . Rather, we discuss reporting strategies --when to use standard 4D tools and when to consider alternative approaches. The focus is on the tough reports.

Target Media

Paper is great, but it's not always the best way to present information. A report can be delivered in any of the following forms, or in any combination of these forms:

Determining which tool to use for a given report should start with some consideration of the target media.

Paper

Paper is the traditional target for reporting. The disadvantage of paper is that it limits the output to, well, just paper. If a report needs to end up on paper, keep in mind that the electronic media below are still options.

Electronic

Many reports can reside in disk files rather than on paper. This can make distributing them easier, and they can always be printed and reprinted later. There are many formats that a disk-based report can take. Let's look at a few common formats.

Text

Text reports are simple to create and very flexible. Pure text reports lack any advanced formatting options beyond simple columns. Even so, such documents are useful for e-mail messages or as import-ready files for use in other programs. Using a markup system like HTML or RTF provides far richer formatting options than pure text, without much extra work.

HTML

HTML-based reports are becoming more widely accepted as intranets, extranets, the Internet, and Web browsers become more and more a part of people's work day. Certainly, if an intranet is present, an HTML report is ideal: a history of previous reports can be available at all times, and reports can be viewed and printed as needed. HTML reports are particularly handy for remote users with slow modem connections because Web pages are generally small and therefore quick to download.

RTF

Rich Text Format (RTF) has been around for a long time. It is a markup language used by word processors as a common way to store formatted documents in a common, text-based format. For our purposes here, it is a lot like HTML. In fact, the programming techniques to create a report in RTF are exactly the same as those for HTML. We'll examine those techniques below in Case History #2 - Electronically Distributed Reports.

Page Layout Programs

Page layout programs like FrameMaker, Ventura Publisher, QuarkXPress, and PageMaker include their own custom markup languages. These languages offer formatting and page layout control and sometimes the ability to perform complex formatting options. If you need to publish large reports or catalogs from your database, these markup languages are ideal. Layout programs are designed to format and print documents quickly. You can produce a marked up export file in a few minutes that a page layout program can interpret, format, and print more quickly than 4D. Another advantage of this approach is that if you define your formatting templates in the page layout program, you can often reuse the same database data for several purposes. Not only this, you can employ the skills of a graphic designer or page layout expert, who may know nothing about 4D, to produce great looking documents.

Output Device

Reports can take the form of paper, faxes, disk files, e-mail messages, or records in a table. Different tools can target different output devices. As we consider reporting tools, we'll consider the outputs the tool uses most easily, which will help determine the appropriate tool for a particular task.

Data+Formatting=Output

It is helpful to think of reporting as applying formatting to data in order to create an output. The data can reside in several places, and the formatting can be applied in a variety of ways. We refer to the place the data is gathered for reporting as the reporting data container , or the container , for short.

The following table briefly compares several tools from this point of view, each of which is discussed in more detail below:

Tool
Data Container
Formatting Rules Output Media
PRINT SELECTION Current selection Form definition Paper
PRINT FORM Any tables, variables and/or arrays Custom methods

Form definition

Paper
REPORT Current selection Report definition Paper

Disk file

Array-Based Tools Arrays Custom methods Paper
4D Calc Spreadsheet Spreadsheet format Paper

Disk file

Screen display

4D Write Document Document format Paper

Disk file

Screen display

Template/Tag Tools Text variable Template definition Paper

Disk file

The Tools

We look at each of the tools discussed below from the point of view of what the container is, how the formatting is applied, and which outputs are supported. This helps clarify how much programming effort a tool requires, how much control we have over formatting, and how flexibly we can target the output.

PRINT SELECTION

The data container for a PRINT SELECTION report is the current selection of records. The formatting is stored in the report form, and the two are combined on the way to the output device. On the way out, code can execute that manipulates the data in the selection, or adds data (perhaps from another table) to the output. This is a powerful combination and, as we will see below, when used in conjunction with a reporting table can be used to print just about anything.

PRINT SELECTION is limited in that it is designed for paper output. You can alternatively direct the report to a fax engine, or a PDF writer for an electronic result. These results, however, are not really suited to further manipulation.

REPORT

REPORT (Quick Report in the User environment) is a version of PRINT SELECTION with a different form designer. It has all the same characteristics of PRINT SELECTION but trades less formatting control for user access to the form designer. In other words, users can edit the report definition, but the form editor is more limited in what it allows.

PRINT FORM

PRINT FORM is a simple tool used to move data from a container to an output device. The container can be any 4D data source: records, arrays, variables, whatever. The positive features of PRINT FORM are that it imposes no limitations on formatting and it prints very quickly. You can employ multiple forms, from any number of tables, in any order. You are not limited to a single table or form. The downside is that you must design and maintain the forms by hand, and code all data manipulation yourself. Nothing is automated, so the coding can quickly get complicated.

Array-Based Tools

Third-party array-based printing tools (PrintList Pro and SuperReport) offer functionality not present in native 4D. These tools close the gap between PRINT SELECTION and PRINT FORM in that they offer non-table-based reporting, but offer formatting tools and built-in output controls that make managing the output process much simpler than with PRINT FORM. The data container for the report is a collection of arrays. The formatting is controlled via commands that are applied as the data is sent to the output target. These tools have the same media limitations as the other printing tools discussed so far.

4D Calc

4D Calc is different from the above-mentioned tools in that the data and the formatting live in the same place: the spreadsheet. For our purposes 4D Calc can be viewed as a "grid with formatting". The data container is the spreadsheet, and the formatting is contained within.

4D Calc spreadsheets can be saved to disk in either 4D Calc format or text format, which lends a lot of flexibility to the output options. Disk files can always be printed, but they can also be passed around electronically, posted on file servers, archived on CDs, etc.

Note: 4D Calc has built-in support for cross-tab reports, which are worth investigating if you need a cross-tab engine, though this feature is not the focus of our example usage of 4D Calc.

4D Write

4D Write is similar to 4D Calc in that the data and the formatting live in the same place. The formatting options are different, though, as 4D Write is a word processor and stores data as such. 4D Write-based solutions can be approached in either the same manner as 4D Calc-based solutions, or as a template/tag tool, described below.

Template/Tag Tools

This technique for producing reports combines a template created with a document editing tool and tags embedded in that template. This powerful technique can be used with 4D Write or any text-based markup language like HTML or RTF.

If this concept is a new to you, consider a .pdf document. You have a document, the .pdf file, and a reader application, Acrobat Reader. The document contains the data and the formatting commands, and the reader renders it all into human-readable form. Extending this idea to other available tools, the document can be a 4D Write document and the reader 4D Write. Or an HTML page and any Web browser. Or an RTF document and a word processor.

So, how does this relate to 4D reporting? When used as a 4D reporting tool, the document templates are created using the reader application, special text-based tags are placed within, and those tags are replaced using 4D code when the report is generated. The data container for this type of report is a text variable.

For example, you can create a Web page using any HTML editing tool, and instead of the usual content, you can place tag strings like "^FirstName", "^CurrentDate", etc. When it's time to generate the report, 4D can read the HTML document into a text variable. That variable is then the reporting container. The formatting is already in the container in the form of HTML tags created when you made the template. The report code gathers the data it needs and places it in the HTML text, replacing the tags you put in the template.

Because the technique is so powerful (and possibly confusing if you've never tried it before) we explore it in more detail using RTF in Case History #2 - Electronically Distributed Reports.

Case Histories

Introduction

Each of our case histories is drawn from a real-world printing problem we solved for a customer. We've selected these examples since they solve problems by approaching reporting with sometimes novel, yet efficient, solutions.

Case History #1 - Complicated Sort and Break Criteria.
A reporting table makes reporting easy and efficient.
 
Case History #2 - Electronically Distributed Reports.
Producing custom RTF satisfies the customer.
 
Case History #3 - Gigantic Cross-Tab Report.
4D Calc produces the Godzilla of cross-tab reports.

Case History #1 - Complicated Sort and Break Criteria

The Directive

Create a one-page Job summary report, broken down by Job Type. Each Type (i.e. Detail section) must compare actual figures stored in a related many table and budget figures stored in an unrelated table (Budget) using several statistical formulas.

 

The Problem

There were two main issues that prevented this from being a simple Jobs-table PRINT SELECTION report. First, the jobs were to be grouped by "Program", but that designation was specific to this report. In other words, a job may be considered to belong to one Program group for this report and another Program group for another report. Second, the data for each Program group was drawn from both a related many table to the Jobs table and an unrelated table. Getting all the right data together proved to be difficult.

The Solution

To accomodate breaking the report based on each job's Program group as well as managing the multiple data sources, we decided to implement an intermediate container--a reporting table--and use PRINT SELECTION to print the records in that table. The source data could then be collected and organized in a manner that made coding and debugging the report simple. Each job's Program group could be looked up and stored in its reporting table record making break processing simple. All the data gathered from the various sources could be collected and stored in one location, making debugging both the report and the data much easier.

The reporting table

The Steps

Create the reporting table with all the fields necessary to print the report. Also, create the form required to print the report using PRINT SELECTION , coding objects on the form to calculate statistics when necessary. 

The report form

The code for printing a report is expressed here in pseudo-code:

` Prepare the container

Delete the selection of reporting table records

` Fill the container

Find the Job records for this report
Sort Jobs by Program

For (Each Job record)

 Determine Program group name for this job based on its Program name

 If (Program group has changed since last Job record)

  Save the existing reporting table record
  Create a new reporting table record
  Fill in Program group name and sort order fields

End if 

` Find source data
 Find related many records
 Find Budget record(s) 

` Store related many data
 For (Each related many record)
 Increment reporting table fields

End for 

` Store budget data

For (Each Budget table record)

 Increment reporting table fields

End for

` Print the report

ALL RECORDS ([BudgetComp])
ORDER BY ([BudgetComp];[BudgetComp]Sort_Order)
BREAK LEVEL (1;0)
ACCUMULATE (<many things go here>)
OUTPUT FORM ([BudgetComp];"rBudgetComp")
PRINT SELECTION ([BudgetComp])

The finished report.

Conclusion

Using a specialized table as an intermediate data container allowed us to use a simple, built-in printing tool rather than coding an elaborate solution using some other tool. It also allowed us to easily debug the report by providing quick access to the report's source data, which was itself derived from the multiple sources.

Case History #2 - Electronically Distributed Reports

The Directive

Create a version of an existing 4D PRINT SELECTION report that can be distributed to key management around the client's corporation via a file server. The report should be viewable from PCs and Macs, and should be able to be produced automatically without extra steps on the part of the operators.

The Problem

The people who needed the report were executives uninterested in learning or buying new software just to view a report. The company did not have an intranet in place, which ruled out HTML. Distributing a .pdf was ruled out because it would involve buying Acrobat and teaching the computer operators to use it. (We said this was the real world, right!) The corporation used Microsoft Office as a standard, so everybody had Word available and knew how to use it.

The Solution

We created the report via an RTF template and tags solution. This provided a precisely formatted report, legible across platforms, that could be read using a commonly available tool.

The Steps

First, we mocked up a sample report in Word. This provided a template that stored all the formatting. Then we replaced the sample data with made-up substitution tags (Like ^Title, ^DateRange, ^Body, in this case). 

Word template showing tags

That template was saved as RTF, and the RTF source was copied and pasted into a Mac TEXT resource via ResEdit. (A TEXT resource was used so the report definition would be part of the structure file. The template could also have been stored in a text field in a record or in a disk file.) 

RTF generated by Word with tags highlighted.

When the report is produced, the template is copied into a text variable via a plug in. (V6 contains built-in tools for accomplishing this.) Then the data is gathered and placed in the text variable in place of the tags via Replace string . Next, a disk file is created with a ".RTF" filename extension, and the contents of the variable are moved into it via SEND VARIABLE . This file gets copied onto a file server and can be downloaded and read by anyone with Word or any other word processor that supports RTF. (Most do.) 

Final report

Conclusion

Using RTF allowed us to create a precisely formatted document that could be read by anyone within the company using existing software. This solution also required no extra work on the part of the computer operators.

Case History #3 - Gigantic Cross-Tab Report

The Directive

Create a report that mimics an Excel-based report that the client has been creating by hand.

The Problem

The Excel-based report is a spreadsheet one page tall and seventeen pages wide. It contains around 250 columns which summarize data from the current and the previous fiscal year, drawing data from a table and its related many table.

The Solution

The width and complexity of the report ruled out 4D's standard printing tools. Array-based tools were ruled out by the number of columns and need for multi-page width. Because the original report was a spreadsheet, we thought to look into using 4D Calc. This turned out to be ideal because 4D Calc could support both the data storage and printing in a format the client was familiar with. The formatting controls on 4D Calc are somewhat limited when compared to the other printing tools, but they were sufficient for our needs.

The Steps

Here are the basic steps expressed in pseudo-code:

` Prepare a workspace
Create a 4D Calc offscreen area to work with
Add main column header data
Add column formatting
Find the source records for this fiscal year

` Collect the data
For (Each record in the source selection)

 ` This year
 Find the related many records

 For (Each related many record)

  Calculate the location of the target column
  Add the data to the current column total

 End for

` Previous year
 Find corresponding record from last fiscal year 
 Find the related many records 

 For (Each related many record)

  Calculate the location of the target column
  Add the data to the current column total

 End for

End for 

` Add subtotals, statistics, and totals
Add total fields to bottom of spreadsheet

` Save
Save the spreadsheet to disk in an archive folder

` Print
Print the spreadsheet

Conclusion

Using 4D Calc, we were able to provide a report in the format desired by the client, on both paper and electronically. Additionally, if need be, the data could be exported from 4D Calc in tab delimited form and used as source data for another program without having to rerun the reports.