Ever since the release of Service Desk Express colour coded Quick Views have been supported. However, the examples that were always demonstrated related to colour coding based on Urgency ID rather than date/time (i.e. rather than approaching due date etc.). BMC subsequently created a knowledge base article that detailed the basics of how to color-code Quick Views based on the due date and whilst this article does give the basics, I thought it may be helpful to provide a bit more detail and a slightly more appropriate (well at least in my opinion) example.
The example I want to implement is a completely dynamic one whereby, the colour of the ticket (incident, work order, problem or change) will be:
- Green: If the current date and time is before the recommended fix date calculated as a result of the urgency selected.
- Orange: If the current date and time is between the recommended fix date and the due date calculated as a result of the urgency selected.
- Red and Bold: If the current date and time is after the due date calculated as a result of the urgency selected.
So the advantage here is that different urgencies can be selected as a result of SLAs etc. and the Quick View will automatically colour code based on the appropriate recommended fix duration and due date – nothing is hard coded.
As per the knowledge based article, to do this we need to create a calculated field in the relevant ticket module (in this example we will do it for Incidents). So, log into DB Admin and right-click on the Incident module and select Add Field…
Select Calculated as the Field Type from the drop down list and enter the following values in the appropriate textboxes:
- Alias Name: Due Date Status
- Column Name: DUE_DATE_STATUS
- Display Name: Due Date Status
- Formula: CASE WHEN GETDATE() > “RECOMMENDEDFIX_DATE:” AND GETDATE() < “DUE_DATE:” THEN ‘Warning’ WHEN GETDATE() > “DUE_DATE:” THEN ‘Critical’ ELSE ‘Normal’ END
Click the button to Test the formula which will prefill the type as varchar. Click the Save icon.
Log out of DB Admin as that is you done in there.
Now we need to create the Quick View as discussed above. Log into Service Desk Express and open the Quick View manager. Expand the Incident Management node and right-click on Incidents assigned to members of my group (Predefined Queries) and choose Copy Query. Enter a sensible name for the query and click OK. The Quick View wizard should appear with the query appropriately populated.
Click the Output link and click on the Due Date Status field from the Available Display Fields listbox and click the arrow to make it appear in the Selected Display Fields listbox.
Now click the Formatting link followed by the Advanced >>> button in the bottom right hand corner. We are going to add the three conditions specified above:
- If Due Date Status = Critical Then Background Color = Red and Text Attribute = Bold
- If Due Date Status = Warning Then Background Color = Orange
- If Due Date Status = Normal Then Background Color = Green
So assuming that these have been added correctly your Quick View should look something like the image below:
Click Review/Save followed by the Save button and your done. Your incidents will now be automatically colour coded based on the Recommended Fix and Due Date of the incident.
As always, any comments (positive or negative) always welcome.
I been reading some of your guides, and I find most of it very useful. Specially cause im pretty new with SDE and its SQL database. And I see, I got a lot to learn about SDE still.
I found this article very interesting because my managers have been bugging me for some time now, just with a little twist.
The twist is they only want 30 days old incidents (Open or ON HOLD) marked as red in the quickviews. Was hoping u could help me out how to add that special field in the database instead of your due_date_status.
Many thanks for the feedback – always pleasing to hear.
There are essentially two ways of doing this. You could modify the field to only display the data you want and in which case the SQL you would need would be something like:
CASE WHEN DATEDIFF(dd, BASE.[DATE OPEN], GETDATE()) > 30 THEN ‘Warning’ ELSE ‘OK’ END
Alternatively, simply set the Urgency/Priority (depending on which version you are on) durations to be 30 days and then the code as I have written it will simply work for you.
The advantage of the latter approach is that if you change your requirements later it is much easier to change than having to take the whole system down.
Happy to discuss if you need more information.
Better late than never !
Finely I got a chance to take down SDE for some hours. And the Due Date Status field in the database is made.
And it works sweet 😉
Thank you for the guide Alan.
No sweat re the late response – I seem to spend my life apologising for late replies at the moment. Glad it helped.
Thank you for the great posts!
How could I do this based on hours? In one quickview I want to be able to display tickets open for 24 hours, 48 hours and 72 hours.
Would I need to create 3 new fields, one for each value?
Thanks very much for the praise.
You could do this really easily with a single calculated field that returns a datediff between the open date and the current date e.g:
CASE WHEN DATEDIFF(hh, “DATE OPEN”, GETDATE()) >= 24 AND DATEDIFF(hh, “DATE OPEN”, GETDATE()) = 48 AND DATEDIFF(hh, “DATE OPEN”, GETDATE()) = 72 THEN ’72’ ELSE ’00’ END
Then in the Quickview you can use the Advanced Formatting explained above using 24, 48, 72, 00 instead of Normal, Warning, and Critical.
Hope this makes sense.
Thanks Alan. This worked perfectly!
How can we put a complex SQL syntax in the Advanced Tab of conditions when we create a Quick View.
Your help is highjly appreciated.
To be honest I don’t think you can. What are you trying to achieve? You may be able to create a Calculated field that returns the result you need and use that in the Quickview.
As always, keep up the awesome work you’re doing here. My question is about the Recommended Fix field – You said something about the Recommended Fix date & time field being calculated by the selected Urgency – Is that out-of -the-box with SDE, or do you have to modify that field in SDE to be a calculated field, because it doesn’t auto-calculate in my system.
Thanks very much for the kind comments. Always appreciated.
The Recommended Fix Date/Time field in the Incident module is out of the box but in order to get it to populate you need to populate the Recommended Fix Duration field in the Urgency module (< SDE 9.6) or the Priority module (9.6 onwards).
Hope this makes sense.
It very much so helps! Thanks Alan!