Reporting on Age of calls

[Ralf Peters]

Hi,

I was wondering if anybody has come up with a  measure/report   to will report on aged calls , grouping by "10 days", "20 days","30 days" , ">30days".

with or without drill down ?

 

Thanks

 

Ralf

 

 

 

 

 

[msw_116]

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?

[Ralf Peters]

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   

[Kelvin]

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.

[Ralf Peters]

Hi Kelvin,

no updates  ....    reporting  forum must be the least active one I think !

Ralf

[Everton1878]

I need something that shows calls over 90 days old, looking more for something in the advanced analytics rather than the reports

[msw_116]

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

[Ralf Peters]

Hi Matt,

thanks for the script , I'll  see if i can find the relevant Service manager variables

 

Ralf

 

[Guest]

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

[Ralf Peters]

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 :

 

 

 

 

 

 

[Guest]

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:

and for the filter:

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:

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:

(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":

 

 

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:

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

 

[Ralf Peters]

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

 

[Guest]

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

[Everton1878]

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