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
  • Last Modified:

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

0
J C
Asked:
J C
  • 5
  • 3
1 Solution
 
J CAuthor Commented:
Figured this one out.
0
 
mlmccCommented:
I don't see anything in the formula that would preclude using a summary on the it.

If that doesn't work try this

In the report header add a formula
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;
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

0
 
J CAuthor Commented:
Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

mlmcc
0
 
J CAuthor Commented:
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.
0
 
J CAuthor Commented:
*created
0
 
mlmccCommented:
But that doesn't account for the weekends and holidays

mlmcc
0
 
J CAuthor 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;
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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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