Working with Lists of Data

Many reports are nothing more than simple lists of data. Examples include such things as task lists, customer lists, overdue invoice reports , student test scores, and phone directories. Besides being the most frequently encountered types of reports, lists of data are also the easiest types to create. As such, they provide us with a good starting place to begin delving into report creation.

List view layouts can be created with the Layout Wizard or by hand. Figure 10.1 shows an example of a basic list view layout that displays student names and quiz scores. Depending on your needs and aesthetics, this alone might serve as a report.

Figure 10.1. Basic list view layouts are the simplest types of reports you can create.

For more on creating layouts and working with layout tools, see "Working with Objects on a Layout," p. 103 .

Beyond being simple to create, list views make nice reports for several other reasons. The first is that they're very flexible. You can allow users to perform ad hoc finds, or you can program scripts with "canned" searches, and then simply display the results using your list view.

Users can also view list reports while in Browse mode. This isn't the case with subsummary reports, which can be viewed only in Preview mode. The key benefit of being able to work with a report in Browse mode is that you can place buttons on your report that give the user additional functionality, such as drilling down to additional levels of detail, or re-sorting the data without having to regenerate the report. There may be buttons (or other objects) on your layout that you wouldn't want to appear when the report is printed (such as navigation buttons ). While building the report in Layout mode, select those objects and then choose F ormat, Sl i ding/Printing to open the Sliding/Printing dialog, then select Do Not Print the Selected Objects.

NOTE

If your users are likely to print from a list view, be sure that you constrain your report to the width of the printed page rather than the monitor screen width. You'll also find that although 10 “12 point fonts generally work well for reports that will be viewed onscreen, 8 “10 point fonts are more appropriate for printed reports. Be sure to actually print out your reports to proof them rather than simply rely on what you see on screen.

If you have problems printing your reports, see "Printed Reports Show Only a Single Record" in the Troubleshooting section at the end of this chapter.

Of course, you can make your list view layout as crafted and fancy as you desire . You might consider employing some common techniques, however, for enhancing list view reports.

Trailing Summaries

A list report in and of itself does little synthesizing of data; it just organizes data for easy review. The main tools at your disposal for synthesizing a set of data are summary fields. Summary fields enable you to perform aggregations across a set of records, including counting, totaling , and averaging.

For more information about creating summary fields, see "Working with Field Types," p. 69 .

Adding a trailing grand summary part to a basic list report gives you a place to put summary information about the set of records in your report. For example, in a list report that displays invoice data, you might choose to put the total amount invoiced in the trailing grand summary part.

For more information about working with layout parts , see "Working with Parts," p. 99 .

Summary fields placed in a leading or trailing grand summary part summarize the entire found set of data, so as you feed different sets of records into a report, your totals change accordingly . Figure 10.2 shows the same report as Figure 10.1, except that here four summary fields ( Average_Quiz1 , Average_Quiz2 , Average_Quiz3 , Average_Overall ) have been added to the database and placed in a trailing grand summary.

Figure 10.2. Summary fields placed in a trailing grand summary part act upon the entire current found set.

Alternating Row Color

Another enhancement that you may wish to make to a list report is to alternate the row color. This is a new feature in FileMaker Pro 7, and it can make reports much easier to read and more aesthetically pleasing. The option to alternate row color is found in the Part Definition dialog, which is shown in Figure 10.3; the quickest way to get there is by double-clicking on the body part label while in Layout mode. Figure 10.4 shows the effect that this feature can have on a list report.

Figure 10.3. The option to alternate row colors can be applied only to body parts; it is grayed out as an option for any other type of part.

Figure 10.4. Adding a subtle alternate row color can make a list report much easier to read.

Alternating the row color is more appropriate for use in reports that are to be viewed onscreen rather than printed, but if you use a light enough color, it may still print well. If you have a need for both print and onscreen versions of the same report, you may end up creating two different layouts, each optimized for a particular usage. This is more work, of course, but is certainly the best solution in many cases.

Horizontal and Vertical Dividers

Another method of increasing the readability of a list report is to add horizontal and/or vertical lines between the columns and rows. When both are used, the resulting report may resemble a spreadsheet; your perception of whether this is good or bad should guide your use of dividers.

We find that using thin gray lines as dividers is more effective than using solid black lines. That way, it's easier to differentiate the data on the report from the grid. There's a risk, though, that too many grid lines, especially in a complex list report, can actually obscure the data. Try to use just as many lines and/or field borders as necessary to increase the readability of your report, but no more.

Report Aesthetics

In some sense, the use of dividing lines is certainly an aesthetic choice. But there's a sense where it's not: The well-respected design expert Dr. Edward Tufte wrote in the Visual Display of Quantitative Information about the concept of data-ink ratio. Eliminating "chart junk," such as most borders, frames , and gridlines, gives the data room to breathe without sacrificing any readability or meaning.

