Attendance Report

[Former Member]

Hi,

Please help me to sort out the following criteria.

Table: [InOut field, 0-CheckIn, 1-CheckOut]

EmpID	EmpName	InOut	_Year	_Month	_Day	_Hour	_Minute
7001	John	0	2016	05	24	08	30
7001	John	1	2016	05	24	17	30
7002	Mathew	0	2016	05	24	08	25
7002	Mathew	1	2016	05	24	17	40

Report Output Expected:

Report - 1 [All Employees on a particular date]

DATE: 24-05-2016

Sl No	EmpID	EmpName	IN Time	Out Time
1	7001	John	08:30	17:30
2	7002	Mathew	08:25	17:40

Report - 2 [Individual Employee Wise]

NAME: John   [more data in table...]

Sl No	Date	IN Time	OUT Time
1	24-05-2016	08:30	17:30
2	25-05-2016	08:10	17:15
3	26-05-2016	08:00	17:25

Please help

Thanks in Advance

Arun Madhav

[abhilash_kumar · Accepted solution]

Hi Arun,

Do you need two separate reports or all of this on a single report?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I am looking for 2 separate reports..

Thanks

[abhilash_kumar · Accepted solution]

For Report #1, try this:

(I assume the report returns data for a particular date only)

1) Create a formula called '@InOut' with this code:

If {InOut_field} = 0 then 'In Time'

else if {InOut_field} = 1 then 'Out Time'

2) Create another formula called '@Day' with this code:

Date({year}, {Month}, {Day})

3) Create another formula called @Time' with this code:

Time({Hour}, {Minute})

Insert a Crosstab and place it on the Report Header:

1) Use the Employee ID and Employee Name fields as the 'Row'.

2) Use the @InOut formula field as the Column

3) Use the @Time field as the 'Summarized fields' with the 'Max' function.

For Report #2:

1) Re-create formulas 1 through 3.

2) Insert a Group on the Employee ID/Name field

3) Place the Crosstab on the Group Header

4) Use the Date field as the Row

5) Use the @InOut formula field as the Column

6) Use the @Time field as the 'Summarized fields' with the 'Max' function.

-Abhilash

[ido_millet · Accepted solution]

Another approach is to bring the table into the report twice as IN and OUT aliases.
Join on date and employee ID.
Use record selection criteria to restrict:
IN.InOut = 0 AND OUT.InOut = 1

The rest is easy...

[abhilash_kumar · Accepted solution]

Absolutely! I would have done that too .

-Abhilash

[Former Member · Accepted solution]

Thank you very much Mr.Millet. I brought the same view 2 times as alias and got result..

[Former Member · Accepted solution]

Hi Abhilash,

Thank you very much...

I understand the the method you mentioned.. but couldn't reach to the final output.. So I just followed Mr. Millet's method.

In fact I tried and got output with another method... I create two tables, one for In and another for out...then did the balance in application level. Then made a view connecting both the tables and got result.

Regards

[former_member11696]

Moved to Report Design forum