Running Total Quandry

[patryn150]

I'm new here, so go easy on me (yes, some of the formulas are a bit bulky, still learning)

I found a thread on here that helped me get an alternate way of accumulating a running total since the actual RT would not accept my formulas as a way to summarize. What I'm having a problem with now is the first instance provides me no data (my numbering scheme is a bit weird, hope you can follow it )

Group Header2:
-{@adherence}
--whileprintingrecords;
--numbervar adherence;
--adherence:=0

Details:
-{@adherence1}
--whileprintingrecords;
--numbervar adherence;
--adherence:=adherence + {@adherence3} + {@adherence4} + {@adherence5}

{@adherence3} through {@adherence5} are conditional time differences returning a number.


Group Footer2:
-{@adherence2}
--if {@Scheduled Work}=time(0,0,0) then 100
--else if {@Deviation}>{@HoursSum} then 0
--else ((((hour({@HoursSum})*60*60) + (minute ({@HoursSum})*60) + (second({@HoursSum})))-((hour({@Deviation})*60*60) + (minute ({@Deviation})*60) + (second({@Deviation}))))/
((hour({@HoursSum})*60*60) + (minute ({@HoursSum})*60) + (second({@HoursSum}))))*100

The problem I have is not the fact that it doesn't summarize, that part works almost flawlessly. The problem that is arising is the fact that the absolute first entry on the report returns no data. I've tried using previousisnull for a login reference, I've tried using previous ({login})<>{login} as well, no dice. It simply will not summarize that number. An example of how it should look:

16:00:00 (Scheudled Sign In)
14:59:00 (Actual Sign In)
18:44:00 (Sign Out Break)
61.00 (Difference between Schedule Sign In and Actual Sign In, returns Absolute value)
19:00:00 (Return Break)
1.00 (Difference between Log Out and Log In minus 15 minutes for scheduled Break Length)

Running Total 62.00

What It actually shows:

16:00:00 (Scheudled Sign In)
14:59:00 (Actual Sign In)
18:44:00 (Sign Out Break)
(Difference between Schedule Sign In and Actual Sign In, returns Absolute value)
19:00:00 (Return Break)
1.00 (Difference between Log Out and Log In minus 15 minutes for scheduled Break Length)

Running Total 1.00

The running total doesn't keep that 1st number for the 1st login for the 1st rep only. Any ideas as to how to fix this?

Yes, it's scatterbrained, this is my first "major" report I'm building, I just happened to pick something that was really far out of my league to start with.

Thanks!

 

[RobbieB]

The first problem I see is you don't reference your variable "adherence" in group footer 2. You should be declaring "adherence" in your third formula, using it in your if then statement to display your outcome.

whileprintingrecords;
numbervar adherence;
if {@Scheduled Work}=time(0,0,0) then adherence := 100
else if {@Deviation}>{@HoursSum} then adherence := 0
else adherence := ((((hour({@HoursSum})*60*60) + (minute ({@HoursSum})*60) + (second({@HoursSum})))-((hour({@Deviation})*60*60) + (minute ({@Deviation})*60) + (second({@Deviation}))))/
((hour({@HoursSum})*60*60) + (minute ({@HoursSum})*60) + (second({@HoursSum}))))*100

 

[lbass]

I think the problem is in one or more of your nested formulas where you are using the previous function, so you need to display the contents of them--in fact you should always display the contents of all referenced formulas.

-LB

 

[patryn150]

Robbie -

Sorry for the delay. I do have that function in there, I just forgot to copy it over when I was cutting and pasting my formulas.

lbass -

The only time I use a previous command is to reference whether the previous login was a null value (aka, that person was the first to log in), or the previous didn't match this one (there's another person before them in the list, but it's not this one).

One thing I don't think I touched on is that this is in the details portion only. Everything else shows up exactly how I want it to except this one entry (which can affect my report for that rep by 12-15% easily), all other details are perfect, just this one is boggling me.

Thanks for the input thus far!

 

[lbass]

Please show the contents of your formulas if you would like any further help.

-LB

 

[patryn150]

This is the formula I'm having trouble with currently (as detailed above):

Details:
-{@adherence1}
--whileprintingrecords;
--numbervar adherence;
--adherence:=adherence + {@adherence3} + {@adherence4} + {@adherence5}

The references to adherence3 - adherence 5 are as such:

{@adherence3}
-if {@BreakCount} = time(0,0,0) then 0
-else if {@BreakCount}>{@TimeConversion2} then ({@BreakCount}-{@TimeConversion2})/60
- if {@BreakCount}<{@TimeConversion2} then ({@TimeConversion2}-{@BreakCount})/60

