Summaries based on dynamic groups

[dave1]

I currently have setup an employees table with a unique identifier, first and last name.

Visually

---------------------

| Employee

---------------------

| id

| first name

| last name

---------------------

It has a relation to a Time Entry table which contains an employee identifier, a date, and the number of hours worked that day

---------------------

| Time Entries

---------------------

| employee_id

| date

| hours

---------------------

Now I want to create a layout to summarize the data but at different granularities.

Eg. One report would show how many hours an employee worked each week

Another report would be the total # of hours worked each month.

Is there anyway I can do this?

Additionally, is there anyway to setup the layout horizontally?

eg.

| May | June |

---------------------------

Employee1 | 5 | 5 |

Employee2 | 6 | 5 |

Thus the header's are dynamic.

Thanks.

dave

[Genx]

Firstly horizontally... it's going to get a lot more complicated. Below is how to do it vertically.

Create a summary field in your time entries table, make it sum of hours. Additionally create a calculation field called "ct_monthyear" -- your calculation is just MonthName(Date) & " " & Year(Date)

Then for your report, base it on your Time Entries Table and create two sub summary parts (you can delete the body, we won't need it).

Sub Summary Part I: ct_monthyear ... just put the ct_monthyear field in here

Sub Summary Part II: employeeID ... put all your employee details in here along with the summary field.

Sortign: Sort by ct_monthyear and then by employeeID -- should give you your report.

For your weekly report, add another field ct_weekyear "Week " & WeekOfYear(Date) & " " & Year(Date) just duplicate the layout and change all references from ct_monthyear above to ct_weekyear

[dave1]

Thanks for the reply,

I don't have filemaker pro @ home, so I'll try tomorrow.

Also since I can't try it out I'm wondering if this is possible:

For each time entry if I added a text field describing an activity that was completed for the day. Is it possible to generate the following reports

1) Employee, and a unique list of activities they have completed

2) Employee, and a unique list of activities and a sum of the total hours spent performing htis activity

3) Employee, and a list of activies they have completed and the total hours on a per week basis etc..

- thanks, dave

[Genx]

Yes, you just have to add the body part then and input whatever fields you want appropriate to each time entry.

[dave1]

I'm having a few issues of setting up the following sub summaries

Sub Summary Part I: ct_monthyear ... just put the ct_monthyear field in here

Sub Summary Part II: employeeID ... put all your employee details in here along with the summary field.

First the ct_monthyear isn't being sorted properly

ie. December 2006 is before November 2006 (it's alphabetical)

Secondly, after I add the employee sub summary i only see one employee showing up and one grand total. Instead of a list of employees and each of their totals.

Thanks,

Dave

[dave1]

nm ... I was using the wrong employee identifier field