Oops, should say we're on 7.25
For your specific situation, the easiet solution may well be to write the query to test for the value of the Incident.Colour attribute. If it equals the colour number for the red it's one you want. You'll need to add a couple more criteria to ensure it's still a live incident, but it should work.
Having said that, the way you are looking to do it is the technically correct way and having just added some info to the other htread about breach call reporting, I should really be pushing you down that path. The issue you are having is with enumeration of the database value. If you write a query to show the Action Time Type as a column, what value do you see? Have you tried to use that value in the query rather than a 1 or 0? If you still have those errors, please log this with support as it is a bug.
I'd love to test the incident.colour attribute, but the colours have negative numbers and SD (TP) wont let me test for negative numbers....
I will test the action.time type and see what it shows as in a column, but again it will only allow us to test for positive numeric values...
Message was edited by: Stuart Walker [typo]
OK, I stuck the Action TIme Type in a column, the one's I'm after are shown as 'Percentage' however when I add Action Time Type as a criteria I only get the option to test for numerical value.
Also, if I tell it to prompt user, I can enter a '1' in and it works. If I put the value '1' into the criteria directly I get the same error as before.
Will be logging with our provider (Hi Ben & David!)
Hayden at HelpDesk Solutions has pointed the way;
instead of using
Action.Action Time Type Is Equal To (1)
Action.Action Time Type Is One Of (1)
Can't use the same work around on the Incdent.colour though, as you still can't test for negative numbers
Just as a totally different approach that I have implemented to measure the point at which the call gets resolved, how about using the Priority attribute to reflect the progression through the service level. I admit, this will only work if you are not using the global priority field that can be set through an escalation action.
What we did was set up ten priorities representing 10% increments through the SL life. There is an escalation created to move through those as the SL progresses. When the call is completed the priority that the call is at shows that state. We then wrote some nice reports to show the profile of completion. This could actually be represented on the dashboard with a bar or pie chart.
This sounds like a really good way to actually see what the profile of your closed calls are in terms of where in their service level they have been resolved if resolved within service level. Very useful if you need to identify where your stated service levels are being achieved by a mile - Good for the customer but can help in identifiing where people possibly do not have enough to do!
I currently use the Priority attribute to sort my workload list. Any suggestions on how to do it differently so that I can use the Priority field as you have suggested. I want to sort the workload list based on next call to breach. Any suggestions on how to achieve this in any of 7.2.4 / 7.2.5 / 7.2.6?
1 of 1 people found this helpful
You could always use the Order value on the Escalation to sort the workload list. You could actually use this to measure the progress as well. It's really just a matter of identifiying a unique attribute that can be changed and does not affect any other reporting or management of calls.
I was doing the same today... but I went on filtering the incidetns on specific color. I had the same problem, negative number, its not a valid value. Tried some different combinations, none of them worked.
So did you find any work-around on this?
*Here the query what i have is showing some 700 records, where some of them are on GREEN color, and rest on RED. I need to show only RED ones.
So, is this possible in 7.2.6?
I actually work around it a different way.
I wanted the colour so we could easily find calls at 90% of the SLA - we have an escalation at 90% that turns the incidents orange, and while I couldn't test for the colour, I WAS able to test for the 'Order' that was set by the same escalation. The catch was that I had to test for all the 'order' values for 90% in all of our SLA's.
so my criteria looks like:
Order Is One Of ( 900, 1400, 1900.......etc)
AND Status.Title Is Not One Of ( Closed, Resolved)
Not as neat, but it gets there in the end.
our customer had the same need. He wants queries on the welcome page for the different colors which represents different escalation parts. So i ran in the same problem as you, that colors are negativ values. So my workaround was, i created a new attribute in the pm_process called colorcode with type string. After that, i created an update trigger on the pm_process table, every time, the original color attribute changes, update my colorcode attribute. Because of its a string, you now can query on the negativ values.
I will attach you the trigger, maybe its helpful. Trigger is running about 8 month without any problems but you should test it first in an test environment, if it fits your needs.
colorcode.sql 716 bytes
Although there is nothing wrong in one sense with what Christian has suggested, I would strongly advise against introducing triggers anywhere in the database. They are a means to an end, but they are not supported and if there is a change in the code that for some reason impacts their operation, support is not obliged to spend time fixing it for you and more importantly if you like, how long might it take for you to realise that there is actually a problem with your system.
This discussion raises a really important issue that can be related to all of the ITBM product and that is the one of understanding as much as possible at design time in order to incorporate the necessary data that would be needed for reporting at a later date. Now, I'm not going to sit here and suggest that we are able to predict every possible occurrence, but conceptually this is very important.
Having said all of that, there a couple of alternatives that you can look at to sort out this query. Yes, the use of the order value is one, the second is the use of the priority value. Again, you need to be in a position where you are not using the Priority attribute within the system for anything else. If that is the case, then you can set the Priority to be a consistent value for all SLA's that reach the 90% limit. Then it only requires one criteria in your query.
The other approach which is a bit more complex and may not apply in this case, is choosing the right business object to write the query from. It may well possible to achieve what you are looking to do by writing the query on the Escalation Point object and criteria relating to that rather than basing it on Incident. As I don't know the exact design of your SLA's, I couldn't comment specifically on that.
I know this is an old post, I found it and was able to determine a solution from this. I used a similar solution to the colorcode attribute, just didn't use a SQL trigger for this.
Instead, I created the ColorCode attribute (String - 20) on the Process object as a Before Save calculation. There are a number of ways to do the calculation:
- Return the Colour attribute with .ToString() -- works great for queries, not so great if you will ever display this info
- Return a string like "%%% - Color" (e.g., "050 - Yellow") -- lets you display the information and sort on it, great for displaying information in grouped queries, like "tickets assigned to me, grouped by colorcode", otherwise you have groupings named "-1" and "-65536" which mean nothing to any who doesn't have the color numbers memorized
Obviously, I opted for the latter. I can now generate pie charts of "my/group un/assigned tickets" that are grouped by escalation point (color). I can, of course, also query for any tickets that are >=50% and <100%, for example.