Jump to content

Reporting on Age of calls


Ralf Peters
 Share

Recommended Posts

Ralf - are you looking for a report that gives numbers of calls only, for the given days timeframe?   Also - in terms of age, I assume this means from when the call was opened, regardless if this was a weekend date?  Finally - I assume you are referring to SupportWorks?

Link to comment
Share on other sites

Hi,

Numbers of calls is fine , ideally I would like to use drill-down if possible.

Yes from when the call was opened, not too fussed about weekends.

No its actually Service Manager, but if you have a support works report xml  i might be able to use the sql from it   :-)

 

Thanks for replying

Ralf   

Link to comment
Share on other sites

Did anyone get any further with this. I am also struggling to work out how to both report on this and also to set up a dashboard graph of ages of calls.

Link to comment
Share on other sites

Hello Ralf (and others! :))

I can't help much with the advanced analytics I'm afraid, but against the SupportWorks database you may find the following useful.  Script one groups the ages of call by timeframe (e.g. less than 10 days, 10 - 20 days etc.), whilst the second script provides an "audit", giving call references and the age of calls - this can be expanded to add other information as you wish.  Here they are:



SELECT 
    SUM(
     CASE 
        WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) < 10
        THEN 1
        ELSE 0
      END)
     AS "< 10"
,    SUM(
     CASE 
        WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) BETWEEN 10 AND 20
        THEN 1
        ELSE 0
      END)
     AS "10 - 20"
,    SUM(
     CASE 
        WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) BETWEEN 20 AND 30
        THEN 1
        ELSE 0
      END)
     AS "20 - 30"
,    SUM(
     CASE 
        WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) > 30
        THEN 1
        ELSE 0
      END)
     AS "> 30"
, SUM(1) AS "Total Calls"
FROM opencall
WHERE `status` NOT IN (6, 16, 17, 18)

[/CODE]

The second script (audit):

[CODE]

SELECT callref
, (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) AS "Age (Days)"
FROM opencall o
WHERE `status` NOT IN (6, 16, 17, 18)
ORDER BY 2 DESC

[/CODE]

 

I hope they help somewhat.

Matt

Link to comment
Share on other sites

Hi Ralf, Kelvin

Sorry for the lack of response here. I have recently just posted on another post with a way of simply counting the number of current open calls that are over 90 days old. I'm not sure if this will help you achieve what you are after but its worth having a look at: 

 

The example I have given is a count - you can "save as" this widget several times, change the number of days old, and place them all on a Dashboard . If you were looking for something more than this, would you be able to specifically advise how you would want this displayed? I.e. if its a graph, what is running down the x-axis/Y-axis. I can't promise we can achieve it, but if we can visualise the exact output you are looking for, I can advise further, 

Kind Regards

Bob

Link to comment
Share on other sites

Hi Bob,

 

Thanks for the info , replied in the link you provided .

ideally we would like to have them grouped by  team and be able to drill down into the tickets .

 

select 30,60,90 days

Bar chart direction Horizontal

stack Multi  NO

Team  on the Y- axis  , No of calls on the X - axis

then  colour the bar into green amber red

 

similar to this :

sample.JPG

 

 

 

 

 

 

Link to comment
Share on other sites

Hi Ralf,

A measure may not be the optimum solution in this scenario - because a measure is taken at the end of a period, and you are actually reporting on requests that are older than a period. 

I have a couple of potential solutions you could use:

1) Creation of "SQL Group By" Widgets

You could create 3 SQL Group By Widgets similar to the criteria that I mentioned in my other post. For example, below I have created the widget called "Number of Requests Older Than 90 Days - By Team" as a widget type of Data Chart - SQL Group By:

SQL Group By.png

and for the filter:

SQL.png

I have then taken copies of these and simply changed the interval to 60 and 30 to create 3 separate widgets and display them on a dashboard next to eachother:

DASHBOARD - All 3.png

This can be viewed as a Dashboard and shared with whoever you like. Though the downside may be that you cannot drill down into the figures. 

 

2) Creation of Request List Dashboards

This method does not use the conventional form of Dashboards and Reports through the user app, but actually uses the My Dashboard functionality in the request list. If you are unfamiliar with this, please check out our wiki and video - https://wiki.hornbill.com/index.php/My_Dashboards

But in theory, you would create 3 views - again, one for 30, 60 and 90 days. The views you create may look like this:

Incidents Over 30 Days Old.png

(Be sure to include all teams in the "Team" criteria that you would like to be included in the view - they do all need to be added rather than simply leaving it blank due to the way that the views work).

You then will have your view, off which you can create a Chart and be able to group it on "Team":

 

chart.png

 

And, once you have created charts for all 3 views, you can place them on "My Dashboard" in the order that you like, as below:

My Dashboards.png

The great thing about this, is that the data returned can be drilled down into for each column (or pie slice) to give you a quick view of the individual requests. 

I know this may not be exactly what you were anticipating based on your post above, but at the moment we cannot combine data onto the same graph unless it has been created via measures, so we tend to create separate graphs and place them side-by-side like in the examples above. 

I hope this helps you in some way! Please let me know if you have any questions

Thanks

Bob

 

Link to comment
Share on other sites

4 hours ago, Ralf Peters said:

Hi Bob,

these are great , thank you very much . Do you know of a way of sharing "My Dashboard" views ,i.e.   export/import  ??

so they I don't have to recreate them on multiple accounts ?

Thanks

Ralf

 

Hi Ralf, 

Unfortunately at present this is not something we have the ability to do - "My Dashboards" are on an analyst by analyst basis. But I'll feed this request back to be looked into .

Kind Regards,

Bob

Link to comment
Share on other sites

It would be useful to share the My Dashboards, I have to screenshot them and send them round my colleagues

Can probably get round this a bit when we start rolling out the dashboads in the advanced analytics

I see the option to allow drilldown but it doesn't seem to do anything unless I'm missing something

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
 Share

×
×
  • Create New...