Elapsed time calculation help

[thiagarr]

Hi,

I use CR XI with the source data read from am XML file using XML native driver. I need to create a report that needs to calculate the time elapsed in the PASS status and Fail status

Sample Data is shown below:

[data]

Timestamp Status
3/28/2007 1:00:00AM Pass
3/28/2007 2:00:00AM Pass
3/28/2007 3:00:00AM Pass
3/28/2007 4:00:00AM Pass
3/28/2007 5:00:00AM Pass
3/28/2007 6:00:00AM Pass
3/28/2007 7:00:00AM Fail
3/28/2007 8:00:00AM Fail
3/28/2007 9:00:00AM Fail
3/28/2007 9:09:00AM Pass
3/28/2007 10:00:00AM Pass
3/28/2007 11:00:00AM Pass
3/28/2007 12:00:00PM Pass
3/28/2007 1:00:00PM Fail
3/28/2007 1:43:00PM Pass
3/28/2007 2:00:00PM Pass
3/28/2007 3:00:00PM Pass


[/data]

For example, to start with, the PASS status existed for 6 hours before the Fail status which lasted for 2 hrs and 9 minutes. Then the PASS status is on for 3 hrs and 51 mts, Fail for the next 43 mts and Pass for 1 hr and 17 mts. The report also needs to summarize the total Fail and Pass periods and also the Fail period as a percentage of Total period. This also needs to be summarized for each node. Once the data is grouped by node, I think the grouping can take care of the values within that node. The data here shows only one node information. The Pass period is from Min(Pass time) to min(next fail time).

I looked at running totals and started with a formula @timedifference that calculates the time elapsed between two consecutive entries. For this I had to use PREVIOUS function that does not allow the formula to be used in Running totals. The Timestamp field is a datetime variable.

I would appreciate any suggestions on how to proceed with this report.

Thank you for your help.

 

[Madawc]

This is tricky, because the successive records saying 'pass' and 'fail' can't be grouped in the way you'd want. I suspect you'll need to use a variable that accumulates the difference between each record and the previous record. When it changes from 'pass' to 'fail' or vice versa, show and clear.

If that fails, an alternative is a Stored Procedure. Within SQL, you should be able to calculate differences between pairs of records. Once the time differences are in the record data, Crystal could easily summarise them using a running total/

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

 

[thiagarr]

Madawc,

Thank you for the response.

I was also looking at different options such as variables, array variables to see if that would help.

Unfortunately with the data coming from an XML source, I am not sure if I can create a view or Stored Procedure, etc.

If anybody can think of anything that I could try, I would really appreciate all the help I can get.

Thank you.

Thanks and regards,

TR