Jump to content

Countlist - Resolved 2017


Recommended Posts

I am trying to work out how many calls we have resolved this calendar year under the IT Support Service Name in a form of countlist.


Firstly, am I correct in thinking that status.resolved is not included in status.open? I have tried entering as h_status = status.open OR (h_status = 'status.resolved') and the figure shown is incorrect.


Secondly, the resolved figures do look correct but I haven't specified the to & from dates...so can't guarantee that the figures shown are for this calendar year or is this not required? 


Apologies in advance, I am a beginner to do this so still trying to go through the basics.  Thanks for your assistance in advance,



Resolved 2017.docx

Link to comment
Share on other sites

Hi @shamaila.yousaf

No need to apologise - the reporting can be tricky, and looking at your screenshot, it's not too far away at all from what you want so its a great start.

To break it down:

StatusThe four (main) statuses in Hornbill are open, on hold, resolved and closed. So in your example, you only want to bring back the ones that are either in a status of resolve or closed (as all the others are currently being worked on. So to do this:

(h_status = 'status.resolved' or h_status = 'status.closed')

ServiceYou have this correct already!

h_fk_servicename = 'IT Support'

DatesThis is the hardest bit as lists of counters don't offer date ranges by default. But we can be smart with some SQL operations (you can find these by simply searching on Google) and include them in the WHERE clause. So for all requests this calendar year, it would be:

h_dateresolved >=  MAKEDATE(YEAR(NOW()),1)

So if you were to put that all together it would be along the lines of:

(h_status = 'status.resolved' or h_status = 'status.closed)
AND h_fk_servicename = 'IT Support'
AND h_dateresolved >=  MAKEDATE(YEAR(NOW()),1)

Give this a try and see if that gives you the count you need!



Link to comment
Share on other sites

I managed to move forward with the status.open & status.resolved issue - mistake my end. 

Thanks for the MAKEDATE as well as confirmation of the above.  It would have been an epic fail if I didn't add the date to the countlist!


So my numbers looked correct - excellent.  As a follow up to this I wanted to do a separate widget to see how much from the closed & resolved were within fix for this calendar year.  I am sure I have the below correct - just need someone in the 'know' to confirm.

(h_status = 'status.resolved' or h_status = 'status.closed') AND (h_withinfix='1') AND (h_fk_servicename = 'IT Support') AND h_dateresolved >=  MAKEDATE(YEAR(NOW()),1)


Thanks to yourself @Bob Dickinson I am a lot more familiar with the reporting...I may have a few more queries but am a lot more confidence and the playing back the webinar has been mega useful too!



Link to comment
Share on other sites

Hi @shamaila.yousaf

Yes your latest widget looks exactly right - you can also have another widget showing the ones that are breached (h_withinfix=0) and put them on a Dashboard next to each other for a nice comparison. 

Kind Regards


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...