[Qs] Sorting & Grouping Report

[janet0211]

I want to make a report like as attachment.

how to group the branch code and calculate the hour & $. Thanks for your help.

[comment]

Your question is not clear. Do you want your report to be summarized by Staff, then by Branch_Code? If so, why is there a Branch_Code field in your Staff table, too?

Also please explain what fields do you have, and in which table, from which one would calculate the hours and the $.

[janet0211]

Thanks for your kind attention.

- attendance table have staff_code, branch_code, and working hour

- just want to group attendance.staff_ID then group attendance.branch_code

- base on attendance.staff_ID and attendance.branch_code to calculate each staff working hour in report

 

attendance table

date	staff_id	branch_code	working_hour
1/1/2016	A111	COD	1
2/1/2016	A111	ABA	2
3/1/2016	A111	COD	1
4/1/2016	A111	ABA	3
5/1/2016	A111	COD	1
6/1/2016	A111	COD	2
7/1/2016	A111	EXE	1
8/1/2016	A111	COD	2
9/1/2016	A111	COD	1
10/1/2016	A111	ABA	3
.	A111	COD	3
.	A111	ABA	2
.	A111	ABA	1
.	A111	ABA	2
31/1/2016	A111	EXE	3
1/1/2016	A222	ABA	1
2/1/2016	A222	ABA	2
3/1/2016	A222	EXE	2
4/1/2016	A222	ABA	3
5/1/2016	A222	EXE	4
.
.
.
.

 

Report

A111
Branch
COD	<sum COD working_hour> x hour salary $ = COD salary$
ABA	<sum ABA working_hour> x hour salary $ = ABA salary$
EXE	<sum EXE working_hour> x hour salary $ = EXE salary$
	Total Salary = COD$ + ABA$ + EXE$

 

Thanks a lot

 

[comment]

The fields that you need to have in the Attendance table:

• StaffID
• Branch_Code
• Hours_Worked (Number)
• Hourly_Rate (Number)

• cWage (Calculation = Hours_Worked * Hourly_Rate)
• sTotal_Hours (Summary, Total of HoursWorked)
• sTotal_Wage (Summary, Total of cWage)

Once you have that in place, produce your report using a layout of the Attendance table, with three sub-summary parts:

1. a leading sub-summary when sorted by StaffID;
2. a leading sub-summary when sorted by Branch_Code;
3. a trailing sub-summary when sorted by StaffID;

and no body part.

Place the staff name field/s from the Staff table In the leading sub-summary by StaffID part.
Place the Branch_Code, sTotal_Hours and sTotal_Wage in the sub-summary by Branch_Code part.
Place the sTotal_Wage field in the trailing sub-summary by StaffID part.

Note:
Rates can change over time. This is why you should have an individual Hourly_Rate value for every record in the Attendance table (you would probably look this up from the related record in the Staff table). The way you have described this - calculating a sub-total of hours for each branch and multiplying it by the hourly rate - would produce incorrect result when the rate has changed during the course of the period being reported or since.

[janet0211]

That's great.

And how to do i want to add date range in the report.

(Attendance table have date field) Thanks Thanks again

[comment]

29 minutes ago, janet0211 said:

And how to do i want to add date range in the report.

Do a find for the date range. Usually that would be part of your reporting script: go to the reporting layout, find the records you want to report on, show and/or print the report, return to whatever you were doing.

Or are you asking how to display the date range? In such case, I would ask how would you specify the date range to report on?

 

Edited July 12, 2016 by comment

[janet0211]

Let me try it. I think problem solve. Thanks so much.

[janet0211]

Help expert

further Qs, I want to make a report like this:

 

tblAttendance.Branch.Main
	tblAttendance.StaffID
		tblAttendance.Branch x Hour x $
		tblAttendance.Branch x Hour x $
		tblAttendance.Branch x Hour x $
		tblAttendance.Branch x Hour x $
				Total $

 

I want to print all staff payroll slip under Main branch group, how to setting in the layout/report? Thanks Thanks Expert

Edited July 13, 2016 by janet0211

[comment]

I am not sure I understand this correctly. It looks like you have a sub-summary by Branch_Code, followed by a  sub-summary by StaffID. But what are the 4 "tblAttendance.Branch x Hour x $" items?

[janet0211]

Thanks for your time.

I am so stupid that i misunderstand your answer, but solve it now. Please forgive me as a new user of Filemaker & really Thanks a lot.