Creating Layouts for Reports

You've now knocked out two good layoutsa detail layout (Chapter 4) and a list layout (this chapter). Layouts like these meet many typical database needs: You've got your detail layout for finding and viewing individual records, and your list layout for rapidly scanning many records at once. You also want to do reporting, an equally important task in a typical database. A report's no different from any other layout as far as FileMaker is concerned. But report layouts are designed from the ground up to be printed. Almost no database gets by without some kind of a report layout, and most important databases have several, from straightforward lists to powerful snapshots of your data's important statistics, like sales by region or inventory by product category.

The People database needs a reporting layout, too. In this chapter you'll create a report layout for a simple purpose: printing a list of people. You can then file the printed list as a hard copy backup, take it with you on a trip, or mail it to an associate. But FileMaker's reporting powers go far beyond simple lists. The next chapter will introduce FileMaker's powerful data summarization and reporting capabilities.

5.3.1. Visualize the Result Preview Mode

First, you need a rough idea of how your layout should look. This step is especially important when you create a report, since the physical constraints of a piece of paper often dictate the working space you have. When you create a detail layout, you're free to make it large or small, tall or short, narrow or widewhatever meets the needs of your data. Not so if you want to print your layout.

In the spirit of visualization, how about a picture? From the Preview illustration in Figure 5-11, you can get a pretty good idea of how this layout is going to come together. It has a header, body, and footer. The header includes a title, the date, and some column labels, and the footer has just a page number (these parts print on the top and bottom of each page). The body is the most important part: It has all the fields that show your information.

You can see the report in Layout mode in Figure 5-12. To see your layout in Preview Mode, choose View images/U2192.jpg border=0> Preview Mode.