Placing dividers into your report typically involves nothing more than drawing some lines on the layout. When adding a horizontal line between rows of data, we generally put it beneath the data as a baseline rather than above it. You can then add whatever effect you need beneath your column headers to set them apart from the first row of data.

Adding vertical dividers to a list report can be a bit tricky until you get the hang of it. The key is that your vertical lines need to be the same height as the height of the body part itself. If they're too small, you'll get a dotted -line effect. Use the Object Size palette to ensure an exact fit. The top of your vertical line should begin one pixel below the top of the body part. It usually looks better if there's some horizontal space between vertical lines and your data cells . If users are allowed to click into fields on the report, however, the field frames that appear may not look aesthetically pleasing. If your list view is truly acting as a report, then you should turn off entry into all fields (using the Field Behavior dialog). That also means that your field frames will never be visible, which is a good thing. Your vertical lines can define the space between fields without interference from field frames. If users need to be able to click into fields, consider then turning off the option to "Show Field Frames When a Record is Active"; this is an option in the Layout Setup dialog.

For more on the Layout Setup dialog, see "Creating and Managing Layouts," p. 91 .

If you find that horizontal lines between every row of your report makes the report look too cluttered, it's possible also to add horizontal lines that just appear, say, every fifth row. The effect of this is illustrated in Figure 10.5.

Figure 10.5. Having lines appear less often provides the visual guide necessary to follow a row across but doesn't overpower the data itself.

You need to add two new fields to your table to achieve this effect. The first is a global container field, which we'll call gLine . Place this field on a layout that you can use as a scratch pad (we generally refer to these as "utility layouts"), then draw a horizontal line on your layout. Copy the line to your clipboard, switch back to Browse mode, and paste the line into the gLine field.

The other field you need is a calculation field (set to return a container result) with the following formula:

 Case ( Mod ( Get (RecordNumber) ; 5 ) = 0 ; gLine ) 

In effect, this formula says that for any record that's a multiple of five, be the contents of gLine , or else be nothing.

On your list layout, finally, you'd place the calculation field as a long, thin object along the bottom of your body part. You need to reduce the field's font size to make the object thin. Also, go into the Graphic Format options for the field (by right-clicking it in Windows , Control-clicking it on Mac): select the Reduce or Enlarge Image to Fit Frame option, uncheck the Maintain Original Proportions option, and set the alignment to be Left, Bottom.

NOTE

You might be wondering whether you can just use Table view for your list reports; it provides a lot of the functionality discussed here (gridlines, sortable headers) for free. In general, though, Table view isn't suitable for reports, especially those that need any degree of "polish" to them. For one thing, the column labels must be the names of your fields; if you use any naming conventions your field names may not be terribly user friendly. Another issue in some reports is that you can't have multiple lines of data per row or any objects that overlap one another. Finally, one of the major drawbacks to Table view is that buttons and other layout objects don't show up.

Sortable Column Headers

