asked on

Crystal Report. Need to get average of number of days to complete work order

I have a report that shows me all of our vendors that have completed work orders and all of the completed work orders tied to them. I have a formula that shows me the number of days it took for them to complete the order which I have included.

I then have another formula to get the average. The problem is, it's giving me one average for all vendor's instead of the average for each. I have the records grouped by Vendor and the average in the Group Vendor area. Can you tell me what I need to do in order to get the average number of work days for each vendor versus system-wide?

Current simple formula to get average: Average ({@DaysDifference})

Thank you!

//DaysDifference...Gives me the number of days
Global DateVar Array Holidays;
Local NumberVar FullDays;
Local NumberVar BankHolidays;
Local NumberVar Weekends;
Local NumberVar i;
Local Datevar StartDate := {WOHeader.RequestDate};//put your date here;
Local DateVar EndDate := {WOHeader.ComplDate};

    (
    //Count the number of full days between dates
    Fulldays := DateDiff ('d',StartDate,EndDate);

    //Count any holidays between dates
    For i := 1 to Ubound(holidays)
    Do
        If Holidays[i] >= StartDate and Holidays[i] <= EndDate Then
            BankHolidays := BankHolidays + 1;

    //Count any weekends between dates
    Weekends := DateDiff("ww", StartDate, EndDate, crsaturday) + 
                DateDiff("ww", StartDate, EndDate, crsunday)
    );

//Calculate Working days
FullDays - Weekends - BankHolidays

Open in new window

J C

ASKER

Figured this one out.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

J C

ASKER

Thanks

Mike McCracken

Just for my own edification, which method were you able to use?

mlmcc

J C

ASKER

I create a formula AverageDaysToComplete and the formula was
DateDiff("d",{WOHeader.RequestDate},{WOHeader.ComplDate})

I clicked on Insert>Summary and selected average and chose the formula as the field and inserted it into the group section.

J C

ASKER

*created

Mike McCracken

But that doesn't account for the weekends and holidays

mlmcc

J C

ASKER

You are right...I don't know what I was thinking. I tried using the modified formula you provided but it is not an option when I go to Insert>Summary. I can use the following formula instead but it still does not return the correct numbers.

I created the formula below and went to Insert>Summary and set it to average and placed the field in the vendor group section. It is closer but still doesn't return the correct numbers. If you are willing to look at this with me again I'd appreciate it.

//DaysDifference...Gives me the number of days
WhilePrintingRecords;
Global NumberVar TotalDaysToCompletion;
Global NumberVar WorkOrderCount;
Local NumberVar DaysDiff;
Global DateVar Array Holidays;
Local NumberVar FullDays;
Local NumberVar BankHolidays;
Local NumberVar Weekends;
Local NumberVar i;
Local Datevar StartDate := {WOHeader.RequestDate};//put your date here;
Local DateVar EndDate := {WOHeader.ComplDate};

    (
    //Count the number of full days between dates
    Fulldays := DateDiff ('d',StartDate,EndDate);

    //Count any holidays between dates
    For i := 1 to Ubound(holidays)
    Do
        If Holidays[i] >= StartDate and Holidays[i] <= EndDate Then
            BankHolidays := BankHolidays + 1;

    //Count any weekends between dates
    Weekends := DateDiff("ww", StartDate, EndDate, crsaturday) + 
                DateDiff("ww", StartDate, EndDate, crsunday)
    );

//Calculate Working days
DaysDiff := FullDays - Weekends - BankHolidays;
TotalDaysToCompletion := TotalDaysToCompletion + DaysDiff;
WorkOrderCount := WorkOrderCount  + 1;
DaysDiff

Open in new window