Crystal Reports Calculating Downtime from a list of WO's

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Calculating Downtime from a list of WO's

Author

Message

dmaenle
Newbie
Newbie

Joined: 18 Apr 2009
Online Status: Offline
Posts: 3

Topic: Calculating Downtime from a list of WO's
Posted: 18 Apr 2009 at 6:45am

I am trying to develop a downtime report that will look at a list of work orders and will consider concurrent workorders as one downtime instance. The work orders are grouped by equipment ID, and ordered by ReportDate in a details section.

Can I give each WO a consecutive number starting with 1 for each group?

If so, can I write a formula that looks to see if the 2nd WO in the list has a report date between the 1st WO's ReportDate and finishdate?

If so, can I write a formula that looks to see if the 3rd WO in the list has a report date between the 1st WO's ReportDate and latest finishdate for the previous WO's?

If I can do this, I think I can set up some if statements to look at concurrent WO's as one downtime instance.

Is there a better way to do this?

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 20 Apr 2009 at 6:12am

can you post sample data that exists per row? It is hard to determine a process without knowing what you have to work with.

IP Logged

dmaenle
Newbie
Newbie

Joined: 18 Apr 2009
Online Status: Offline
Posts: 3

Posted: 21 Apr 2009 at 11:53am

Thanks, for looking at this

Seq#	WONUM	EQNUM	REPDATE	STARTDATE	ACTFINISH	Response	RepTime	WorkTime	MasterWonum	WonumGroup	Last Finish	First Start	Work Time
1	12546	17595	1/5/09 7:00 AM	1/5/09 7:10 AM	1/5/09 8:10 AM	0.2	1.2	1.0	12546	12546
2	12548	17595	1/5/09 8:00 AM	1/5/09 8:10 AM	1/5/09 9:30 AM	0.2	1.5	1.3	0	12546
3	12555	17595	1/5/09 8:00 AM	1/5/09 8:05 AM	1/5/09 9:30 AM	0.1	1.5	1.4	0	12546	1/5/09 9:30 AM	1/5/09 7:00 AM	2.5
4	12596	17595	1/5/09 10:00 AM	1/5/09 10:10 AM	1/5/09 11:00 AM	0.2	1.0	0.8	12596	12596	1/5/09 11:00 AM	1/5/09 10:00 AM	1
5	12601	17595	1/5/09 12:00 PM	1/5/09 12:02 PM	1/5/09 2:10 PM	0.0	2.2	2.1	12601	12601
6	12603	17595	1/5/09 12:00 PM	1/5/09 12:10 PM	1/5/09 2:10 PM	0.2	2.2	2.0	0	12601	1/5/09 2:10 PM	1/5/09 12:00 PM	2.166667

						# Failures	MTTR
						3	1.888889

I through this together in Excel. The cells with blue labels would be calculated. The MasterWonum and WonumGroup cells use an if statement looking at the REPDATE and ACTFINISH of the previouos record.

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 21 Apr 2009 at 1:12pm

Well your response times, rep times and work times are pretty straignt forward using a datediff with minutes /60 for your hour format.

The next items are obviously the problem. Do you have the option of making a stored procedure or view "create" this data outside of crystal? If so you could group on this and make it easier.

I know you can get record counts where the changes occur using a running total. Just reset the RT as a formula: {table.REFDATE} in previous({table.REFDATE}) to previous({table.ACTFINISH})

Anyone else want to take a stab at variables to create the these last coulmns of data?

IP Logged

dmaenle
Newbie
Newbie

Joined: 18 Apr 2009
Online Status: Offline
Posts: 3

Posted: 21 Apr 2009 at 4:18pm

More info on how I developed the Excel data:

I put the Seq # in by hand (Column A). The MasterWonum formula in Excel is =IF(A2=1,B2,IF(D2 <=F1, 0, B2)). The WonumGroup formula was =IF(A2=1,B2,IF(D2 <=F1, K1, B2)).

I could easily accomplish what I am looking for if I could group on WonumGroup after calculating them. I can't think how to do this because I need the Seq# for my if statement, right? Would a sub-report be a possibility?

Another option I am considering would be to write a report locating concurrent WO's on a given EQNUM. We would then have to update a "Belongs to" field with the WonumGroup value by hand for all but the MasterWonum. My analysis report could then accurately count the Downtime occurances (set select expert to isnull({belongs to})), but I would not capture Repair Time accurately.

I think I'm getting dizzy.....

IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum