6 Replies Latest reply on Mar 11, 2014 11:45 AM by philmodjunk

    Reporting Challenge...



      Reporting Challenge...



           I am working on a solution for a company that wants to track their use of utilities during a given period, which the measure every day.  This used to be a paper based challenge, with manual entry to a spreadsheet that kicked out final values at the end of a given time period. 

           I feel like this should be an easy thing to accomplish, however, I am running into difficulty conceptualizing the final "report".  I have the table set up to take individual readings of different utility types as new records.  However, to be able to show, on a printed page, the difference between one record and another over a time period seems, well, like there are a million ways to do it, but none feel "right".  

           Does anyone have some guidance as to a good way to approach that problem?

           FTR: we are on FM 13. The table has a _PKUtilityID, UtilityType, UtilityMeasure, Date(timestamp), Day(calculated), Week(calculated), SumOfUtility(Summary of UtilityMeasure), and CountofUtility(CountNumber of records)


        • 1. Re: Reporting Challenge...

               From your description it sounds like they are doing a derivative of this with paper and a spreadsheet.  Can you attach a copy of what they currently use?

          • 2. Re: Reporting Challenge...
            /files/4d1482a944/Screen_Shot_2014-03-10_at_11.04.04_AM.png 1613x239
            • 3. Re: Reporting Challenge...
              /files/b11b035024/Screen_Shot_2014-03-10_at_11.04.39_AM.png 1627x506
              • 4. Re: Reporting Challenge...

                     The examples you show are typical of what is called a "cross tab" report as you have data from different records and sets of records shown in different columns of the same report.

                     In FileMaker, this is often produced either with calculation fields using ExecuteSQL or with one row filtered portals using summary fields from the related table to show aggregate values such as totals and averages. For the top example, your relationship could match to records for a specific equipment item for a specific week. The different portals producing your columns of data can then filter for a specific day of the week.

                • 5. Re: Reporting Challenge...

                       this has given me a good idea and has gotten things up and running.  however, i have one final question.  

                       I want to show the difference between "monday"'s record and "tuesday"s record, and then the difference between wednesday and tuesday, and so on. with the individual portals, i can't pull the difference between dates on this layout.


                  • 6. Re: Reporting Challenge...

                         By adding additional relationships and calculation field based match fields, it should be possible to link to a record for Monday of a given week and another to a week of Tuesday of a given week. Then you can refer to them in a calculation that computes the difference.