1 Reply Latest reply on May 18, 2014 1:03 PM by philmodjunk

    How can I filter and report this... ?



      How can I filter and report this... ?



           I'm pretty new to FM but have been successful with some basic DB already. 

           Now - I really struggle to find out how... 



      •           For our company I would like to see a reporting that allows me to filter / report the following: 
      •           For about 100 employees: 
      •           Total contracted hours / week:  per DEPARTMENT (Main category) but also for the sub-category of ROLE within a department.  
      •           Every dep. has various ROLES (Rolle in my screenshot). 



      •           We do see employees moving from one department to the other quite often. With that they do change their current role and also weekly contracted hours.  This is due to promotions, seasonality, exit and new starters.  
      •           We have to follow strict quarterly budgets per department (we can joggle a bit with how we spend these hours to the roles within the department)
      •           e.g:  An employee could work as a Associate (Verkäufer) in January on a 16hrs contract but could then move to the LOGISTICS department next month to a new role and even with a new contracted hours. 



      •           1 FIELD for employee name
      •           1 filed per month per  employee ROLE (role_Jan.... role_Feb... )
      •           1 field per month per employee DEPARTMENT (dep_Jan... del_Feb...)
      •           1 field per month per employee HOURS (hour_jan... hour_feb...)
      •           SEE SCREENSHOT
      •           Text fields for ROLE and DEPARTEMENT are being filled over a drop-down-list
      •           in one dataset I input data for all year per employee.  This is giving us a good overview if we search for the employee. 





      •           I can't figure out what and how I can report the totals per month for: 
      •           TOTAL HOURS per Department / month
      •           TOTAL HOURS per Role / month
      •           Ideally I would also get a Headcount
      •           and ideally I could filter select which department and which month I want to see or report. 


           Any tip is very much appreciated - thanks a lot

           best regards from Switzerland / Europe



        • 1. Re: How can I filter and report this... ?

               It looks like you are trying to use a single table where you actually need several related tables:


               Employees::__pkEmployeeID = HoursWorked::_fkEmployeeID
               Departments::__pkDepartmentID = HoursWorked::_DepartmentID

               Table: Employees--one record for each employee

               __pkEmployeeID (Primary Key--usually an auto-entered serial number)
               EmployeeName ( text )
               CurrentRole (Text )

               Table::HoursWorked--One record for the hours worked by one employee during one day in one role

               __fkEmployeeID (Number)
               Date                   (Date)
               cMonth               (Calculation: Date - Day ( Date ) + 1 ; ResultType: Date )
               Role                    (Text; Looked up value copied from Employee::CurrentRole )
               HoursWorked     (Number)
               sTotalHours        (Summary, total of HoursWorked)
               _fkDepartmentID (Number)

               Table: Department--one record for each department

               __pkDepartmentID   (Serial number)
               DepartmentName     (Text)

               With this structure to your data, you can set up a summary report with sub summary layout parts and sorting that groups records by department and role. Role is copied from the employee table so that an employee can be given a new role without altering what role was recorded for work prior to that change.

               Here's a tutorial on Summary Reports:Creating Filemaker Pro summary reports--Tutorial

               (And while I am using a number field in this example for HoursWorked, it's possible to make it a calculation field that computes time worked from time fields that record when an employee started and stopped working.)