Need Help with timecard formulas and Header 1

[fabianor]

CR 8.5

I can email what I have. NEED HELP BADLY

I have a Crystal Report saved with data and an Excel sheet as a sample of what I want to achieve.

The report is now showing the total hours worked per week and the in and out times and dates per work week.

I can not seem to find a way to get the date header to appear above the second week. The date range from 7/4/04 to 7/10/04 appears for the first week but 7/11/04 to 7/17/04 does not appear above the hours worked for the second week.

The only other item that I would like to address is the total hours worked for the week.

Regular hours for the week are hours to up 44 hours per week and Overtime hours is the balance of Total hours over 44 hours if applicable or else Overtime hours is 0.

I really appreciate any help or guidance or changes to my report that you may provide since I do not use Crystal Report regularly but learn from examples.

Thanks again.

 

[lbass]

Try:

//{@holidayhrs} to be placed in the detail section
//(substitute your holidays for those in brackets):
if {@datein} in [date(year({@datein}),07,04),date(year({@datein}),12,25),date(year({{@datein}),01,01)] then {@hours}

//{@holiday} to be placed in the group (weekstartdate) level:
local numbervar x := sum({@holidayhrs},{@WeekStartDate});
if val(right(totext(round(x,2),2),2)) in 0 to 24 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 25 to 74 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 75 to 99 then truncate(x) + 1

Then change {@regular} to:
local numbervar x := If Sum ({@Hours}, {@WeekStartDate})<=44 then
Sum ({@Hours}, {@WeekStartDate})-{@holidayhrs} else 44 - {@holidayhrs};
if val(right(totext(round(x,2),2),2)) in 0 to 24 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 25 to 74 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 75 to 99 then truncate(x) + 1

Change {@overtime} to:
local numbervar x := If Sum ({@Hours}, {@WeekStartDate}) +
Sum({@holidayhrs},{@WeekStartDate}) > 44 then
Sum ({@Hours}, {@WeekStartDate}) +
Sum({@holidayhrs},{@WeekStartDate}) - 44;
if val(right(totext(round(x,2),2),2)) in 0 to 24 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 25 to 74 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 75 to 99 then truncate(x) + 1

To accumulate the holiday hours across groups, you would add another variable in the reset formula, the same variable and a grand total variable in the accumulation formula, and separate display formulas for each at the group level and report level.

I think this addresses the remaining issues.

-LB

 

[fabianor]

Hi LB

All is well, but for some reason the holiday hours are adding together with overtime hours.

Change {@overtime} to:
local numbervar x := If Sum ({@Hours}, {@WeekStartDate}) +
Sum({@holidayhrs},{@WeekStartDate}) > 44 then
Sum ({@Hours}, {@WeekStartDate}) +
Sum({@holidayhrs},{@WeekStartDate}) - 44;
if val(right(totext(round(x,2),2),2)) in 0 to 24 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 25 to 74 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 75 to 99 then truncate(x) + 1

Should this be removed
Sum({@holidayhrs},{@WeekStartDate}) - 44;
and just left as
Sum ({@Hours}, {@WeekStartDate})- - 44;

Orignal

{@regular}
If Sum ({@Hours}, {@WeekStartDate}, "daily")<=44 then
Sum ({@Hours}, {@WeekStartDate}, "daily") else 44

If Sum ({@Hours}, {@WeekStartDate}, "daily")>44 then
Sum ({@Hours}, {@WeekStartDate}, "daily") -44

 

[lbass]

Oops. First, you don't need to add "daily" as a condition to your date group, as this is the default. Let's change those formulas a bit to make the variable names different and to use the sum of the holiday hours (not the detail level formula) in the {@regular} formula. Try:

//{@holidayhrs} to be placed in the detail section
//(substitute your holidays for those in brackets):
if {@datein} in [date(year({@datein}),07,04),date(year({@datein}),12,25),date(year({{@datein}),01,01)] then {@hours}

//{@sumholiday} to be placed in the group (weekstartdate) level:
numbervar x := sum({@holidayhrs},{@WeekStartDate});
if val(right(totext(round(x,2),2),2)) in 0 to 24 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 25 to 74 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 75 to 99 then truncate(x) + 1

Then change {@regular} to:
numbervar y := If Sum ({@Hours}, {@WeekStartDate})<=44 then
Sum ({@Hours}, {@WeekStartDate})- sum({@holidayhrs},{@WeekStartDate}) else 44 - sum({@holidayhrs},{@WeekStartDate});
if val(right(totext(round(y,2),2),2)) in 0 to 24 then truncate else
if val(right(totext(round(y,2),2),2)) in 25 to 74 then truncate + .5 else
if val(right(totext(round(y,2),2),2)) in 75 to 99 then truncate + 1

Change {@overtime} to:
numbervar z := If Sum ({@Hours}, {@WeekStartDate}) > 44 then
Sum ({@Hours}, {@WeekStartDate}) - 44;
if val(right(totext(round(z,2),2),2)) in 0 to 24 then truncate(z) else
if val(right(totext(round(z,2),2),2)) in 25 to 74 then truncate(z) + .5 else
if val(right(totext(round(z,2),2),2)) in 75 to 99 then truncate(z) + 1

I changed the logic a little, too. This assumes that {@hours} will include holiday hours initially, and that the holiday hours are then subtracted out at the weekly group level from the regular hours.

-LB

 

[fabianor]

Hi LB

I made these changes before I got your response and it seems to be working ok.

Please just check these out and tell if they seem ok.

I changed {@regular} to:
local numbervar x := If Sum ({@Hours}, {@WeekStartDate})<=44 then
Sum ({@Hours}, {@WeekStartDate})-{@holidayhrs} else 44 - {@holidayhrs};
if val(right(totext(round(x,2),2),2)) in 0 to 12 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 13 to 37 then truncate(x) + .25 else
if val(right(totext(round(x,2),2),2)) in 38 to 62 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 63 to 87 then truncate(x) + .75 else
if val(right(totext(round(x,2),2),2)) in 88 to 99 then truncate(x) + 1


I changed {@overtime} to:
local numbervar x := If Sum ({@Hours}, {@WeekStartDate})>44 then
Sum ({@Hours}, {@WeekStartDate}) - 44;
if val(right(totext(round(x,2),2),2)) in 0 to 12 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 13 to 37 then truncate(x) + .25 else
if val(right(totext(round(x,2),2),2)) in 38 to 62 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 63 to 87 then truncate(x) + .75 else
if val(right(totext(round(x,2),2),2)) in 88 to 99 then truncate(x) + 1

I changed{@holidayhrs} to:
local numbervar x := sum({@holidayhrs},{@WeekStartDate});
if val(right(totext(round(x,2),2),2)) in 0 to 12 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 13 to 37 then truncate(x) + .25 else
if val(right(totext(round(x,2),2),2)) in 38 to 62 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 63 to 87 then truncate(x) + .75 else
if val(right(totext(round(x,2),2),2)) in 88 to 99 then truncate(x) + 1

and I changed {@hours} to:
local numbervar x := IF IsNull({TimeClock.TimeIn}) OR IsNull({TimeClock.TimeOut}) THEN
0
ELSE
(DTSToSeconds ({TimeClock.TimeOut}) - DTSToSeconds ({TimeClock.TimeIn})) / 3600.0 + ({@DateOut} - {@DateIn}) * 24;
if val(right(totext(round(x,2),2),2)) in 0 to 12 then truncate(x) else
if val(right(totext(round(x,2),2),2)) in 13 to 37 then truncate(x) + .25 else
if val(right(totext(round(x,2),2),2)) in 38 to 62 then truncate(x) + .5 else
if val(right(totext(round(x,2),2),2)) in 63 to 87 then truncate(x) + .75 else
if val(right(totext(round(x,2),2),2)) in 88 to 99 then truncate(x) + 1

This way now my detail hour section rounds hours to nearest 1/4 hour.

I have more questions.

I work with a report saved with data. Can that data be removed. When I connect to the database at work it pulls up the data range that I worked with ok. If I change the date range then my reports comes up with nothing in it.

I also have created a report without the data saved to it and it work just fine but I would like to use the report that the data was saved with because the layout looks great since I sent all my time on that one.

Thanks again.

Your help has been greatly appreciated.