| Author |
Message |
Monkey
Newbie
Joined: 20 Aug 2009
Location: United States
Online Status: Offline
Posts: 9
|
  
Topic: Crystal Report - Holiday transactions
Posted: 20 Aug 2009 at 7:08am |
I am entry level report writer. Our HR/Payroller need a holiday transaction report to catch employees who reported Federal Holiday on the other day not on the holiday. For example, this July 4th was on Satursday and the Federal agencies closed on Friday(July 3th) . Empolyees should record 'Federal Holiday 8 hrs' on Friday on the time card. However, a number of them recorded to Satursday (July 4th), and some others might record on next Monday, Tuesday or other work days.
We are paid in Bi-week so there are 14 days on the time card for employees to fill out their transactions. Columns are 'day'; Rows are 'Transaction' like Regular pay, overtime, Sick leave, Federal holiday...etc.
Would you give some ideas that how can I use Crystal report to pick up those employees who made mistakes on their time card?
Database: Oracle.
Time card application: webT&A
Your friendly help will be appreciated.
Monkey
Edited by Monkey - 20 Aug 2009 at 7:09am
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Aug 2009 at 8:01am |
You described your timecard but not the database that stors that data.
I am guessing that this does a row level data per day.
Since i do not know your DB architecture or dat aI have to completely guess but this is a likely scenario. You will have to tweak it to match your DB
Bring in the attendence table, join it to the employee table (to get the employee names).
Write a Select Statement to look for your data errors...
((Table.paydatefield in Date(2009,07,01) to Date(2009,02,07)
or
Table.paydatefield in Date(2009,04,01) to Date(2009,07,07)) and table.codefield = "HolidayCode")
OR
(Table.paydatefield=Date(2009,03,01)
and table.codefield <> "HolidayCode")
|
| IP Logged |
|
Monkey
Newbie
Joined: 20 Aug 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 20 Aug 2009 at 8:39am |
Very thank you DBLank. Your idea is very helpful to me.
I will try to add the Sql statement that you suggested to the report.
Regards
Ann
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Aug 2009 at 8:44am |
Just to clarify, I was suggesting using that in the Crystal Select Expert. It will filter your data down from all records to just "problem" records based on those data definitions. You can do that in SQL on the back end or in a Command but do not use it as a SQL Expression Field.
Hope that helps
|
| IP Logged |
|
Monkey
Newbie
Joined: 20 Aug 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 27 Aug 2009 at 11:53am |
Hi DBLank,
I worked for one week but I still can't get it. I felt I need one more condition but I don't know how. I set up time card test version, I made two employees who reported July 4th holiday on Monday and Satursday.
The crystal report I wrote came back all 14 days (Bi-week) records for these two employees. That was not what I want. I'm exhausted.
How can I post the time card and report to you? That might help you understand my problem.
Thank you
Monkey
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Aug 2009 at 12:14pm |
Sorry I can't get that from you but I can still try to help.
Post some sample row level data for your test subjects. You can export to excel and cut and paste it.Try to leave column header names and rows and columns that you do not need displayed but that are needed for the select statment.
Post your select statement as you have it now. I or someone else can try to fix it for you. Edited by DBlank - 27 Aug 2009 at 12:14pm
|
| IP Logged |
|
Monkey
Newbie
Joined: 20 Aug 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 27 Aug 2009 at 12:40pm |
Here is a part of report's data. Pay Period 13: June 22 - July 4, DAY0=Sunday1, Day1=Monday1...Day13=Satursday2
|
LASTNAME |
|
PAY_PERIOD |
LEAVE_YEAR |
|
DESCRIPTION |
|
DAY |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
0 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
0 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
0 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
0 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
0 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
0 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
0 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
1 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
1 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
1 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
1 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
1 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
1 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
1 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
2 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
2 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
2 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
2 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
2 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
2 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
2 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
3 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
3 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
3 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
3 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
3 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
3 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
3 |
SELECT "TA_USER"."LASTNAME", "TA_MASTER"."PAY_PERIOD", "TA_MASTER"."LEAVE_YEAR", "TA_TRANS_DAILY"."DAY", "TA_TCODE_TIP"."DESCRIPTION"
FROM (("BEPWEBTA"."TA_TRANS" "TA_TRANS" INNER JOIN "BEPWEBTA"."TA_MASTER" "TA_MASTER" ON "TA_TRANS"."TA_ID"="TA_MASTER"."TA_ID") INNER JOIN ("BEPWEBTA"."TA_TRANS_DAILY" "TA_TRANS_DAILY" INNER JOIN "BEPWEBTA"."TA_USER" "TA_USER" ON "TA_TRANS_DAILY"."EMP_ID"="TA_USER"."EMP_ID") ON ("TA_TRANS"."EMP_ID"="TA_USER"."EMP_ID") AND ("TA_MASTER"."EMP_ID"="TA_USER"."EMP_ID")) INNER JOIN "BEPWEBTA"."TA_TCODE_TIP" "TA_TCODE_TIP" ON "TA_TRANS"."TC_ID"="TA_TCODE_TIP"."TC_ID"
WHERE "TA_MASTER"."PAY_PERIOD"='13' AND "TA_MASTER"."LEAVE_YEAR"='2009' AND ("TA_TRANS_DAILY"."DAY"='0' OR "TA_TRANS_DAILY"."DAY"='1' OR "TA_TRANS_DAILY"."DAY"='10' OR "TA_TRANS_DAILY"."DAY"='11' OR "TA_TRANS_DAILY"."DAY"='13' OR "TA_TRANS_DAILY"."DAY"='2' OR "TA_TRANS_DAILY"."DAY"='3' OR "TA_TRANS_DAILY"."DAY"='4' OR "TA_TRANS_DAILY"."DAY"='5' OR "TA_TRANS_DAILY"."DAY"='6' OR "TA_TRANS_DAILY"."DAY"='7' OR "TA_TRANS_DAILY"."DAY"='8' OR "TA_TRANS_DAILY"."DAY"='9') AND "TA_TCODE_TIP"."DESCRIPTION"='Federal Holiday'
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Aug 2009 at 12:45pm |
WHERE "TA_MASTER"."PAY_PERIOD"='13' AND "TA_MASTER"."LEAVE_YEAR"='2009' AND
(
("TA_TRANS_DAILY"."DAY"<>'3' AND "TA_TCODE_TIP"."DESCRIPTION"='Federal Holiday')
OR
("TA_TRANS_DAILY"."DAY"='3' AND "TA_TCODE_TIP"."DESCRIPTION"<>'Federal Holiday')
)
Does this work for you? Edited by DBlank - 27 Aug 2009 at 12:46pm
|
| IP Logged |
|
Monkey
Newbie
Joined: 20 Aug 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 27 Aug 2009 at 1:04pm |
I updated the report but it's incorrect. DAY12 = Friday2 (July 3th) which is legal federal holiday. But t22 reported 'F. H.' on DAY13 (July 4th) and t27 reported on Monday2 (June29). Correct report should catch: t22 DAY8; t27 DAY13 to whom Payroll will notify them to correct their time card.
| LASTNAME |
|
PAY_PERIOD |
LEAVE_YEAR |
|
DESCRIPTION |
|
DAY |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
12 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
12 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
12 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
12 |
| t27 |
|
13 |
2009 |
|
Federal Holiday |
|
12 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
12 |
| t22 |
|
13 |
2009 |
|
Federal Holiday |
|
12 |
| |
|
|
|
|
|
|
|
|
SELECT "TA_USER"."LASTNAME", "TA_MASTER"."PAY_PERIOD", "TA_MASTER"."LEAVE_YEAR", "TA_TRANS_DAILY"."DAY", "TA_TCODE_TIP"."DESCRIPTION"
FROM (("BEPWEBTA"."TA_TRANS" "TA_TRANS" INNER JOIN "BEPWEBTA"."TA_MASTER" "TA_MASTER" ON "TA_TRANS"."TA_ID"="TA_MASTER"."TA_ID") INNER JOIN ("BEPWEBTA"."TA_TRANS_DAILY" "TA_TRANS_DAILY" INNER JOIN "BEPWEBTA"."TA_USER" "TA_USER" ON "TA_TRANS_DAILY"."EMP_ID"="TA_USER"."EMP_ID") ON ("TA_TRANS"."EMP_ID"="TA_USER"."EMP_ID") AND ("TA_MASTER"."EMP_ID"="TA_USER"."EMP_ID")) INNER JOIN "BEPWEBTA"."TA_TCODE_TIP" "TA_TCODE_TIP" ON "TA_TRANS"."TC_ID"="TA_TCODE_TIP"."TC_ID"
WHERE "TA_MASTER"."PAY_PERIOD"='13' AND "TA_MASTER"."LEAVE_YEAR"='2009' AND "TA_TCODE_TIP"."DESCRIPTION"='Federal Holiday' AND NOT ("TA_TRANS_DAILY"."DAY"='0' OR "TA_TRANS_DAILY"."DAY"='1' OR "TA_TRANS_DAILY"."DAY"='10' OR "TA_TRANS_DAILY"."DAY"='11' OR "TA_TRANS_DAILY"."DAY"='13' OR "TA_TRANS_DAILY"."DAY"='2' OR "TA_TRANS_DAILY"."DAY"='3' OR "TA_TRANS_DAILY"."DAY"='4' OR "TA_TRANS_DAILY"."DAY"='5' OR "TA_TRANS_DAILY"."DAY"='6' OR "TA_TRANS_DAILY"."DAY"='7' OR "TA_TRANS_DAILY"."DAY"='8' OR "TA_TRANS_DAILY"."DAY"='9')
|
| IP Logged |
|
Monkey
Newbie
Joined: 20 Aug 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 27 Aug 2009 at 1:30pm |
t27's time card:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
June |
|
|
|
|
|
|
|
|
|
June |
July |
|
|
July |
|
| |
|
21 S |
22 M |
23 T |
24 W |
25 T |
26 F |
27 S |
Wk1 |
28S |
29M |
30T |
1W |
2T |
3F |
4S |
Wk2 |
| work time |
start |
|
8:00 |
8:00 |
8:00 |
8:00 |
8:00 |
|
|
|
|
8:00 |
8:00 |
8:00 |
|
|
|
| |
end |
|
4:00 |
4:00 |
4:00 |
4:00 |
4:00 |
|
|
|
|
4:00 |
4:00 |
4:00 |
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Regular pay |
Total |
|
8 |
8 |
8 |
8 |
8 |
|
40 |
|
|
8 |
8 |
8 |
|
|
24 |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| leave time |
start |
|
|
|
|
|
|
|
|
|
8:00 |
|
|
|
8:00 |
|
|
| |
end |
|
|
|
|
|
|
|
|
|
4:00 |
|
|
|
4:00 |
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Sick leave |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
8 |
|
8 |
| Federal Holiday |
|
|
|
|
|
|
|
|
|
8 |
|
|
|
|
|
8 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Daily Total |
|
|
8 |
8 |
8 |
8 |
8 |
|
40 |
|
8 |
8 |
8 |
8 |
8 |
|
40 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| IP Logged |
|
|
|
