What type of relationship do I need between the tables to account for mid-quarter changes?
You need the join table and it would link to Employees by employee ID and to Departments by Department ID.
Your columnar report would be a something called a "cross tab" report--something that you can research here and in other forums. There are multiple methods for producing such a report. A series of one row (often filtered) portals in a list view is often used in such a report. I will note that you haven't indicated what one row of your report will represent. One department or one Employee? If one Employee, what would you want to see for employees that worked in more than one Department in the same specified time interval?
I will note that you haven't indicated what one row of your report will represent. One department or one Employee? If one Employee, what would you want to see for employees that worked in more than one Department in the same specified time interval?
I envision this as a sub-summary type report with a section for each department and individual employees indented beneath each department showing the employee's name and compensation for the time period. If the employee's compensation increased mid-cycle, the total compensation should reflect the change for the portion of the period in which it was changed.
If an employee changed departments mid quarter, their name and compensation should appear in both departments with the correct compensation showing for each. For example, assume an employee earns $2,000 per week. This person transferred from Customer Support to Sales effective week 7 of a 13 week quarter. The report would show 6 weeks ($12,000) in Customer Support and 7 weeks ($14,000) in Sales for the Quarter. Thereafter, and until the next change, that employee would appear in the Sales department only.
As you said, the join table would link Departments to Employees by DeptID and EmpID. I'm assuming I would create a global g_DEPTS_startdate to hold the start date for the report. This would be converted to a Quarter value (Q1-2014, etc)
To create the report, I think the Join table would also have to include an EffectiveDate and two additional fields. CompRate and DeptID. What would the relationship between DEPTS and JOIN be to calculate the cost properly? Using my example above, assume John Doe moves from Customer Support to Sales effective Feb 9, 2015 (week 7). Customer Support should show 6 weeks of John Doe and Sales should show 7 weeks for Q1 - 2015.
Thanks for your help.
What you describe sounds like a summary report layout based on the Join table with a self join relationship to another occurrence of the join table in order to set up the filtered portals.
Employee_Department::_fkEmployeeID = Employee_Department|SameEmpDept::_fkEmployeeID AND
Employee_Department::_fkDepartmentID = Employee_Department|SameEmpDept::_fkDepartmentID
A one row portal to Employee_Department|SameEmpDept can be filtered to only show a record if the range of effective date values put's in the the time interval (year or quarter) specified for that column.
To get department sub totals, you would use this relationship:
and match only by department ID. A one row portal to this TO can use the same basic filter, but summary fields defined in the Join table can be put in the portal row to show Totals, Averages, Max, Min... type aggrgate values for all employees in that department who were working for it during the specified time interval.
Just a quick Thank You for your fast and detailed reply. I haven't tried it yet but I sure do appreciate your taking the time to reply in such detail.
Cross tab reports are a bit of a challenge as there are a lot of "moving parts" that all have to be just so to get the needed results. Take it one step at a time. If you can get one column of data displaying the way you want it, you can the make copies of that portal and just edit the portal filter expression to get date for a different time period.