Unable to replicate the following reports

[Everton1878]

We currently do a CSV export of all calls logged for the last 2 years, import it into MS Access, run various queries and export to Excel to produce our graphs

I've been able to replicate some of our current graphs in the advanced analytics but I'm stuck with the following ones

Open Vs Closed Tickets with Queue Impact

I can get the red and the green bars onto a graph but I can't add the line graphs to the same graph. We also need to be able to add trend and target lines

Outstanding Tickets by Age

I might be able to do this, I think I would need to create a measure for each range of days

We also like to be able to do a pie chart per team i.e. Development, Engineering, Infrastructure and Service Delivery

Calls Opened by Site

I couldn't get it to show the sites along the bottom instead of the months

Opened v Resolved by Team Previous Month

I can do this one with the exception of combining our two service delivery teams into one as it is grouped by team

We have a Service Delivery Reception and Service Delivery Escalation team but we tend to report them together as Service Delivery

Resolved Calls by Resolution Type Previous Month

We can pull out the closure profiles but we only want to report on the top level

Calls may be closed as 4CS > Fault or 4CS > Request but we just want to report 4CS

If we can do that, then I also want to be able to do for the last 3 months, so we'd have one bar for each profile per month

Closed by Team (In and Out of Fix)

I think I can probably do the smaller pie charts but not sure how to do the bigger one with the multiple teams and the percentage of calls closed in and out of fix for each

Percentage of Calls Closed Within Fix

I've just managed to get close to this but the percentages are a bit out

In our current reports we just look at closed and resolved calls but when I put that criteria into the measure the percentages are way out

 

I've not tried the average fix time yet but I think I should be able to do that one

 

Edited April 28, 2017 by Everton1878
lost my images when posting

[Guest]

Hi @Everton1878

I'm sorry for the delay in responding here. I've had a look at each of these reports to see whats is and isn't currently possible. I will separate out some of the reports into different posts for ease of reading

1) Open Vs Closed Tickets with Queue Impact

We do have the Linear Trend line option - but we don't currently have a target based trend line.
The options and output can be seen in the screenshots below:

[Guest]

2) Outstanding Tickets By Age

I have managed to achieve something close to what you are after, as highlighted in my screenshots.
Unfortunately in the widgets we do not have the options for pie charts currently - these are only available in out standard reporting function which is not part of the Advanced Analytics suite

[Guest]

3) Calls Opened By Site

I couldn't get this exactly like your screenshot but I was able to get some of the way there using a "SQL Group By" widget. As you can see, instead of having the specific month names, I've had to use "1 Month Ago", "2 Months Ago" in doing it this way because you need to specify the text that will be displayed so its not dynamic in that sense. 

The other issue is the order of the coloured result bars (and how it appears in the legend). This isn't possible to currently amend, but I have raised this internally with our developers to see if this is something we can make a manual choice, so watch this space!

[Guest]

4) Opened v Resolved by Team Previous Month

Unfortunately not much we can do on this one - the automatic grouping will be based on the value that is stored in the database, which in this case is the specific team names. 

5) Resolved Calls by Resolution Type Previous Month

We don't have the ability to split/group the categories based on a particular level yet (we do have a change outstanding to enforce category selection at a particular level e.g. must select the top level - so potentially as part of that, we may store this information slightly different in the DB which could help out in this scenario). 
I did play with a few ideas, e.g. creating a separate measure for each category using criteria such as "where h_category LIKE 'Hardware%" - but when using a measure in a widget, the x axis will always show the date - and this is different to your requirement. 

6) Closed by Team (In and Out of Fix)

Unfortunately right now we can't display/process percentages through the Reporting module of Hornbill - and this is the only area where pie charts can be used so there isn't a workaround for this scenario. 

I will flag these requirements to the developers to ensure they have visibility of the types of reports that our customers are requesting and what can be performed to ensure you have the flexibility to implement them

[Guest]

7) Percentage of Calls Closed Within Fix

This sounds like a data issue rather than an issue with the reporting or how you have the widget set up. It would be useful if you could post a few screenshots of what you have already configured for this particular widget/measure and the areas you believe are wrong and why. And I could potentially have a closer look at your instance for you. 

 

 

I hope the above posts have given you some insight about what is and isn't currently possible, or maybe even just given you a few ideas. Let me know if you have any questions or comments, and I'll be happy to help

[Everton1878]

Thanks @Bob Dickinson, I'll have a look through those and see how I get on and also give some more info on item 7

[Everton1878]

I've been asked if we can do something else as well but I don't think it's possible. I think I'll have to create separate widgets for it

We currently have some measures that look at the number of opened and closed calls daily and the number of calls outstanding per month

Those measures will run daily and monthly respectively

I've been asked if they can show the info for the current day and month respectively and update every 15 minutes

I'm going to create a separate widget for this

[Guest]

Hi @Everton1878

You are correct - this would need to be performed on another widget, as the measures only record the last full sample e.g. the last full day, the last full month. 

"Daily" is currently the shortest frequency selection we have to choose from. Hourly has been discussed in the past, but I believe that it has not been introduced due to performance concerns based on particular queries have been configured. I can see if there has been any change in this stance, but its not in the short term plans to introduce this. 

Typically a counter widget is a better option for "Live" counts of data, whilst the measures are better for the trends. 

Kind Regards

Bob

[Everton1878]

@Bob Dickinson

Can you give me the SQL for the date ranges for 0-2 days and 2-5 days?

I'm struggling a bit with them

Thanks

Pete

[Guest]

Hi @Everton1878

0-2 Days:

h_datelogged >= DATE_SUB(CURDATE(), INTERVAL 10 DAY) AND h_datelogged < DATE_SUB(CURDATE(), INTERVAL 2 DAY)

 

2-5 Days:

h_datelogged >= DATE_SUB(CURDATE(), INTERVAL 5 DAY) AND h_datelogged < DATE_SUB(CURDATE(), INTERVAL 2 DAY)

 

I don't have a huge number of recently logged calls on my test instance to double check this, but I believe it should work as required. To make any changes, simply tweak the number in the "INTERVAL" functions. 

Kind Regards

Bob

[Everton1878]

Thanks @Bob Dickinson, I was pretty much there, I had the the lowest number first