An interface convention that's been widely adopted by software applications is that clicking on the various column headers of a list report sorts the set of records by that column. It's relatively easy (especially after you've done it a few times!) to add this sort of functionality to your list reports in FileMaker Pro. There are several ways you can go about this, but they're all essentially variations on the same basic theme, so we present a relatively " vanilla " method that you can spice up as you see fit.

NOTE

Another easy way to sort a set of records is to (Control-click) [right+click] on any field and choose Sort Ascending or Sort Descending. You don't need to know the name of the field or fret about finding it in a long list of available fields.

The two components of a sortable column header routine are a script (which does the actual sorting) and a graphic indicator to let the user know by which column the list is sorted. You can use whatever graphic indicator you want for this purpose. The simplest, and what we use here, is simply a special background color, but you can also use other indicators if you prefer. Figure 10.6 shows an example of what a list layout might look like after sortable column headers have been implemented. In the example, the set of records has been sorted by the values in the Quiz1 field.

Figure 10.6. Users can re-sort this list report any way they want by clicking on the column headers.

TIP

An alternative to indicating the sort column graphically is to use text formatting functions to change the appearance of the column labels. It's quite similar to the approach discussed here, except that you would use calculated text fields rather than calculated container fields.

Several fields need to be added to your database to make the graphic indicators for this routine. These fields can be added to whatever table you're working with (here, Student), but it's also possible (and arguably better) to place these new fields into a separate table. This allows them to be reused in other places, and it also helps keep your data tables free of clutter. In this example, the utility table is called globals .

The following fields need to be created in the globals table:

 gHighlight - Container - Global gSortField - Text - Global Highlight_Quiz1 - Global Calculation - Case (gSortField = "Quiz1" ; gHighlight) Highlight_Quiz2 - Global Calculation - Case (gSortField = "Quiz2" ; gHighlight) Highlight_Quiz3 - Global Calculation - Case (gSortField = "Quiz3" ; gHighlight) Highlight_Average - Global Calculation - Case (gSortField = "Average" ; gHighlight) Highlight_FirstName - Global Calculation - Case (gSortField = "FirstName" ; gHighlight) Highlight_LastName - Global Calculation - Case (gSortField = "LastName" ; gHighlight) Highlight_StudentID - Global Calculation - Case (gSortField = "StudentID" ; gHighlight) 

Notice that all the calculation fields have been set to use global storage. This is so they can be used on any layout, even by those attached to unrelated tables. They should also be set to return a container result. After gSortField has been set to the name of a field from the quiz score report (this happens in the script shown in Listing 10.1), one of the seven calculations will resolve to the contents of gHighlight ; the other six will be empty.

After these fields have been defined, you need to put a swatch of color into the gHighlight field. Switch to Layout mode and draw a colored rectangle. Copy it to your clipboard, return to Browse mode, and paste it into the gHighlight field.

There's still a little layout work to be done on the report itself.

1. Position a single gray rectangle behind all the column labels.

2. Place horizontal lines on top of the gray bar as necessary to segment the header row.

3. Then, on top of the gray bar (but beneath the column labels), place the seven Highlight calculation fields from the globals table. Each should be sized to fit its particular label.

4. Define each to be a button that calls a script called "List Report - Sort" (which is shown in Listing 10.1; you need to create the script before defining the headers as buttons).

Although all seven buttons call the same script, each passes that script a unique parameter. In this example, the parameters are simply the names of the fields themselves . That is, clicking on the Quiz 1 header sends the parameter Quiz1 , and clicking on the First Name field sends the parameter FirstName . You can also choose to pass a numeric code instead of the field name. This type of abstraction makes the buttons more reusable and means that you don't have to edit the parameter if your field names change, but we think it's more intuitive when learning this routine to use the actual field names.

For more information about using script parameters, see "Script Parameters," p. 422 .

Listing 10.1. List Report-Sort Script

 Set Field [globals::gSortField; Get(ScriptParameter)] If [Get (ScriptParameter) = "Quiz1"]     Sort Records [Restore; No dialog] Else If [Get (ScriptParameter) = "Quiz2"]     Sort Records [Restore; No dialog] Else If [Get (ScriptParameter) = "Quiz2"]     Sort Records [Restore; No dialog] Else If [Get (ScriptParameter) = "Average"]     Sort Records [Restore; No dialog] Else If [Get (ScriptParameter) = "FirstName"]     Sort Records [Restore; No dialog] Else If [Get (ScriptParameter) = "LastName"]     Sort Records [Restore; No dialog] Else If [Get (ScriptParameter) = "StudentID"]     Sort Records [Restore; No dialog] End If 

Each of the Sort Records steps is defined to sort by the appropriate field. Also, because gSortField is set in the first step, the correct Highlight field will be turned "on" in the globals table; after the sort is performed, the column heading will therefore accurately reflect the sort order.

TIP

If you have your list report displayed simultaneously in multiple windows, each report can be sorted differently, but the graphic sort indicator highlights the same field in all the windows. That is, if you were to click on the "Last Name" header in the active window, that window's found set would be sorted appropriately, but all open windows would have "Last Name" highlighted as the sort order, even when they may in fact be sorted differently.

You can easily extend this example on your own to allow for both ascending and descending sorts. To do so, you would need another field in the globals table (something like gSortDirection ) to indicate the direction of the sort. Then add more conditional statements to the script so that a combination of field name and direction determines how to sort the records. Finally, alter the Highlight calculations in the globals table so that they display different images for ascending and descending sorts. You can either create a separate global container field to house the descending image, or simply turn gHighlight into a repeating field and have a conditional statement in the calculation resolve to the appropriate repetition. As an example, the definition for Highlight_Quiz1 might end up as the following:

 Case (gSortField = "Quiz1" Case (gSortDirection = "Ascending" ; gHighLight[1] ;  gHighlight[2] ) 

Finally, because the sort order and the column images are all based on global fields, this routine is multi-user friendly. Two different users can be viewing the same report but have it sorted differently.

Go to Detail

No matter whether the set of records displayed in your list report is the result of an ad hoc find by a user or a canned report routine, you'll probably want to enable users to see additional details for a particular record. Typically, if you allow users to enter into fields in the list report, then you have a discrete button at the beginning or end of the row that a user can click on to get to a detail view. If you don't allow data entry, then it's common to let a user click anywhere on the row to be taken to a detail screen. To achieve the latter result, place a long transparent rectangle (to which you attach a navigation script) on top of the row. It should be the same height as the body itself so that there aren't any dead spaces between rows.

You have a few choices about how to display the detail record. The easiest thing to do is have the script navigate to a form view data entry layout. Another option to consider is to have the detail record pop up in its own window. This enables users to go back and forth more easily between detail and list layouts.

For more on scripting techniques like this, see "Window Management Techniques," p. 430 .