Crystal Reports, get the time from a specifik location

0
votes

Long time lurker, first post.

The company I work for has a parcel-tracking software that we sell to our customers. It uses SQL-database to store the data. And to create reports for the parcels devliery times and other stuff, like exceptions, costs, signatures, we use crystal reports.

One of our customers would like a SLA report, where they want to track the time between a certain location to the time where is has been delivered.

The formula must contain:

  • Package Status, in this case it's LOCSCAN
  • Package Location, it's X-ray
  • And the time that is has been scanned to that location.

I have all the data in the SQL-database, but I cant figure out the formula for this.

EDIT:

A brief description of the workflow:

  • The parcel arrives at the site. They scan it in to the system, thus gets an received date/time, location:Goods and status: RECEIVED.
  • After that they put the parcel trough a x-ray scanner, so it doesnt contain any illegal stuff. Scans the parcel with the new status: LOCSCAN, location:X-ray and a new date/time.
  • After that, they deliver the parcel, to the receiver, and gets status: DELIVERED with a new date/time.
  • All the date/time fields are seperated from eachother, stored in individual columns, dependent on the status of the parcel.

Im using Crystal Reports, where im trying to create the formula.

1
crystal-reports
Hello. You well described the context, but what is actually the problem ? If I understand you have some X-Ray locations associated with some times in the DB. It looks you are trying to make a Delta of this time for some of the X-Ray. First question is where to you want to make the computation ? in the DB via a stored procedure or in Crystal Report ? – sandwood
Hi @sandwood. Thanks for your reply. Im trying to do this in Crystal. I will edit the orignal post, to get it to make more sense. – Martin Ekman

1 Answers

0
votes

Use the datediff function:

datediff('d', {startingdate}, {endingdate})

First a formula for RECEIVED to LOCSCAN and then LOCSCAN to DELIVERED if I'm understanding correctly.

The 'd' stands for days, you can replace with 'h' for hours or 's' for seconds. The fields between {} are the ones from database.