{@adherence4}
-if {@BreakCount2} = time(0,0,0) then 0
-else if {@BreakCount2}>{@TimeConversion} then ({@BreakCount2}-{@TimeConversion})/60
-else if {@BreakCount2}<{@TimeConversion} then ({@TimeConversion}-{@BreakCount2})/60

{@adherence5} (This one is fairly detailed due to conditionals)
-if (nextIsNull ({VW_EVT_AGENT_PERF.EAP_STAFF}) or
next({VW_EVT_AGENT_PERF.EAP_STAFF}) <> {VW_EVT_AGENT_PERF.EAP_STAFF})
then
0
-else
if Previous ({VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME}) <> {VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME} and {VW_EVT_AGENT_PERF.EAP_SIGN_OUT_REASON_TEXT}="BREAK"
then
abs(((RIDTSToSeconds (next({VW_EVT_AGENT_PERF.EAP_SIGN_in_TIME})) -
RIDTSToSeconds (({VW_EVT_AGENT_PERF.EAP_SIGN_out_TIME})))/60)-15)

-else
if Previous ({VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME}) <> {VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME} and {VW_EVT_AGENT_PERF.EAP_SIGN_OUT_REASON_TEXT}="LUNCH" and {@Lunch Length2}=time(5,0,0)
then
abs(((RIDTSToSeconds (next({VW_EVT_AGENT_PERF.EAP_SIGN_in_TIME})) -
RIDTSToSeconds (({VW_EVT_AGENT_PERF.EAP_SIGN_out_TIME})))/60)-300)

-else
if Previous ({VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME}) <> {VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME} and {VW_EVT_AGENT_PERF.EAP_SIGN_OUT_REASON_TEXT}="LUNCH" and {@Lunch Length2}=time(4,0,0)
then
abs(((RIDTSToSeconds (next({VW_EVT_AGENT_PERF.EAP_SIGN_in_TIME})) -
RIDTSToSeconds (({VW_EVT_AGENT_PERF.EAP_SIGN_out_TIME})))/60)-240)
-else
if Previous ({VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME}) <> {VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME} and {VW_EVT_AGENT_PERF.EAP_SIGN_OUT_REASON_TEXT}="LUNCH" and {@Lunch Length2}=time(3,0,0)
then
abs(((RIDTSToSeconds (next({VW_EVT_AGENT_PERF.EAP_SIGN_in_TIME})) -
RIDTSToSeconds (({VW_EVT_AGENT_PERF.EAP_SIGN_out_TIME})))/60)-180)
-else
if Previous ({VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME}) <> {VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME} and {VW_EVT_AGENT_PERF.EAP_SIGN_OUT_REASON_TEXT}="LUNCH" and {@Lunch Length2}=time(2,0,0)
then
abs(((RIDTSToSeconds (next({VW_EVT_AGENT_PERF.EAP_SIGN_in_TIME})) -
RIDTSToSeconds (({VW_EVT_AGENT_PERF.EAP_SIGN_out_TIME})))/60)-120)
-else
if Previous ({VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME}) <> {VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME} and {VW_EVT_AGENT_PERF.EAP_SIGN_OUT_REASON_TEXT}="LUNCH" and {@Lunch Length2}=time(1,0,0)
then
abs(((RIDTSToSeconds (next({VW_EVT_AGENT_PERF.EAP_SIGN_in_TIME})) -
RIDTSToSeconds (({VW_EVT_AGENT_PERF.EAP_SIGN_out_TIME})))/60)-60)
-else
if Previous ({VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME}) <> {VW_EVT_AGENT_PERF.EAP_SIGN_IN_TIME} and {VW_EVT_AGENT_PERF.EAP_SIGN_OUT_REASON_TEXT}="LUNCH" and {@Lunch Length2}=time(0,30,0)
then
abs(((RIDTSToSeconds (next({VW_EVT_AGENT_PERF.EAP_SIGN_in_TIME})) -
RIDTSToSeconds (({VW_EVT_AGENT_PERF.EAP_SIGN_out_TIME})))/60)-30)

The {@adherence3-5} formulas work perfectly and pull exactly the information I want. It's just that first formula at the top that's giving me problems (I was trying the previous commands, I pulled them out to simplify the formula again).

Thanks!

 

[lbass]

When you use the previous function, you need to use a clause like this to address the first record issue:

if onfirstrecord then 0 //or whatever value
else
if {table.field} <> previous({table.field}) //etc.

If your formulas are picking up values from a previous group, then you would use something like:

if {table.groupfield} <> previous({table.groupfield}) then 0 else
if {table.field} <> previous({table.field}) //etc.

-LB

 

[patryn150]

Debugged it. Was an error in that hugely long Adherence5 formula that was causing it, forgot to put in a previousisnull reference thus it was returning a detail that was not summable.

Thanks for the ONFIRSTRECORD info though, didn't know about that command

Thanks!