Main page

Time-sheet Report

Description

This example shows how to build a Time-sheet Report.

1. The Report shows the data for the half of the month with calculation of the Overtime, more than 40 hours a week (Monday-Sunday).
2. The Calculation of the Overtime starts after 40 hours per week were exceeded. It can never happen on Monday, and usually only the 40-hour limit is reached on Thursday or Friday. Every hour after 40 hours is calculated as the Overtime.
3. The Report Starting Date is shifted on 6 day before in the Report's Before Build Select event for the Overtime calculation.
4. Working hours during a week are calculated in the Report Detail's On Calculate event. If the total is more than 40, the surplus is calculated as the Overtime.
5. We hide rows for six extra days using the Report Detail's Before Show event.

Usage

Select an employee and enter date (for example, Alex Antion and 10/15/2004) to view the Time-sheet Report.

Steps to recreate

1. Use the Report Builder to create the emp_timesReport report with the emp_Search Search Form.
   • On Step 2 click on the Build Query button, then in the dialog select the following tables employees, absence_types. Add absence_types table one more time. Then set the Left Join relation between absence_type_with_pay field and absence_type_id field of the absence_types table. After set the Left Join relation between absence_type_no_pay field and absence_type_id field of the absence_types1 table. Select all * fields from the emp_times table, and emp_name and title fields from the employees table, also the absence_type_name fields from both absence_types table.
   • On Step 3 select emp_name as a Groups field.
   • On Step 4 select Group Above.
   • On Step 5 check Sum for emp_time_total field.
   • On Step 6 select No sorting and No Page Navigator.
   • On Step 7 set the Record Per Web Page to empty and check Include   into empty cells.
   • On Step 8 check Create Search/Filter and select the emp_id and emp_time_date and select ListBox for the emp_id.
2. Add a new row into the Search Form.
3. Add the s_start_date textbox into the new row and set its Data Type property to Date. In the Format tab of the Properties Window check the Read-Only property.
4. For the s_emp_id ListBox set Connection property to IntranetDB, Data Source to employees, Bound Column property to emp_id, and text Column to emp_name.
5. For the emp_timesReport Report, open the 'Visual Query Builder' window by clicking on the [...] button next to the Data Source property
   • For the Use Default Value if parameter is empty property change the WHERE parameter as follows: emp_times.emp_id equals(=) s_emp_id to set -1.
   • For the Condition property change the WHERE parameter as follows: emp_time_date equals(=) s_emp_time_date to set less than or equal(<=).
   • Add the following WHERE parameter: emp_times.emp_time_date equals(=) s_start_date.
6. Delete emp_name_Footer section.
7. Add a new column after the 'Absence reason' column.
8. In the 'Detail' section add the emp_time_overtime Report Labels into the new cell and set its Data Type property to Float and Format to 0.00.
9. Move the title field from section detail to emp_name_Header.
10. Add two rows into the Report_Footer section.
11. Add the RegularTime label into the new row and set its Data Type property to Float and Format to 0.00.
12. Add the Overtime label into another row and set its Data Type property to Float and Format to 0.00.
13. Set the Format property to h:nn AM/PM for the following labels: emp_time_in, emp_time_out, emp_time_in2 and emp_time_out2.
14. Set Format property to 0.00 for the following labels: emp_time_total and emp_time_overtime.
15. Set Format property to #.## for the following labels: emp_time_with_pay and emp_time_no_pay.
16. Add the TotalHoursSum, OverTimeSum and WeekOvertime global variables as shown in the example.
17. Add the appropriate programming code in the events as shown in the example.
18. Make cosmetic changes as needed by modifying the text and captions within the HTML.

Database Tables Used

Database: IntranetDB

Tables: employees, emp_times, store_products, absence_types

Programming Notes

The Search Form On Validate event is used to verify the value of the s_emp_time_date field.

The DoSearch Button On click event is used to calculate the starting date of the Report.

The Report Before Build Select event is used to reduce the starting date for 6 day for the WHERE parameter. It is required for the correct Overtime calculation.

The Report Detail On Calculate event is used to calculate the detail rows for the Overtime calculation for the week and reset 'Total Hours this period' for the days less than the Starting Date.

The Report Detail Before Show event is used to hide the detail rows for the days less than the Starting Date.

The Report Footer Before Show event is used to set the values of the Regular Hours and Overtime labels.