1 Reply Latest reply on May 18, 2011 8:25 AM by philmodjunk

    Creating a Report Problem...



      Creating a Report Problem...



      I have a table with data for operations that were done over a period of a year.  FM can create a report of the dates upon which the operations were performed but I would like to know how to:

      (1)  Get FM to report the total number of operations by each day of the week for the duration of the year;

      (2) Instead of listing each date individually, get FM to list the total number on any date for all date for the duration of the year...

      How do I go about doing this?


        • 1. Re: Creating a Report Problem...

          In both cases, you can use a special type of summary report layout that has a sub summary part, but no body part. With this layout setup, the individual records for each operation are not listed, only the sub totals.

          To group your operations totals by week, add a calculation field that computes the date for Sunday of the same week from every date:

          OperationDate - DayOfWeek ( OperationDate ) + 1

          You can then set up your sub summary report part to sort by this field and then you can put a summary field that uses the "count of" option to count records (Specify a field to "count" that is never blank) inside this sub summary part. To pull up a report, you do a find for the dates you want and then sort the records by this new calculation field.

          For your second report, set up the same type of layout, but set your sub summary part to sort by the operation date field instead of the new calculation field I described.

          You can even put both sub summary parts on the same layout. You can control which subtotals appear by how you sort your records. Sort them by the Week calculation field, the first sub summary part appears and you get weekly totals. Sort them by the date field and the second sub summary part appears and you get daily totals. Sort by both fields, (week, then operation date) and you get both sub totals in your report.