AnsweredAssumed Answered

Creating Filemaker Pro summary reports--Tutorial

Question asked by philmodjunk on Jan 11, 2010
Latest reply on Feb 1, 2015 by PhilipRand_1

Title

Creating Filemaker Pro summary reports--Tutorial

Post

This tutorial assumes that you are using Filemaker Pro 10. If you are using an earlier version, you can create and view the following sample reports, but you will have to enter preview mode before you can see the properly formatted reports as these types of reports are not visible in browse mode with earlier versions of Filemaker.

Summary reports allow you to organize your data into meaningful groups with aggregate calculations (calculations spanning values in more than one record) such as a total, count, average or maximum value computed for each such group. In addition, “grand total” type values may be computed and located at the end or beginning of the report.

Here’s a simple example that we’ll use to demonstrate several key concepts as well as a few “tricks of the trade” that you may not have realized were possible.

 Let’s define two tables. You only need one table for a summary report, but we’ll use the second table to illustrate how we can combine data from more than one table in such a report.

Table1: Invoices

Fields:
InvoiceNumber (Auto-entered serial number)
Invoice Date (date field)
Customer Name (text)

Table2: LineItems

InvoiceNumber (number)
PartNo (number)

PartName (text)
Qty (number)
Category (text)
Total Qty (Summary, total of Qty)

Define a relationship:

Invoices::InvoiceNumber = LineItems::InvoiceNumber

Enter Data:

Table 1: (3 records)

InvoiceNumber Invoice date Customer Name

1.                   12/10/2009  Tom
2.
                   12/10/2009  George
3.    
              12/11/2009  Jane

Table 2 (5 records)

InvoiceNumber  PartNo   PartName   Qty  Category

1.                          1            Widget         1     Auto
1.                          2            Thing           2    Home
2.                          1            Widget         4    Auto
2.                          3            Tool             1    Home
3.                          1            Widget         5    Auto

This is sample data of a type you might see in a typical invoice database where you’d have a portal to LineItems located on your invoices layout. You could print such a layout, but if you have more items purchased than you have rows in your portal, your report won’t be able to show all the items purchased. You also won’t be able to get a report that tells you the total quantities of each item sold.

 Now we’ll create a simple summary report for printing out our line item data: 

  1. Select your LineItems layout and enter layout mode.
  2. Select Part Setup… from the layouts menu
  3. Click the Create button to create a new layout part.
  4. Select Sub-summary when sorted by… InvoiceNumber.
  5. Click OK and choose the “print above” option for this part.
  6. Click Create to create a second sub-summary part “sorted by” InvoiceNumber, but this time; select the “Print Below” option.
  7. Click OK to dismiss the Part Setup… dialog box.
  8. Drag the border between the upper sub-summary part and the body down to make it larger.
  9. Drag the field label and field for Invoice Number into this upper sub-summary part.
  10. Drag the field labels for PartNo, PartName, Qty and Category into the sub-summary part and position them so that they form column headers. Move the fields for these labels into a single row with each field positioned in the body, under its field label. You can resize your fields to better fit your layout if you wish, but make sure that no part of these fields cross or touch the border between the sub-summary part and the body.
  11. Drag the field label and field for Total Qty into the lower sub-summary part.
  12. Drag the boundary between the body and the lower sub-summary part up to make it as small as possible.
  13. Save your changes and return to browse mode.
  14. Select View as List from the View Menu or click the “List View” Icon in the status area.
  15. Select Sort from the records menu and sort your records by InvoiceNumber.

You should now see the following report:

InvoiceNumber 1

PartNo   PartName      Qty      Category

1             Widget          1          Auto
2              Thing            2          Home

Total Qty: 3

InvoiceNumber 2

PartNo   PartName      Qty      Category

And so forth…

Now if this were a real invoice, we’d want the invoice date and customer data to appear also. Return to layout mode and use the field tool to add the InvoiceDate and CustomerName Fields from the Invoices table to the upper sub-summary part. You can place these fields to the right of the InvoiceNumber field.

Return to Browse mode and you should now see the correct date and customer name to the right of each invoice number.

Now let’s make our report more sophisticated:

  1. Return to layout mode and create a new sub-summary part. Make it “sorted by” Category and select “Print Above”.
  2. Click OK to dismiss the part setup dialog.
  3. Move the Category field and label into this new layout part. Resize the part and place a copy of the field labels for the other three fields in the body in the new sub-summary part positioned so that they can serve as column headers.
  4. Copy the Total Qty field and label to the clipboard and paste this copy into the new sub-summary part.

We might also want our report to display a “Grand Total” quantity of all the items purchased.

  1. While still in layout mode, double-click the layout part label for the layout’s footer.
  2. Change this part from “footer” to “Trailing Grand Summary” and dismiss the dialog boxes.
  3. Put a copy of the total quantity field in this part and label it “Grand Total”.

Now return to browse mode and check to confirm the only visible change is that the Category field and label has disappeared and that you have a new Grand Total. If other fields have vanished, return to layout mode and resize layout parts and/or move fields and labels so that no part of the objects in the body cross or touch the boundary with a sub-summary part. (Any object with even one pixel on or across this border will be treated as part of the sub-summary part.)

 If you can’t get a part boundary to move, hold down the Alt key (windows) and drag the boundary. Then drag the layout objects to place them on the correct side of the boundary.

To see the results of all these changes, return to Browse Mode and sort your records by Category instead of InvoiceNumber. Note how one layout can now produce very different reports simply by changing how the records are sorted!

Further experiments:

  1. Try using both InvoiceNumber and Category fields in your sort order.
  2. Try switching the order of the fields specified in the sort order and note how your report changes appearance.
  3. Try the Unsort option.
  4. Try including “InvoiceDate” in your sort order.
  5. Perform a find for just one invoice number or a specific Invoice date, and then sort your records in different ways.
  6. Click a sub-summary’s part label and choose a fill color for it. This can be a simple way to make sub-summary parts stand out and better function as “visual boundaries” between sorted groups of body records.
  7. Enter layout mode and use Duplicate Layout from the Layouts menu to make a copy of your layout. Click on the label for the Body layout part and press the delete key to delete it and all the objects in this layout part. Now browse and sort records on this layout. You now have a summary report that doesn’t list data from any of the individual records and instead just reports the sub-totals for each record group.

Outcomes