Work Order Crystal Reports not Summing Correctly

Question: Some Work Order Costs are not totaling correctly when we run Crystal Reports. What do you recommend?

Answer: The Work Order Cost reports function properly if the report is run against the entire record set, "This Record Only", or against a filterset where the filter is only run against the main WKORDER table; however, if the filter has been set against a field in a child or grandchild table (i.e. Work Task Code or Employees), then the report may have problems. These problems occur because a different filter string is being passed to Crystal Reports.

• For example, below you can see a WO Filter on Work Task Code where WT_TASK_CD = '01'. This filter returns a unique set of WO_IDs.

  

• This same filter in the Report SQL format (the filter string that is passed to Crystal Reports) looks like this:

  

• Notice that the Crystal Reports string does not return a unique set of WO_IDs. Therefore, if there were three child tasks on one work order that all contained the task '01', then Crystal Reports would see the same WO_ID three times (and thus have three costs).

Recommendations: If the above problem is occurring in your Crystal Reports, you can try one of the following options:

• Sometimes reports are run against child dialogs when they could instead be run against the main record set (i.e. end date or task). If this is the case, we always recommend running the report against the main WKORDER table.
• If this is not an option, you could run a Filter and a Browse. Then, export the WO_NUMBER field and load it into a new 'In List' Filter (provided that the result is not too long). This should return a unique set of WOs.
• Alternatively, you could run the Filter and then perform a Global Edit on one of the Logical (checkbox) fields on the Custom tabs for the filtered record set only. Then, you could run a new filter where the Logical (checkbox) Custom field is set to 'True'. You could use this new filterset to run the report. Later, you can globally change the Logical Custom field back to 'False' so as not to interfere with your data.