Subreports question (possibly?)

[aftab45]

Hello All,

I've been given the task of producing a report based upon a helpdesk. Basically, I need to find out whether a a particular ticket has been acknowledged within 30 minutes. The data is currently stored as:

Ticket No. Start Date End Date Status
1 14/03/2006 12:00 14/03/2006 12:10 Assigned
1 14/03/2006 12:10 14/03/2006 12:30 WIP

I know that I need to subtract the start date of when the ticket was acknowledged from the start date of when it arrived with the team, but I need my output to look like this:

Ticket No. Failed(Y/N)
1 Y

Do I need to use a subreport and if so whats the best way to go about it?

Thanks in advance.

 

[Madawc]

Try DateDiff - it includes time, minutes and hours.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[aftab45]

Thanks, Madawc.

 

[Olvera]

If datediff("n",startdate,ackdate)=< 30 then "Yes" else "No

 

[IdoMillet]

I think the prior responders didn't realize you need the difference between the Start Dates of 2 different records.

You could use a subreport but there's a simpler way:

1. Add the same table again to the report, so you have:
"Ticket" and another instance of the same table (give it an alias of: "Ticket_WIP").

2. Join the two tables on Ticket Number but use the record selection formula in Crystal to restrict the records as follows:

{Tickets.Status} = "Assigned" AND {Tickets_WIP.Status}="WIP"

3. Now create the {@Failed} formula as:

datediff("n",{Ticket.StartDate},{Ticket_WIP.StartDate} > 30 then "Y" else "N"

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[aftab45]

Thanks, IdoMillet.