CODEWITHSUNDEEP
crystal-reports
YetAnotherRandomUser
YetAnotherRandomUser

Reputation: 1408

How to calculate and total length of time with only timestamps?

I am working with Crystal Reports 2016. I have some datetime values that represent when something changes. I have a numeric value of what changes at the datetime value. In this case, some network connections are changing states between 3 values (0, 1, 2). I'd like to calculate the periods, and totals of the periods; then chart the ratio of the periods to each other, and the time of the report. I've gotten some of this done, but I think I took the wrong approach, because the last bit isn't working.

I have formulas for Online, Offline, and Partially Online, (respectively) like so:

If {CRV_AttributeLog.AnalogValue}=2
  Then {CRV_AttributeLog.LogTimeStamp}

If {CRV_AttributeLog.AnalogValue}=0
  Then {CRV_AttributeLog.LogTimeStamp}

If {CRV_AttributeLog.AnalogValue}=1
  Then {CRV_AttributeLog.LogTimeStamp}

I have formulas to calculate the periods between the states:

Online

abs(dateDiff("s", next({@TimeStampPartiallyOnline}), {@TimeStampOffline})) +
abs(dateDiff("s", next({@TimeStampPartiallyOnline}), {@TimeStampOnline}))

Offline

abs(dateDiff("s", next({@TimeStampOffline}), {@TimeStampOnline})) +
abs(dateDiff("s", next({@TimeStampOffline}), {@TimeStampPartiallyOnline}))

Partially Online

abs(dateDiff("s", next({@TimeStampPartiallyOnline}), {@TimeStampOffline})) +
abs(dateDiff("s", next({@TimeStampPartiallyOnline}), {@TimeStampOnline}))

I cannot make a summary of the formulas that calculate the period, presumably because I have the first set of formulas that are effectively variables; Crystal Reports does not like the nesting of formulas. I can't just copypasta replace the formula name with the if ... then expression; that gives me an error when I check it for errors (a field must be here). I thought about having the if statement up front and only doing operations if it was an offline record, but then I can't reference the other timestamps.

I can put the time periods into a chart, and that will add them up. However, the calculated time is not representative of all the time for the period the report is executed, so, while the ratios are still mostly good, due to idiosyncrasies of the data, the resulting chart is not 100% correct. For edge cases, I am not able to calculate a total of how long the connection is offline. For example, if a connection was offline for the entire length of the report, there will be many time stamps that it is offline, but because there are no time stamps where it is online, the offline period is 0, throwing off the chart.

How can I calculate these periods and then compare them to the length of time of the report? I have already created a formula that gives me the length of time in the report (the divisor aka numerator), but I need the dividend (denominator) for my calculation.

Is there a better way to approach this problem?

Upvotes: 1

Views: 234

Answers (1)

Ken Hamady
Ken Hamady

Reputation: 116

Crystal Reports calculations that use the Next() function have to wait until after all of the sorting, grouping and totaling are done. They work, but they strictly limit what you can do with the results in Crystal. The best approach is to write the SQL query so that each record is linked to the next record. That way the dataset that Crystal sees includes both the beginning of the period and the end of the period in the same record. How you write that SQL would be a good separate question.

For edge cases when there is no value, I think you would want to use IF-THEN logic to replace the missing value with either the beginning of the target period or the end of the target period.

Upvotes: 1

Related Questions