1 Reply Latest reply on Feb 9, 2009 11:05 AM by TSGal

    Totals

    MikeTV

      Title

      Totals & Sub Totals in Grouped Reports

      Post

      My credentials:

      • Experienced DataEase (for DOS) database developer....
      • .....but new to FileMaker
      • Using FM Pro 9 Advanced 

       

       

      I am having trouble in showing Totals & Sub Totals in a grouped report. 

       

      Scenario is this:

       

      I have a table in a telephone call logging system which contains all the outgoing calls made. Data is held at individual call level and accumulates over time, resulting in a fairly large record count (currently around 12k records). Each call is flagged as either a "business" or a "personal" call.

       

      The report I am trying to create needs to include only those records that lie between a pair of dates, these to be input by the user at run-time. The output needs to be grouped into "business" & "personal" calls, with sub-totals for each grouping and a grand total at the bottom.

       

      The (sub)summary figures needed are: a count of the number of calls made, and the sum of the call costs, but only for calls within the specified time period.

       

      Relevant table & field details as follows:

       

      Table: PHONE_CALLS (about 12k records, but will get much bigger over time)

      Fields: CallDate (user to select records on this, e.g. 01/01/2009 to 31/01/2009)

                PhoneNumber

                CallCost

                BusinessOrPrivate  (report to group on this field)

       

      I know how to make the output split into groups and list the record lines within each group, but I haven't dicovered how to provide the sub-totals and grand total needed.

       

      This is familiar (and very easy) stuff to do in DataEase, but I'm left scratching my head a bit with FileMaker at the moment. Any help would be much appreciated.

       

       

        • 1. Re: Totals & Sub Totals in Grouped Reports
          TSGal

          MikeTV:

           

          Thank you for your post.

           

          In your table, define a new field of type "Summary".  More specifically, pull down the File menu and select "Manage -> Database..."  Click on the Fields tab, create a new field "Total CallCost", and to the right, next to "Type: ", select Summary.  Click "Create", and a new dialog box appears.  Click the first option on the left side, "Total of" and then select the "CallCost" field.  Click OK, and you will see a large total.  FileMaker Pro is actually calculating the value for the entire found set of records.  If you find a subset of records, the value will change.  If you place this field in a sub-summary part, the total will be reflected for that group of records.

           

          Following the same steps above, create another Summary field titled "Total Calls" and instead of clicking on "Total of", select the third option "Count of".  Again, the same rules apply for the found set, and for each sub-summary.

           

          If you need clarification for any of the above steps, please contact me.

           

          TSGal

          FileMaker, Inc.