Solved

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

Posted on 2011-05-12
601 Views
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
Question by:J C

Author Comment

Figured this one out.
0

LVL 100

Accepted Solution

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 Closing Comment

Thanks
0

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

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 Comment

*created
0

LVL 100

Expert Comment

But that doesn't account for the weekends and holidays

mlmcc
0

Author Comment

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

### Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!