Video Screencast Help

Calculated column based on Time to Status from Starting

Created: 14 Nov 2012 | 3 comments

Pretty new in ServiceDesk, so maybe a stupid question.

In a report, I have the "time to status from starting" as a column,  only for the "resolved" status.

Now: I need to have a "consolidated" view:

- everything resolved below 1h (3600)

- between 1-8h (3600-28800)

- more then 8h (28800)

Creating a calculated column with

IF [ReportProcessStatusHistory.TimeToStatusFromStarting] < 3600 THEN
"<1h" ELSE
IF [ReportProcessStatusHistory.TimeToStatusFromStarting] < 28800 THEN
"1-8h" ELSE
">8h"
 ENDIF

does not work, as the field does not seem to be numeric.

Any idea how to handle (preferably in the reporting tool, not via SQL statement)?

Comments 3 CommentsJump to latest comment

J1gnesh's picture

Try this. I was doing something similar with nested IF/THEN functions and i needed a few extra quotes and an extra ENDIF.

 

 

IF [ReportProcessStatusHistory.TimeToStatusFromStarting] < 3600 THEN
"<1h" ELSE
""IF [ReportProcessStatusHistory.TimeToStatusFromStarting] < 28800 THEN
"1-8h" ELSE
">8h"

ENDIF""
 ENDIF

 

 

J1gnesh's picture

Also ran into the same problem with incorrect format.

Add "Date Posted" and "Process Started" to your report and then use the following code in your Calculated Columns formula. You can hide those fields in the report if you do not want them to show.

 

IF [ReportProcessStatusHistory.DatePosted] - [Process.ProcessStarted] < TimeSpan('1:0:0') Then
"<1h" ELSE
""IF [ReportProcessStatusHistory.DatePosted] - [Process.ProcessStarted] < TimeSpan('8:0:0') THEN
"1-8h" ELSE
">8h"
ENDIF""
ENDIF

 

This seemed to work as both parts are in the TimeSpan format.

 

Jackot's picture

Thanks J1gnesh - your second solution works .....

Saved me quite some searching .....