10 Replies Latest reply on Feb 1, 2015 7:56 AM by PhilipRand_1

    Creating Filemaker Pro summary reports--Tutorial

    philmodjunk

      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.

        • 1. Re: Creating Filemaker Pro summary reports--Tutorial
          chadlawie
            

          Issue: Issue with "total of" summary with the following options selected: running total, re-start summary for each sorted group.

           

           

          I have a number of projects that I try to spend XX amount of hours on each month.

           

          My report is simple

           

          summary by project,

           

          displaying:

           

          Field 1. the number of hours I intended to spend on each project,

          Field 2. the number of hours I have spent on each project,

          Field 3. the number of hours remaining I have to spend on each project.

           

          The culpret:

           

          Field 2. the number of hours I have spent on each project,

           

          This field is a "total of" summary with the following options selected: running total, re-start summary for each sorted group.

           

          The issue:

           

          "field 1" - "field 2" = "field 3"

           

          Except that instead of "field 2" subtracting the total time spent on that particular project it only subrtacts the first record of time spent on that project. 

           

          Example:

           

          Field 2(I spent an hour each business day of the week, five hours total)

           

          on the project "fixing the house".      

           

          Field 1(My goal for the whole week is 10 hours.)

           

          Field 3(How many hours do I need to work this weekend to meet my goal?)

           

           

          Field 1 (10 hours) - Field 2 (5 hours) =  Field 3(should be 5, but my report only takes into account the time from the first day, or 1 hour, so I get 9 hours)

           

          Question:

           

           How do I tell Filemaker to use the last record for the project instead of the first!?!

           

           

          Thank you for any help you can offer!

           

          Chad

          • 2. Re: Creating Filemaker Pro summary reports--Tutorial
            philmodjunk
               Unlike the sub-total you see when you place a summary field in a Sub-Summary part, references to a summary field in a calcualtion will return the grand total of the current found set. If you need a subtotal, use the GetSummary(field 2 ; breakfield) to extract just the subtotal. "Break Field" should be the same "sorted by" field you specify in the corresponding Sub-Summary part.
            • 3. Re: Creating Filemaker Pro summary reports--Tutorial
              bobearl

              Hi Phil,

               

              Excellent discussion.  I am struggling with something that seems quite simple but I can't seem to solve.  Basically, instead of the display you got above:

              InvoiceNumber 1

              PartNo   PartName      Qty      Category

              1             Widget          1          Auto

              2              Thing            2          Home

              Total Qty: 3

              InvoiceNumber 2

              PartNo   PartName      Qty      Category

              I'd like to show a list of the Invoices and the total quantity at the top of the report followed by a list of all the Invoice items, like so:

              InvoiceNumber 1  -  Total Qty: 3

              InvoiceNumber 2  -  Total Qty: 8

              PartNo   PartName      Qty      Category     InvoiceNumber

              1             Widget          1          Auto                          1

              2              Thing            2          Home                       1

               

              [Obviously, this doesn't seem very useful for the example of invoices.  My goal is to make a task list, which lists all tasks sorted by user, and shows the number of tasks for each user at the top of the page.  This allows a heads-up view of the number of tasks, but allows someone to also see all the tasks on the same layout.]

               

              Is is possible to make a list of the subsummary items and a list of all the list items on the same layout?  By leaving out the Body part, I can generate the susbsummary list, but then the parts list isn't visible on the same layout.  Many thanks!

               

              • 4. Re: Creating Filemaker Pro summary reports--Tutorial
                Heatherrd

                I think I have a similar problem.  I'm very new to FMP and am stuck on how to create reports.  I'm working on FMP11 30 day trial, Windows XP.

                My tables are:
                Materials, Material_Lines, Styles, Orders, Order_Lines, Customers

                The data entered in the Order_Lines portal on Orders table needs to be sorted and subtotaled to create a production schedule that looks like this: (It was originally done in excel with each piece of data entered manually from the orders onto the production schedule.  This was very time consuming and had to be carefully updated when new orders came in.) Customer and XS-XL (sizes) are column headers.

                Style: XYZ Shirt
                Ship Date: 7/1/10
                Customer XS S M L XL
                Gap 2 4 4 4 2
                Banana 4 4 4 2
                Old N 3 3 6 6 3
                Sub TTL 9 11 14 12 5 total 51

                Style: XYZ Shirt
                Ship Date: 8/1/10
                Customer XS S M L XL
                Macy's 10 10 10 10 5
                Banana 4 4 4 2
                H&M 5 5 3 3 3
                Sub TTL 19 19 17 15 8 total 78

                Grand total: 129 (of Style XYZ Shirt, all sizes, all Ship dates)

                 

                When entering the Order (like an invoice), the customer is selected and the ship date.  Then in the Order_Lines portal, the Style/Color (Style_ID) is selected, then the size is selected from a value list, then the quantity is entered and the line total is calculated.  Nothing fancy needs to be done on the Order, but the information must then be made into a production schedule.   I thought I could bring the data from the Order_Lines into a portal on the Style table. Maybe I could have several portals, one for each ship date?  There are 3 or 4 ship dates per season.  How can I get the Order_Lines separated first by 3 or 4 parameters before having the quantity totaled?  I need it separated by Style, Ship Date and then Size and listed by Customer, then subtotaled.  Is this posible?  I need to know if I should keep trying or if this isn't what FMP can do.

                I added a new layout and followed your instructions in the tutorial through #15 and I'm not seeing what you say I should see.  The labels in the sub-summary aren't showing in the list view, only in the form view.  The subtotal is working fine for the form view, however, since you can only see one record at a time, it isn't useful.  What am I doing wrong?

                 

                Thanks in advance,
                Heatherrd












                • 5. Re: Creating Filemaker Pro summary reports--Tutorial
                  chadlawie

                  Does this have to be a report? Could build it on a seperate layout?

                   

                  I would try switching to "list view"

                   

                  in the body place:

                   

                       InvoiceNumber 1  -  Total Qty: 3

                   

                  and create a portal in the footer that lists:

                   

                  PartNo   PartName      Qty      Category     InvoiceNumber

                  1             Widget          1          Auto                          1

                  2              Thing            2          Home                       1

                   

                   

                   

                  Chad

                  LongerDays.com

                   

                  • 6. Re: Creating Filemaker Pro summary reports--Tutorial
                    chadlawie

                    Heatherrd,

                     

                    Make sure you sort the records, probably by style, then ship date.

                     

                    This should be in your header:

                    Style: XYZ Shirt
                    Ship Date: 7/1/10
                    Customer XS S M L XL

                     

                    This is your body:

                    Gap 2 4 4 4 2
                    Banana 4 4 4 2
                    Old N 3 3 6 6 3

                     

                    This is your subsummary:

                    Sub TTL 9 11 14 12 5 total 51

                     

                    This is your titlefooter:

                    Grand total: 129 (of Style XYZ Shirt, all sizes, all Ship dates)

                     

                    Hope this helps!

                    Chad

                    LongerDays.com

                    • 7. Re: Creating Filemaker Pro summary reports--Tutorial
                      philmodjunk

                      Bearl,

                      This isn't something you can do with a single summary report using just summary report features. You could make two layouts for this as has been suggested.

                      Also, depending on how many individuals you need to list, you might be able to set up a portal in your header for this.

                      • 8. Re: Creating Filemaker Pro summary reports--Tutorial
                        JimWaldvogel

                        Hello.

                         

                        I am experimenting with a database/table design for a time and expense billing situation.  Nothing I have done is in stone and am open to change anything.  

                         

                        With my job, I bill for my time at an hourly rate and out of pocket expenses.  I read this tutorial and thought that the general database design (i.e. invoice table and line items table) might be the structure that would fit my needs. 

                         

                        I was thinking about having separate tables for time line times and expense line items.  These tables would be essentially the same with the exception that the time line items would capture my number of hours worked and hourly rate and the expense line items table would not have these fields. 

                         

                        The ultimate goal is to generate invoices from time to time which first list my time line items (number of hours, description, hourly rate and total charge for the line item), second list my out of pocket expenses (date, description and amount), and finally a grand total for the entire invoice.  


                        I understand the concept of not using a portal because the number of time and/or expense line items might exceed the number of portal rows.  

                         

                        The table design from the tutorial makes sense.  If it is a good design for my needs, I’m not sure how it would work for me in real life.  For example, if I wanted to generate an invoice that would contain time and expense line items from say January 1, 2010 through March 31, 2010, I assume I would create a new record in the invoices table which would give me a unique Invoice No.  This is where I start to get lost.  I’m not sure how I would then associate the new Invoice No. with the time and expense line items for January through March.  

                         

                        Questions:

                         

                        1.  Does it sound like the database structure makes sense for what I’m trying to achieve, or is there another structure that I should be considering? I am not sure that the database design listed in the tutorial would allow the invoice to contain line items from two separate tables (i.e, time line items and expense line items)?

                         

                        2.  If I go ahead and use this database structure (or something similar), how do I, for example, group/capture the January through March time and expense line items and associate them with the new Invoice No.?  


                        Thanks for any help you can offer.

                         

                        Jim

                        • 9. Re: Creating Filemaker Pro summary reports--Tutorial
                          philmodjunk

                          This tutorial describes a reporting technique. For most real-live applications, you'd use a portal to line items on an Invoice layout for data entry and use the summary report as a method for printing and/or summarizing data from these invoices and line items.

                           

                          For grandparent to Parent to Child table structures, the summary report works well. You base your report on the child table and link to related Parent and/or grand parent data for display in sub-summary and grand summary parts. It does not work well when you have two different "child tables". If you have two such tables, you end up choosing between a "merged table" that combines the records of your two child tables or reverting to a portals set to shrink (with all its limitations) for the child tables.

                           

                          Since you've indicated: "These tables would be essentially the same with the exception that the time line items would capture my number of hours worked and hourly rate and the expense line items table would not have these fields. "

                          I'd use one table to store both kinds of data and use filtered portals for input to help keep your Hours workded and expense records separate. Then on your summary report you can use a sort to group the two types of records into separate groups.

                           

                          • 10. Re: Creating Filemaker Pro summary reports--Tutorial
                            PhilipRand_1

                             

                            I feel that I am in the midst of Einsteins: my problem is quite elementary. I wanted to create a report of Christmas greetings sent and received, and types of card in each case. The objective was to get a report of the correspondent and whether a card (or whatever) was sent or received - if so in what year. The hope was to obtain "name" and, below that, a list of each year and type of communication for each year: name  - sent - type of communication sent - received - type of communication received. "Name" would be the heading and the other data would be below each name, i.e. Arnold, and below that year sent, year received, type of communication sent, type of communication received.

                            One layout is called "name" (listing all the names of current and past correspondents), the other "received - sent" (listing each year sent or received, and type of communication in each case). The "name" field is the basis of a value list for the related name field in the received-sent list.  In other words, the two are related by the field "name" via a value list. I used the report wizard, and since I was not summarizing numerical data, excluded the summary portions, put the fields I wanted in the report, and used "name" as the organizing field. Indeed, the report gives me the name for one record, but if i click list view, it gives me the list of years and types of communications for each name. Quite rightly, as you said above, I could (and probably should) use a portal, with enough lines to cover the years of interest, but what like is to obtain a list with each name, with all the data pertaining to it from the sent-received layout. At present, I have to do a search name by name. My suspicion is that I can only use the list option offered at the time of creation via wizard, and not the report form. Is that correct, or is there a way of putting text data into summary fields, in this case "name" such that I will obtain a complete list, name by name?

                             

                            Many thanks in advance from this green filemaker.