In previous posts (http://www.joatit.com/wordpress/?p=96) and (http://www.joatit.com/wordpress/?p=69) I showed how to colour code the quick views in a traffic light style using the following query:
(CASE WHEN [CLK_STOPTIME:] IS NOT NULL THEN 'On Hold' WHEN GETDATE() > [RECOMMENDEDFIX_DATE:] AND GETDATE() < [DUE_DATE:] THEN 'Warning' WHEN GETDATE() > [DUE_DATE:] THEN 'Critical' ELSE 'Normal' END)
Recently I was talking to one of our users who, when using Service Desk Express 9.8, rather liked the ability to view closed incidents in the quick views. She commented however, that when you viewed closed incidents with the above query everything went red eventually. It would be much better if you could see if the call was inside or outside SLA as defined by Close Date vs. Due Date.
So without further ado; creating a calculated field with the query below will not only achieve the same results as the previous posts but additionally will show if the incident was closed in time (Normal) or not (Critical) when looking at closed incidents:
(CASE WHEN BASE.[CLK_STOPTIME:] IS NOT NULL AND BASE.[STATUS] = 'O' THEN 'On Hold' WHEN GETDATE() > BASE.[RECOMMENDEDFIX_DATE:] AND GETDATE() < BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Warning' WHEN GETDATE() > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Critical' WHEN BASE.[CLOSED ON] > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'C' THEN 'Critical' ELSE 'Normal' END)
Hope it helps. As always, please keep the feedback coming. It makes sure that what I am posting is of relevance and interest to the people reading.
JOATIT 
Thanks for sharing you knowing with us.
Really, i learned a lot from your writing.
Please accept me as one of your admirer visitor.
Hi Motasim,
Many thanks for your kind comments.
Regards,
Alan
Hey Alan – I tried adding the above calculated field to try and implement the traffic light and the DBADMIn tool is giving me an error
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near”‘.SQLSTATE = N’37000’
(11203)dataBase error #102
Any ideas?
Thanks!
Hi Josh,
My guess is it is to do with cutting and pasting (the bane of my life). Check that the apostrophes are genuine apostrophes not “impostors.”
Regards,
Alan
Hey man, good call – That was absolutely the issue! You da man!
Hey Alan, this worked great, except I notice that if an incident has the clocked stopped, then the color doesn’t work – If you start the clock again, it works fine. Is this by design?
Thanks!
Hi Josh,
In the Conditional Formatting have you specified a colour for “On Hold” calls?
Regards,
Alan
Alan,
No, I sure didn’t. We’re not utilizing the Stop Clock feature, so I’m really not interested in the tickets that the clock has been stopped. If I remove the (CASE WHEN BASE.[CLK_STOPTIME:] IS NOT NULL AND BASE.[STATUS] = 'O' THEN 'On Hold' from the calculated query, will this remove the Due Date Staus of “on hold”? Will the rest of the query still work?
Thanks again for all u do – This site is great!
Hi Josh,
You need to leave the CASE statement but you can remove that line i.e:
(CASE WHEN WHEN GETDATE() > BASE.[RECOMMENDEDFIX_DATE:] AND GETDATE() BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Critical' WHEN BASE.[CLOSED ON] > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'C' THEN 'Critical' ELSE 'Normal' END)
Hope this helps.
Regards,
Alan
I think this query is great and it tests okay as a calculated field. But I’m running in Oracle 10g and when I try to save the field I get a message that the query is correct, but database administrator is not able to add it to the table. Any ideas would be greatly appreciated.
Thanks in advance,
Marc