Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 627

# 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;

(
//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
0
J C
• 5
• 3
1 Solution

Author Commented:
Figured this one out.
0

Commented:
I don't see anything in the formula that would preclude using a summary on the it.

If that doesn't work try this

WhilePrintingRecords;
Global NumberVar TotalDaysToCompletion;
Global NumberVar WorkOrderCount;
""

Modify the DaysDifference formula as shown below

mlmcc
//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;

(
//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
0

Author Commented:
Thanks
0

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

mlmcc
0

Author Commented:
I create a formula AverageDaysToComplete and the formula was

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

Author Commented:
*created
0

Commented:
But that doesn't account for the weekends and holidays

mlmcc
0

Author Commented:
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;

(
//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
0

## Featured Post

• 5
• 3
Tackle projects and never again get stuck behind a technical roadblock.