First At Job Visit 1

[Ox73]

I have Crystal version 10. I am trying to summarize to get a percentage but I am unable because each formula that I have created ends up doing the same thing. Once I do get the value I want in the group footer and then try to add it it says that I can not summarize field.

I currently have 4 groups:
Group by work order
At Job DateTime by each day
At Job DateTime by each second
Appt Start Date Datetime

If I place this formula at the footer of the Work Order it gives me the values I want....

***********This whole thing is MinAtJob****************
Global DateTimeVar MinDailyAtJob;
Global DateTimeVar AllAtJob;
Global DateTimeVar ApptStart;
Global DateTimeVar FinalDate;

AllAtJob := minimum ({DBTABLE.AT_JOB},{DBTABLE.WORK_ORDER});
ApptStart := {DBTABLE.APPT_START};
MinDailyAtJob := Minimum ({DBTABLE.AT_JOB},{DBTABLE.AT_JOB}, "daily");

FinalDate :=
(if date(ApptStart) = date(AllOnSite) then AllAtJob else
if date(ApptStart) < date(MinDailyAtJob) then MinDailyAtJob else
if MinDailyAtJob > ApptStartDate then MinDailyOnSite else
if date(ApptStart) > date(MinDailyAtJob) then AllAtJob)
*****************************************************

However if I want to evaluate the last value and say count the missed appointments based on the last value evaluated it says that "field can not be summarized"....

I want to add the missed appoitments
*************field countmissed appointments********
{@MinAtJob} < {DBTABLE.APP_START}
or {@MinAtJob} > {DBTABLE.APP_END}
then 1
else 0
****************************************************

************SumMissed*******************
sum(countmissed)
*****************************************

Based on the formula I want to evaluate the date after the starting appointment date Scheduled
and obtain the first At Job Record after the appointment made. If the appointment date is less
than any of the given At job records then just evalute the first at job record.


So based on these examples I would have 3 made appoitnments and 1 missed as follows:

Work Order#
00123
06523
00654
56879

***00123
Appt Start-End Time
3/6/08 8:00 am - 12:00 pm

At Job Record(s) this is how many times we visited
01/21/2008 6:05:55 PM
01/22/2008 1:34:35 PM
03/06/2008 8:51:06 AM

Value wanted based on formula
03/06/2008 8:51:06 AM

***06523
Appt Start-End Time
3/10/08 9:00 am - 12:00 pm

At Job Record(s) this is how many times we visited
03/10/2008 11:09:04 AM
03/10/2008 12:11:12 PM

Value wanted based on formula
03/10/2008 11:09:04 AM

***00654

Appt Start-End Time
3/25/08 8:00 am - 12:00 pm

At Job Record(s) this is how many times we visited
03/25/2008 9:06:43 AM

Value wanted based on formula
03/25/2008 9:06:43 AM

***56879

Appt Start-End Time
4/2/08 12:00 pm - 4:00 pm

At Job Record(s) this is how many times we visited
03/27/2008 1:05:29 PM
03/28/2008 8:55:28 AM

Value wanted based on formula
03/27/2008 1:05:29 PM

Any ideas how I can evaluate the value that I want so I can summarize it?

 

[lbass]

Create another variable to place in the WorkOrder footer:

whileprintingrecords;
numbervar cntmissed;
if {@MinAtJob} < {DBTABLE.APP_START}
or {@MinAtJob} > {DBTABLE.APP_END} then
cntmissed := cntmissed + 1;

Then in the report footer use this display formula:
whileprintingrecords;
numbervar cntmissed;

-LB