2 Replies Latest reply on Jul 21, 2010 7:08 AM by Melinda

    Summary report trouble



      Summary report trouble


      I am using FM Pro 9 over a shared network, Mac OS X, still consider myself a beginner, though I have been working with FM for over 2 years... 

      I am trying to generate a summary report, which seems like it should be a farily smple endeavor... but I am stumped! 

      Our employees complete an .xls record of their hours worked, personal days taken, etc., which dumps beautifully into FM.  I have generated a number of reports using the data without any trouble. 

      I have been asked to create a report that summarizes personal leave earned and taken for each employee.  Each Employee Name should head a column, and each month should be represented in two rows, leave earned and leave taken, followed by the cumulative balance of leave for each employee...  here is a sample of what I am trying to do...

                                 Jane Smith    John Cash

      September Earned      2                2

      September Taken        0                1

      October Earned           2                2

      October Taken             0                1

      Balance                      4                2

      The problem is NOT with exporting data into such a report, it is getting it to drop into a layout...  The trouble I am having is generating a calculation that will get the months leave "taken" to drop into the field.

      Our employees daily activity is recorded by date, M/D/YYYY, and personal leave can be taken in .5 day increments, and they record it with a "P" in two of three activity fields (AM Activity and PM Activity).  AND, each day of the month is entered in FM as it's own record...  I normally generate reports from the list layout, but I don't want to see every record for each employee,  I just want all employees included once in one report.

      I am sure you'll have questions before you can help me, so please ask and I will clarify...  It doesn't seem like this should be that hard!  :)

      Thanks in advance for any help!!


        • 1. Re: Summary report trouble

          You've described what is commonly referred to as a "cross tab" report and such are not as easy to construct in filemaker as in some other database systems but it can be done. The trick is to get your columns of data to correctly refer to the right groups of related records and there is more than one way to set this up.

          You can probabaly set up a separate table where one record = one month of a given year and then construct relationships that link month and year of this table to month and year of your data table. Each column of data could be based on a separate relationship that includes the employee ID or possibly a filtered portal that filters by a specified employee ID for each column if you are using filemaker 11.

          • 2. Re: Summary report trouble

            Oh boy.  I was really hoping this was going to be simple!  :) 

            I'll likely be back with questions!  Thanks!!