Figure 5-11. This is the report you'll add to the People database. Notice it's formatted to fit nicely on letter-sized paper when printed (it's in Preview mode here). You can also see the date the report was printed in the top-right corner, and a page number at the bottom of each page.

Figure 5-12. In Layout mode, you can see how this layout comes together. Everything here should look familiar to you: parts, text objects, lines, fields, and merge fields. But don't fearthere's plenty left to learn, from FileMaker's automatic layout assistant to the ins and outs of printing a layout.

 

5.3.2. Creating a Report Layout with an Assistant

In the example in the previous section, you started with FileMaker's blank layout. Since blank layouts are always just blank, there were no more choices to make. This time, FileMaker does a lot more work for you. Before it does, it asks you a handful of questions. Your job is to answer the questions, click Next, answer some more, and so on. When FileMaker has all the information it needs, it changes the Next button to a Finish button. FileMaker then creates the layout according to your specifications. Here's how to proceed:

1. In the People database, switch to Layout mode and choose Layout New Layout/Report. Name the new layout You've been through these steps before (Section 5.1.6). Next, select a layout type (Figure 5-13), so FileMaker knows which choices to offer you in the upcoming steps. Different layout types, different options.

   

   Figure 5-13. This is the first round of choices when creating a "Columnar list/report" layout. Here, you decide whether or not you want "grouped data" (you don'tmore on that in Chapter 6). You also need to decide if you want FileMaker to make sure your layout fits the width of a piece of paper. As you make selections, the picture in the top-right corner shows a rough representation of what your choices do.

    
2. Select the "Columnar/list report" radio button. While you're at it, turn on the "Constrain to page width" checkbox.

   The picture in the top-right corner of the window changes to show that the layout won't extend past the width of a page.

3. Click Next.

   Now you see the window shown in Figure 5-14, where you tell FileMaker what fields to add to the layout.

4. Click Move All.

   Every field in the "Available fields" list moves to the "Layout fields" list. Click Next.

   

   Figure 5-14. You pick the fields by adding them to the Layout Fields list. FileMaker adds them to the body part for you when it creates the layout.

    
5. As shown in Figure 5-15, add the Last Name field to the "Sort order" list.

   In the "Report fields" list, click Last Name, and then click Move.

   

   Figure 5-15. Sometimes you want your reports sorted, so FileMaker offers to do it for you. In truth, it creates a script (Part 4) for you that sorts the records, switches to the layout, and goes to Preview modeeverything you do to view a typical report.

    
6. In the same way, add the First Name field to the "Sort order" list.

   The "Sort order" list now shows the Last Name field, then the First Name field. This arrangement tells FileMaker to sort first by Last Name, then by First Name. Click Next.

7. From the "Layout themes" list shown in Figure 5-16, select Standard.

   The picture changes to show a simple white background with black text. Click Next.

   

   Figure 5-16. Here's your chance to select a theme. As you make selections from the "Layout themes" list, the picture changes to show how the theme will look. Click all you wantit's pretty much guaranteed that none of them will match your business collateral. When you're done browsing, pick Standard for the example on these pages.

    
8. From the "Top left" pop-up menu (Figure 5-17), choose Large Custom Text. When the Custom Text dialog box appears, type People I Know, and then click OK.

   The "Top left" pop-up menu now shows that you've selected Large Custom Text.

9. From the "Top right" pop-up menu, choose Current Date. Then, from the "Bottom center" pop-up menu, choose Page Number.

   The pop-up menus' content changes to reflect your choices. Click Next.

10. On the next screen, turn on the "Create a script" radio button. Then, in the "Script name" box, type Report.

    As explained in Figure 5-18, you've just told FileMaker to add an item with this name to the Script menu. You can choose this menu command to run the report. Click Next.

11. In the assistant's final screen (Figure 5-19), turn on "View the report in Layout mode."

    This tells FileMaker to deposit you in Layout mode after it finishes creating the report. Click Finish.

Figure 5-17. Once you have a theme, you get to choose what to put in the headers and footers. You can add Page Numbers, the Current Date, the Layout Name, Custom Text, and Logos. And you can put these things in six different places: Top left, Top center, Top right, Bottom left, Bottom center, and Bottom right. FileMaker lets you put one thing in each spot.

Figure 5-18. Almost done! Remember a couple steps back when you defined a sort order for this report? As you learned then, FileMaker can create a script for you to perform the sort and show the report in Preview mode. Now you get to decide if you want it to or not. If you turn on "Do not create a script," then the sort order you specified before does you no goodwithout a script you have to sort the records yourself.

Phew! You're finished at last. Your new report layout should look something like Figure 5-20.

Figure 5-19. Time to make your last choice. You can opt to view the report directly in Preview mode, or you can view the new layout in Layout mode. Since you're going to need to make some adjustments to the layout, choose "View the report in Layout mode."

Figure 5-20. The layout FileMaker creates for you looks like this. It has all the parts and pieces, but it needs a little cleanup. You can reorganize the fields in a way that makes more sense to you, and you can always change fonts, sizes, and styles to suit your taste. You can use any of the tools you learned about in Chapter 4 to modify these objects. You can even put a picture of your first-born in the header. After all, it's your database.

 

5.3.3. Print Margins

Before you begin arranging things on the layout, you need a feel for the amount of available space. Normally when you print from FileMaker, the page margins are set to the minimum size allowed by your printer. This arrangement provides the most usable space possible, but at a cost: The marginsand the printable areachange as you switch printers. For a report that you intend to print often, you don't want to deal with that kind of inconsistency.

Luckily, you can override this behavior and set explicit margins. Before you do, though, you need to make sure FileMaker is using units that make sense for page margins. Follow these steps to set the units and the margins:

1. Choose Layouts Set Rulers.

   From the Units pop-up menu, choose Inches, then click OK.

   You're back on your new layout, where you've just told FileMaker to use inches for just about everything.

2. Choose Layouts Layout Setup, and then click the Printing tab.

Figure 5-21. These settings affect the way the current layout prints. For example, you can create a layout that prints in multiple columns (imagine printing sticky labels). You'll learn more about that in the next chapter. For now, draw your attention to the "Use fixed page margins" checkbox, which you need to turn on if you want this layout to have hardcoded (and consistent) page margins.

• Turn on the "Use fixed page margins" checkbox.

  The Top, Bottom, Left, and Right text boxes start out grayed out. As soon as you turn on this checkbox, you can type into them.

  Note: The numbers you see in these boxes (before you type anything into them, that is) are the margins associated with the printer you're hooked up to. That's why they probably look different from what you see in Figure 5-21.
• In each of the Top, Bottom, Left, and Right text boxes, type .5.

  Don't forget to type the decimal point. Your goal is to set the margin on all sides to half an inch.

• Click OK.

  The Layout Setup dialog box disappears and you're looking at your layout again. If you have sharp eyes, you notice the page width has shrunk a bit, as shown in Figure 5-22.

Figure 5-22. FileMaker draws thick, hashed lines right on the layout to show where the page breaks are. When you adjust page margins, the location of these lines changes. In these pictures, the top image shows the layout as FileMaker created it. Once you adjust the margins, your header no longer fits the width of a page, as shown in the bottom image.

Tip: If you like working in pixels and don't want the bother of switching units, take heart. You can probably do the math in your head even faster. Remember, there are 72 pixels per inch: A one-inch margin would be 72 pixels and a half-inch margin would be 36. (If you like centimeters, figure there are 28 pixels per centimeter.)

5.3.4. Improving the Report Layout

Now that you've specified your page size, you can begin to lay out the report. FileMaker already created header, body, and footer parts. It already set the layout to list view. And it already added a title, date stamp, page number, column labels, and fields in the proper parts. As usual, it just isn't very pretty. (Remember, Figure 5-30 on Section 5.3.5.5 shows how the finished product will look.)

5.3.4.1. Tidy up the header

Your first job is to clean up the header part. As shown in Figure 5-23 for guidance, your tasks involve mostly text formatting and dragging things around.

5.3.4.2. Rearrange the body

In Figure 5-23, you can also see before and after shots of the report's body. Again, you know everything you need to rearrange things. You want to replace the First Name and Last Name fields with larger merge fields (Section 5.2.3.1), rearrange the rest of the fields, and give them new font, size, and style formatting. This time, though, there are a few extra steps.

Figure 5-23. To fix up the header, you need to do a little rearranging and change some fonts. For the look shown here, resize the "People I Know" title to 18-point. Simplify the column headings and change them to 11-point bold italic. Slide the date stamp (the "//" text object in the top-right corner) left so it doesn't extend off the page. Finally, add a 1-point black line the full width of the page along the bottom of the header. (Instructions for the line are in the box below.)

UP TO SPEED
Adding Straight Lines

You often see horizontal lines that cross the width of a page in printed reports. Many reports include a line between the header and the body of the page. (Technically, such a line is in the header part.) Creating these lines is a snap:

1. In the status area, click the line tool. The mouse arrow changes to a cross-hair icon.
2. With the Shift key held down, drag a straight horizontal line anywhere on the layout. Don't worry about getting the line in the right place. You can more easily move it later.
3. Drag the new line all the way to the left, so that its left end touches the left side of the layout. As you drag left, FileMaker won't let you drag the line past the edge of the layout.
4. With the Shift key held down, drag the selection handle on the right end of the line until it touches the vertical page break line. Again, since you have the Shift key down, the line stays straight.
5. Using the mouse or the arrow keys, move the line up or down until it's positioned properly.

Using these steps, you can be sure your line is straight and exactly the width of a page.

First, you can delete the First Name, Last Name, City, State, and Zip fields. In their place, add two new text objects. Oneformatted as 14-point bold textshould hold merge fields for the First Name and Last Name fields, with a space between them. In the second, add City, and comma and space, State, another space, and Zip. Finally, change the font on the remaining fields to 10-point and italicize the notes field.

Once you've made these changes, you can rearrange the fields and objects using Figure 5-30 as a guide. (Don't worry about matching the example exactly. Periodically inspect your work in Browse mode and adjust the arrangement until it pleases your eye. Use your individual sense of style!)

With the fields in place, you have just one finishing touch to add to the body. It's hard to see in Figure 5-30, but there's a hairline-width line along the bottom edge. You can save some effort by duplicating the horizontal line you added to the header. Select the line and choose Edit images/U2192.jpg border=0> Duplicate. A new line appears just below the first. Move it into position, and use the Line Thickness tool to make it a hairline.

Tip: You can copy an object and position it in one quick move: Ctrl-drag (Option-drag) it. The Ctrl or Option key tells FileMaker to copy the object instead of move it. To constrain the movement, press Shift as you drag. Your new object stays right in line with the original.

5.3.4.3. Tweak the footer

Next up is the footer part. It's already almost perfect. But it would be nice if the report showed "Section 1.1" on the bottom of the third page, instead of the mysterious "3." Use the text tool to edit the page number text object ("##"), typing Page and a space before the page number symbol.

5.3.5. Sliding Layout Objects

Your layout is essentially finished; time to take a look. Choose Scripts Report to run the script you told FileMaker to create for your report on Section 5.3.2. As Figure 5-24 confirms, this report has one minor problem: Its not very paper-efficient. All that blank space beneath most items is the notes field, which may have lots of information, but usually doesn't. Wouldn't it be cool if you could tell FileMaker to slide the next record up if the notes field didn't need all that space? The engineers at FileMaker thought so too, and added a feature called sliding.

5.3.5.1. When to use sliding

Since the data in a field changes from record to record, the amount of space it takes up often changes too. Usually this behavior doesn't cause a problem. After all, you might want that empty space because you're printing onto a preprinted form, and everything needs to go in just the right spot on the page, or maybe your report design counts on consistent field sizes so things line up properly. But sometimes you can't get the effect you want without adjusting the layout based on the amount of datausually when you're trying to tighten things up on the printed page to avoid wasted paper or excessive spacing around data.

Sliding does three things to help in this situation. First, it lets fields shrink to just the right size for their data. After a field has shrunk, any object on the layout can slide up or to the left to fill the space left behind.

Figure 5-24. The report is looking better, but it sure wastes a lot of space. Only one record on this page has anything in the notes field, and even that is only two lines. But when you were arranging fields on the body, you told FileMaker to make the notes field about five lines tall, and that's how much space it reserves.

Finally, whole layout parts can shrink vertically to compensate for their shrinking contents. It's an idea that's much easier to see than to explain, so turn your attention to Figure 5-25.

Figure 5-25. These windows show the same thing: a page with two product records. The version on the left uses sliding to get rid of unsightly extra space. The width of the name field shrinks to fit its contents. The height of the description field shrinks to fit as well. The icon and category both slide left. The notes field, shipping info field, price field and shipping price field all slide up. Finally, the entire body part shrinks to fit its contents (notice how the second product record starts higher on the page in the left window).

 

5.3.5.2. Choosing the objects to slide

Object sliding in FileMaker is notoriously hard to figure out. It's a bit like that board game "Go." The rules take a minute to learn, but a lifetime to master. Here goes:

Sliding exists for only one reason: to compensate for changing field data. Therefore, unless you set at least one field to shrink, nothing on the layout moves. Unfortunately, you can't explicitly set a field to shrink. Instead, you set it to slideand FileMaker makes sure it shrinks too. This seemingly simple principle is guaranteed to confuse you at least 36 times in the near future. You've been warned. Figure 5-26 shows how this field shrinking business works.

Once you've figured out which fields should shrink to fit their contents, you need to decide which objects should slide. What does that mean exactly? Normally, when you add an object to a layout, you specify exactly where it goes. But when the object is set to slide, its position is no longer fixed at an exact spot on the layout.

Instead, it moves up (or to the left) if other objects above it (or to the left of it) move or shrink. Figure 5-27 illustrates this concept.

Figure 5-26. In the top window, the line and text object are set to slide up…but as you can see, there's no sliding going on. That's because the field above them isn't set to slide. In the bottom window, the field has been set to slide up too, and now things are clearly sliding and shrinking.

Figure 5-27. Sliding objects (like the field and text object on this layout) position themselves relative to other objects on the layout. For example, since this field shrinks, and the text object slides up, the text object is always the same distance from the field. Its position on the page, though, changes from one record to another. The picture isn't set to slide, so it doesn't move at all.

 

5.3.5.3. Setting sliding options

Once you have a general idea of which elements need to slide (and which fields must shrink accordingly), you can start telling FileMaker.

FREQUENTLY ASKED QUESTION
Merge Fields vs. Sliding

Why should I bother with sliding? Isn't that what merge fields are for?

It's true that merge fields and sliding objects have some things in common. Both adjust the data shown on a layout, squeezing things together in the process. But they have some major differences:

• Merge fields work everywhere, even in Browse mode. Sliding objects, on the other hand, have no effect on Browse mode (or Find mode). Instead, they only do their thing in Preview mode and when printing.
• Any object on a layout, including pictures, can slide. There's no way to incorporate pictures into merge fields.
• Fields that slide act just like normal fields in Browse mode, in that you can edit the data in them. Merge fields are just text objects, and are only for display.

Bearing all these differences in mind, you can easily figure out which method to use. If you have a few fields that you want to display as a single block of text, use merge fields. If your needs are more complex (incorporating graphics, for instance), or you need to be able to edit data on the layout, use sliding objects instead.

Also, there's absolutely nothing wrong with using both on one layout. In fact, the layout you just created uses merge fields for the name and address, but you're about to add sliding to the notes field and hairline. You can even tell a text object containing merge fields to slide if you want. (Phew!)

The general process works like this:

1. Select one or more objects on the layout.

   FileMaker puts handles on the corners of each selected object.

2. Choose Format Sliding/Printing.

   Make your selections.

   Right now you're just getting comfortable, so feel free to choose whatever you want. For example, choose Sliding Left to make your selected objects slide to the left to get rid of excess space. Each option will be explained in the next section. Click OK when you're done to return to your layout.

3. Click OK.

   FileMaker returns you to your layout.

Since sliding affects the way your layout looks only in Preview mode and when printing, your layout won't look any different after you've made these changes. You can choose View images/U2192.jpg border=0> Show images/U2192.jpg border=0> Sliding Objects to identify the objects that will slide. With this option turned on, FileMaker draws tiny arrows on each sliding object, showing the direction it will slide.

Figure 5-28. The Sliding/Printing dialog box is where you go to tell FileMaker how an object should slide, if at all. (You can also turn on "Do not print the selected objects" which does exactly what it says. Hence the "/Printing" in this dialog box's name.) You'll learn about all those other buttons soon.

Tip: Since setting sliding options requires a trip to a special windowand all those clicksit can be tedious. If you have more than one object that will get the exact same settings, save yourself trouble by selecting them all first. If you plan ahead, you can usually set up even a complex sliding arrangement in just a few batches.

Now that you've seen the Sliding/Printing window, it's time to learn how to make sense of all those options.

5.3.5.4. The Sliding/Printing dialog box

The Sliding/Printing dialog box has several options to control just how the selected objects slide (and shrink if appropriate). In general, an object can slide left, up, or both.

If you want something to just slide left, you're in luck. Simply turn on the "Sliding left" checkbox and you're done. When you print or preview the layout, the objects slide to the left when field data isn't long enough to fill the full width of the field.

Objects that slide up, on the other hand, need a little more thought. To start, turn on the "Sliding up based on" checkbox.

When you turn this checkbox on, you make three more options available.

The "All above" and "Only directly above" radio buttons are hard to explain in words. Luckily, FileMaker gives you a picture to help you understand what they mean.

FREQUENTLY ASKED QUESTION
This Field Slides and That Field Shrinks

What if I have a field that should shrink but not slide? How do I do that?

Would you believe, "You can't?" Sadly, it's almost entirely true. This series of images shows the problem, and one workaround. Imagine you use FileMaker to keep track of the kinds of boards you sell. You record the material and the length, width, and height of the board. Sometimes you print this layout, and the first picture shows the result. It's not bad, but you decide you want the "ft" and "in" unit labels to slide left so they're closer to the numbers.

You remind yourself that the fields need to shrink for the sliding to work, and that shrink means slide. So you set the Length, Width, and Height fields, the "ft" text object, and the two "in" text objects all to slide left. The second image shows the resultnot exactly what you had in mind.

What happened? Since the fields are set to slide, they didright into one another. What you really want is for the fields to shrink but not slide. Alas, FileMaker has no such setting.

If you really want this to work, you have to get creative. The fields don't slide left if there's something in the way. Instead, they maintain their position relative to whatever is to their left. As shown in the third picture, you can put simple vertical lines next to the Width and Height fields. Since they're not set to slide, these lines act as little roadblocks for the slippery fields. To complete the effect, set the line thickness of the little lines to None. They disappear from the layout completely, but since they still exist, they still stop the sliding.

This workaround isn't perfect. It leaves invisible objects on your layout, which is always a recipe for trouble down the road ("Where is that line?!" or "What is this thing?"). But if you must have shrinking without sliding, a trick like this is all you've got.

Remember an object that slides up stays the same distance from the object above it. But which object? See Figure 5-29.

Finally, if you turn on "Also reduce the size of the enclosing part," the part the object is on shrinks to fit its contents. This setting is a little misleading, though. For example, imagine you have two sliding objects on the body part. One of these is set to reduce the body part, while the other isn't. How can the body both reduce and not reduce at the same time?

Figure 5-29. Everything on this layout is "above" the Do Not Contact checkbox. But for the purposes of sliding, only two count. From FileMaker's perspective, the Address field is directly above it, while the email field is just above it. If the Address field and the Do Not Contact checkbox are both set to slide up, the "All above" or "Only directly above" choice becomes important. If you choose "All above," the checkbox never slides because the Email field doesn't. If you choose "Only directly above," it sticks with the Address field, even if that means sliding up alongside the Email field.

In truth, the "Also reduce the size of the enclosing part" setting only matters for the lowest object on the part. If that object wants the part to reduce, it will. If it doesn't, the part won't. Anything you set on any other object doesn't matter. The lesson: If you're trying to get your part to reduce, focus your attention on the bottom- most object on your layout.

5.3.5.5. Using sliding on the report layout

The report layout you've been working on provides a dramatic example of how sliding can both save space and produce a more attractive printout. You might have lengthy notes about some of the people you know, and no comment about others. Here are the specific steps for shrinking the notes field to avoid wasting space.

1. Select the notes field and the hairline below it.

   Both objects have selection handles, and the Format Sliding/Printing command is no longer grayed out.

2. Sliding/Printing.

   Turn on "Sliding up based on" and select "All above."

   You want the notes field to shrink vertically, and the line to slide up next to it, so up makes sense.

3. In this case, you're interested in sliding things up, so turn on the "Sliding up based on" checkbox. With this option selected, the notes field shrinks vertically to hold just its contents. The hairline also slides up, maintaining its distance from the notes field as it shrinks.

If you stop here, you won't quite have your problem solved. Even though the hairline slides up as much as possible, the body part itself doesn't shrink, and your report still wastes paper. To solve this problem, turn on "Also reduce the size of the enclosing part." When you're done, click OK.

If you view your report now (Scripts images/U2192.jpg border=0> Report), things should look much better. Figure 5-30 agrees.

Tip: If you need to rearrange the order of your layouts, choose Layouts Set Layout Order. Like other elements lists in FileMaker, drag the double-arrow to reorder the list. You can also use the checkbox column to change which layouts display in the layout pop-up menu.

Figure 5-30. Now each record in the People database is small and paper-efficient. When a record does have notes, like the last one here, FileMaker makes just enough room to hold them, with no wasted space. If you choose File images/U2192.jpg border=0> Print while viewing this report, you get a printout that looks surprisingly like what you see onscreen.