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)?
Discussion Filed Under:
Comments 3 Comments • Jump to latest comment
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
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.
Thanks J1gnesh - your second solution works .....
Saved me quite some searching .....
Would you like to reply?
Login or Register to post your comment.