reports ITSM Reporting

[Adambingley]

Hi All, 

Wondering if any of you kind individuals could help.

We have been utilising Hornbill service manager now for 7 months and although I believe its a great ITSM service desk tool, the reporting is down right appalling and the advanced analytics cost is extortionate for a small team.

Could anyone share their standard reporting definitions for a few basic reports, the front end is massively clunky to get used to and its proving very time consuming.

I'm looking for the following but ANY would be most welcome.

1. SLA reporting by month.

2. Tickets Logged / Resolved by month. (Ticket Volume)

3. AVG Resolution times

4. Incidents caused due to change. (&How do we flag incidents as caused due to change?)

5. Individuals Performance

6. Unplanned Changes by month

7. Incident Report For Incident/problem management

Best Regards

Adam

 

 

 

 

 

 

 

 

 

 

 

[James Ainsworth]

Hi Adam,

Thanks for your post and for the comments about Service Manager being a "Great ITSM tool".

Regarding the reporting I thought I'd cover the areas of reporting that are available and describe our approach.  Point 2, the Service Manager Reports, is possibly the area that you may want to have a closer look at.  Some of the reports you have mentioned above are already available.

1.) Request List Views, Personal Dashboards, and Exports - While this is not your standard reporting, we have provided tools for support staff to build custom views to display requests, build charts, publish and share charts, and export lists to CSV.  The goal behind this is to provide live data and information to support staff within the environment that they are working, without having to wait for the creation or delivery of reports. 

2.) Service Manager Reports - This is fairly new to Service Manager and is available through the Service Manager menu under the option titled Reports.  Some documentation can be found here.  This is currently a Beta feature so you will find that there is still more documentation and features to be added.  This has been available for some time now to allow customers to provide feedback.  If you don't see this menu option you may need to have your account added to the Service Manager In-App Reporting Role which is done through the Administration Portal.  This reporting feature is being provided for managers and users that do not have the desire, knowledge , or access to navigate database tables, joins, queries, etc.  The idea behind this reporting is that we are providing predefined reports based on industry standards and you can use each of these reports as a base to create an unlimited number of related reports using the report configuration and by defining the criteria.  All without scripts, tables, or queries.  Another important aspect to these reports is that you can only report on what you have access to.  We have just added about 8 new reports over the last few weeks and we have 3 more coming out in another couple of weeks.  The number of base reports and features will continue to be expanded over time. 

3.) System Reports - Located within the Administration Portal this is more focused with the data experts in mind.  While the Service Manager Reports, described above, provide ease and simplicity, the result of this can be limiting when it comes to reporting on very specific data sets that sit outside of the provided reports.  The System Reports take this to the next level but they do require an extra set of skills in order to create what you need, but with this is the flexibility.  Unlike the Service Manager Reports which have controls to only show requests that you have access to, the System Reports opens you up to all the requests held within Service Manager. 

4.) Advance Analytics  -  As you are aware, there is a premium for this.  Again, the audience for report creation will more likely be the data experts.  There are a large number of abilities and detailing of the information that you want to report on, but with this comes complexity and therefore additional knowledge in order to create and define.  The biggest value with the Analytics is the trending.  Other reporting options will look at the data at the time the report is run.  Information over time can change in a request and therefore getting trends on some information may not be possible.  The Advance Analytics can pull information from the database over time and build up trend patterns. 

5.) Microsoft BI Integration - Taking reporting a step further, we have found that some businesses have Microsoft Power BI as a standard reporting solution that encompasses a number of different apps.  We have created integration points with Microsoft Power BI to allow customers to bring reporting from Hornbill into their centralized reporting.

6.) Additional Integrations - With our public APIs, iBridge integrations, and exporting tools, this opens up our data to any number of possibilities.  

 

I hope you find this helpful.  Again, as mentioned above, have a look at the Service Manager Reports within the main Hornbill client.  With Hornbill's ability for continual deployment of our apps you will find that features such as this will continue to grow and improve.  

Kind regards,

James

[Adambingley]

On 8/23/2019 at 9:02 PM, James Ainsworth said:

Hi Adam,

Thanks for your post and for the comments about Service Manager being a "Great ITSM tool".

Regarding the reporting I thought I'd cover the areas of reporting that are available and describe our approach.  Point 2, the Service Manager Reports, is possibly the area that you may want to have a closer look at.  Some of the reports you have mentioned above are already available.

1.) Request List Views, Personal Dashboards, and Exports - While this is not your standard reporting, we have provided tools for support staff to build custom views to display requests, build charts, publish and share charts, and export lists to CSV.  The goal behind this is to provide live data and information to support staff within the environment that they are working, without having to wait for the creation or delivery of reports. 

2.) Service Manager Reports - This is fairly new to Service Manager and is available through the Service Manager menu under the option titled Reports.  Some documentation can be found here.  This is currently a Beta feature so you will find that there is still more documentation and features to be added.  This has been available for some time now to allow customers to provide feedback.  If you don't see this menu option you may need to have your account added to the Service Manager In-App Reporting Role which is done through the Administration Portal.  This reporting feature is being provided for managers and users that do not have the desire, knowledge , or access to navigate database tables, joins, queries, etc.  The idea behind this reporting is that we are providing predefined reports based on industry standards and you can use each of these reports as a base to create an unlimited number of related reports using the report configuration and by defining the criteria.  All without scripts, tables, or queries.  Another important aspect to these reports is that you can only report on what you have access to.  We have just added about 8 new reports over the last few weeks and we have 3 more coming out in another couple of weeks.  The number of base reports and features will continue to be expanded over time. 

3.) System Reports - Located within the Administration Portal this is more focused with the data experts in mind.  While the Service Manager Reports, described above, provide ease and simplicity, the result of this can be limiting when it comes to reporting on very specific data sets that sit outside of the provided reports.  The System Reports take this to the next level but they do require an extra set of skills in order to create what you need, but with this is the flexibility.  Unlike the Service Manager Reports which have controls to only show requests that you have access to, the System Reports opens you up to all the requests held within Service Manager. 

4.) Advance Analytics  -  As you are aware, there is a premium for this.  Again, the audience for report creation will more likely be the data experts.  There are a large number of abilities and detailing of the information that you want to report on, but with this comes complexity and therefore additional knowledge in order to create and define.  The biggest value with the Analytics is the trending.  Other reporting options will look at the data at the time the report is run.  Information over time can change in a request and therefore getting trends on some information may not be possible.  The Advance Analytics can pull information from the database over time and build up trend patterns. 

5.) Microsoft BI Integration - Taking reporting a step further, we have found that some businesses have Microsoft Power BI as a standard reporting solution that encompasses a number of different apps.  We have created integration points with Microsoft Power BI to allow customers to bring reporting from Hornbill into their centralized reporting.

6.) Additional Integrations - With our public APIs, iBridge integrations, and exporting tools, this opens up our data to any number of possibilities.  

 

I hope you find this helpful.  Again, as mentioned above, have a look at the Service Manager Reports within the main Hornbill client.  With Hornbill's ability for continual deployment of our apps you will find that features such as this will continue to grow and improve.  

Kind regards,

James

Good Morning James, 

Thanks for your response.

I'm fairly well versed in SQL (Oracle OCA in SQL) so generating the reports from data perspective is fairly straight forward but as you cannot use SQL to generate reports using the clunky front end is painful, especially when you cant easily run the query then edit like you can in SQL.
Currently, I use the database direct to export the requests into a CSV then import into an Oracle database, I have pre-created various reports that I can run which is very time consuming hence why I posed the questions.

The Service manager reports are a step in the right direction but as you cannot see the criteria behind them, most are pointless, there's also nothing on SLA's which should probably be the no.1 report.

As an example, "Requests caused by change" returns nothing for me which means I'm not flagging the tickets correctly but how am i supposed to know how ?
Also, "Avg response time" & "Avg Fix Time" has anomalies from test tickets etc which skews the data but as you cannot filter these out they make the reports unusable.

Could you not display the reports SQL and potentially allow the "WHERE" clause to be editable? This would make the tool far more useful?

With regards to Power BI, we use Tableau, and, although i'm aware that you can use R script, it doesn't appear to be anywhere near as easy to use as as in Power BI, if you have any information on using Hornbill with Tableau then that would be most welcome.

Just from an outside perspective, we are in the process of rolling out a help-desk ticketing tool for various other departments (not ITSM) and we had 15 demonstrations from various platforms before finding the right fit, every single one had better standard reporting and most had dashboards covered under the standard cost all of which cost less p/m than hornbill which was very disappointing for me to find out.
I know you do offer some dash boarding from your Advanced Analytics but its 50% of our monthly bill just for a feature which should be included in the cost, the monthly cost of AA should include reports to be "built" for you on demand & support but I feel that the features should be covered under the standard cost of service manager.
 

Thanks

Adam

 

 

 

[James Ainsworth]

8 hours ago, Adambingley said:

Currently, I use the database direct to export the requests into a CSV then import into an Oracle database, I have pre-created various reports that I can run which is very time consuming hence why I posed the questions.

Hi Adam,

I was wondering if you were aware of the Hornbill Data Export tool?  This may be something that will help take away some of the time that your current method of using  Database Direct is taking up.

[James Ainsworth]

8 hours ago, Adambingley said:

The Service manager reports are a step in the right direction but as you cannot see the criteria behind them, most are pointless, there's also nothing on SLA's which should probably be the no.1 report.

That's good to hear that the Service Manager Reports are going in the right direction for you.  The highlighted reports are all specific to the Service Levels and Service Level targets defined in the SLAs.  If these are not showing then it might be that your instance is waiting for an update to Service Manger.  

 

Along with this, using the Active and Closed Request reports you can create custom reports using the criteria held in the request to define more SLA based reports.  There is also an option to show the used criteria on the report output.

 

 

[James Ainsworth]

9 hours ago, Adambingley said:

"Avg response time" & "Avg Fix Time" has anomalies from test tickets etc which skews the data but as you cannot filter these out they make the reports unusable.

While these can't be filtered out on the base report, you can create custom reports where you can specify the criteria that you use to mark them as a test to exclude them.  The base reports are just provided as a starting point and in almost all cases it will be the creation of custom reports based on the base reports where the value will come in.  Cancelled requests are also excluded from all the results, so when and where applicable, it would be worth assuring that test requests are cancelled as soon as the testing is completed.  

[James Ainsworth]

9 hours ago, Adambingley said:

Could you not display the reports SQL and potentially allow the "WHERE" clause to be editable? This would make the tool far more useful?

If you are referring to the Service Manager Reports, adding visibility of SQL and the ability to change it would start to take the away from the goal of these particular reports.  The audience for these are non technical.  The aim being that non technical staff don't have to go to the technical staff to get the reports that they need.  

[Gerry]

@Adambingley

I wanted to just add a commentary here as I think its important to represent both sides of the coin.  As a general rule we do everything we can to limit customers from generating direct SQL statements, we are well aware that many of our customers have the technical ability, but in our experience and after many hundreds of deployments we also know that customers cannot always appreciate that our database is designed first and foremost for application performance, in some regards the data is de-normalised intentionally by design, and as a result can easily lead to customers doing complex joins that can have an adverse effect on database performance.  To maintain good levels of application performance we therefore need to contain and in some way control what queries our database needs to serve. 

As an aside, we do offer an Enterprise Edition of our platform, which amongst other things provides for a distributed replicated database architecture, in essence all transactional queries (adds, updated, deletes, ACID and non-ACIT) are routed through the primary database, we then (depending on the size of the instance) have one or more slave replica databases, and we route traffic to these based on the function, in essence we have a separate replica database for running reports, measures and other ad-hoc queries. Our roadmap here is to eventually move to a fully data-warehoused solution where we will have a database optimised for reporting rather than application performance, and data will be ETL'd from the the application database to the reporting database. The thing is, by comparison to a single database, this architecture is much more complex and costly to provide, which is why its only part of the Enterprise Edition of our platform.

 

To your point with Dashboards and other vendors products including them, our Advanced Analytics is an option really geared towards larger team/enterprise use, its priced accordingly and for our typical customer, Advanced Analytics is a pretty  negligible cost by comparison to your situation, where you are at the very lowest end of the subscription range we can realistically support. However, we do offer "personal dashboards" which are highly configurable and can give you a lot of what most customers need, these are all included as part of the base subscription, they exist and are available in the absence of Advanced Analytics, just like other vendors in the industry.  You mentioned that you guys use Tableau, something that you would also need to pay for, you can think of Advanced Analytics being an alternative to buying something like Tableau, and in that context you should find Advanced Analytics very cheap by comparison.  

All of that being said, I really want to state that, smaller customers are very important to me personally, I have been there in a small team, wanting that bigger systems capabilities but not being able to afford it, and thats really frustrating.  We have built Hornbill with a lot of capability and I want to make that available to smaller teams to the greatest possible extent, but I need to balance that with the cost realities of providing such a service, that is just a commercial reality for us. 

Our strategy is to provide reporting that is ready-made, and we appreciate any feedback with regards to our shortcomings here, this will be fed back to the dev team, James is the product owner for Service Manager, so you are already talking to teh right people.  Our view is, if we design the reports, we can consider the output requirements in the database design and optimise accordingly.  The truth is though, in my 25 years of seeing ITSM deployments, no two customers want that same reporting output, its a never-ending battle to meet the demand, but we do our very best to accommodate all reasonable requests. I think you though, for your detailed feedback.

As James mentioned above, we do have some export tools which use our API's to grab raw data and pump into things like PowerBI, these tools are open-source, so if you are technically minded then you could do a simple export to our own database and report from there if we cannot offer what you need today, 

[edit] James also makes one other important point I should have touched on - we are striving to provide the greatest possible level of functionality to customers without the need for them to have deep technical capabilities, we consider hornbill a no-code environment and we are dedicated to ensuring we maintain that, its an important part of our strategic approach to the market.  This approach has enabled us to expand the use of our platform far beyond the confines of IT to teams such as HR, Facilities and Finance who generally do not have such technical expertise on-hand, overall we can deliver far more value to our customers taking this approach rather than the technical toolkit approach that was our past experiences with our on-premise Supportworks solution.

Kind Regards

Gerry
 

[James Ainsworth]

9 hours ago, Adambingley said:

With regards to Power BI, we use Tableau, and, although i'm aware that you can use R script, it doesn't appear to be anywhere near as easy to use as as in Power BI, if you have any information on using Hornbill with Tableau then that would be most welcome.

I would recommend using the Hornbill Data Export Tool to work with Tableau.

I've also added to your post regarding this question.

 

[Adambingley]

13 hours ago, James Ainsworth said:

I would recommend using the Hornbill Data Export Tool to work with Tableau.

I've also added to your post regarding this question.

 

Thanks James, I'll take a look at the export tool, that will probably do the job!

Just on one report that you missed, "Requests caused by change", what actually flags this to show on the report? is there a resolution category we need to be using to flag this?

Best Regards

Adam

 

[David Hall]

Hi @Adambingley

Just to pick up on your last post, the "Requests caused by change" report will show requests which have a linked Change Request with a relationship of "Caused by".  So for example if you link a change to an incident and choose the relationship "Caused By" then it should be shown in the report.

Hope that makes sense,

Regards,

Dave

[Adambingley]

1 minute ago, David Hall said:

Hi @Adambingley

Just to pick up on your last post, the "Requests caused by change" report will show requests which have a linked Change Request with a relationship of "Caused by".  So for example if you link a change to an incident and choose the relationship "Caused By" then it should be shown in the report.

Hope that makes sense,

Regards,

Dave

Brilliant - Thanks!

[Adambingley]

14 hours ago, Gerry said:

@Adambingley

I wanted to just add a commentary here as I think its important to represent both sides of the coin.  As a general rule we do everything we can to limit customers from generating direct SQL statements, we are well aware that many of our customers have the technical ability, but in our experience and after many hundreds of deployments we also know that customers cannot always appreciate that our database is designed first and foremost for application performance, in some regards the data is de-normalised intentionally by design, and as a result can easily lead to customers doing complex joins that can have an adverse effect on database performance.  To maintain good levels of application performance we therefore need to contain and in some way control what queries our database needs to serve. 

As an aside, we do offer an Enterprise Edition of our platform, which amongst other things provides for a distributed replicated database architecture, in essence all transactional queries (adds, updated, deletes, ACID and non-ACIT) are routed through the primary database, we then (depending on the size of the instance) have one or more slave replica databases, and we route traffic to these based on the function, in essence we have a separate replica database for running reports, measures and other ad-hoc queries. Our roadmap here is to eventually move to a fully data-warehoused solution where we will have a database optimised for reporting rather than application performance, and data will be ETL'd from the the application database to the reporting database. The thing is, by comparison to a single database, this architecture is much more complex and costly to provide, which is why its only part of the Enterprise Edition of our platform.

 

To your point with Dashboards and other vendors products including them, our Advanced Analytics is an option really geared towards larger team/enterprise use, its priced accordingly and for our typical customer, Advanced Analytics is a pretty  negligible cost by comparison to your situation, where you are at the very lowest end of the subscription range we can realistically support. However, we do offer "personal dashboards" which are highly configurable and can give you a lot of what most customers need, these are all included as part of the base subscription, they exist and are available in the absence of Advanced Analytics, just like other vendors in the industry.  You mentioned that you guys use Tableau, something that you would also need to pay for, you can think of Advanced Analytics being an alternative to buying something like Tableau, and in that context you should find Advanced Analytics very cheap by comparison.  

All of that being said, I really want to state that, smaller customers are very important to me personally, I have been there in a small team, wanting that bigger systems capabilities but not being able to afford it, and thats really frustrating.  We have built Hornbill with a lot of capability and I want to make that available to smaller teams to the greatest possible extent, but I need to balance that with the cost realities of providing such a service, that is just a commercial reality for us. 

Our strategy is to provide reporting that is ready-made, and we appreciate any feedback with regards to our shortcomings here, this will be fed back to the dev team, James is the product owner for Service Manager, so you are already talking to teh right people.  Our view is, if we design the reports, we can consider the output requirements in the database design and optimise accordingly.  The truth is though, in my 25 years of seeing ITSM deployments, no two customers want that same reporting output, its a never-ending battle to meet the demand, but we do our very best to accommodate all reasonable requests. I think you though, for your detailed feedback.

As James mentioned above, we do have some export tools which use our API's to grab raw data and pump into things like PowerBI, these tools are open-source, so if you are technically minded then you could do a simple export to our own database and report from there if we cannot offer what you need today, 

[edit] James also makes one other important point I should have touched on - we are striving to provide the greatest possible level of functionality to customers without the need for them to have deep technical capabilities, we consider hornbill a no-code environment and we are dedicated to ensuring we maintain that, its an important part of our strategic approach to the market.  This approach has enabled us to expand the use of our platform far beyond the confines of IT to teams such as HR, Facilities and Finance who generally do not have such technical expertise on-hand, overall we can deliver far more value to our customers taking this approach rather than the technical toolkit approach that was our past experiences with our on-premise Supportworks solution.

Kind Regards

Gerry
 

HI Gerry, 

I appreciate you taking the time for such a thorough response.

The export tool will certainly come in handy, especially if its something I can automate so i'll be looking into that shortly.

With regards to the personal dashboards, I did create my first dashboards yesterday but found a bug, James Ainsworth has had this checked by a developer and they confirmed that there is a discrepancy between the "View" data source and "Chart" data source even though it should be the same. Once this has been fixed, I'm sure I'll be able to use them as expected.
I'm quite shocked the issue was not spotted before considering it was the first dashboard I'd created but at least it will be resolved in the coming weeks.

I understand that you have to provide a tool that's usable for a wide variance of skills, you can't expect someone with little analytical knowledge to perform complex SQL and i appropriate that.

There are some obvious changes for me that would really help with the standard reporting you already offer:

As a real life example,  "Avg response time" & "Avg Fix Time"  reports shows anomalous values but as I cannot identify what's causing these issues it makes the reports unusable,  being able to drill down or preview the background data from the report would be required to figure out what's going on here and would be required in general to be able to investigate issues.
A friendly English wording of the criteria would also be helpful rather than the outright SQL.
 

Thanks again.

Adam

 

[Gerry]

@Adambingley

Thanks for the response, and no problem, this is a public forum so I generally feel compelled to keep threads balanced to ensure there is always a fair representation.  We do really appreciate your feedback, and yes I am sure you will find the odd bug here and there, I do apologise for that. We are quite aggressive with our test regime but we will never catch everything, I hope that on balance, the speed at which we can resolve such issues outweighs the fact that the odd thing does slip through the net. Thank you for reporting the issue, I am sure the team will sort it over the coming days. 

We are also always looking to improve what we do, customer feedback is critical to this and we very much appreciate your input always.  

 

Thanks,

Gerry

[Adambingley]

39 minutes ago, David Hall said:

Hi @Adambingley

Just to pick up on your last post, the "Requests caused by change" report will show requests which have a linked Change Request with a relationship of "Caused by".  So for example if you link a change to an incident and choose the relationship "Caused By" then it should be shown in the report.

Hope that makes sense,

Regards,

Dave

Hi David, 

How do you change the relationship of a linked change request, I don't appear to have the ability to set that?
See screenshot below.

 

Thanks

 

Adam

 

[ArmandoDM]

Hi @Adambingley

can you double check if you have active relationships between requests please?
If they are all disabled, then you will not have the option to update it

Regards

Armando



 

[Adambingley]

58 minutes ago, ArmandoDM said:

Hi @Adambingley

can you double check if you have active relationships between requests please?
If they are all disabled, then you will not have the option to update it

Regards

Armando



 

Hi @ArmandoDM

I didn't have any relationships, didn't know they even existed or needed to be set up.
I've done as per your screenshot but changed the type names accordingly and it seems to be working, presumably the type name has to be "Caused by" in order to show on the report?

Thanks

Adam

 

 

[ArmandoDM]

Hi @Adambingley,

yes in order to be shown in the report "Requests caused by change", the name has to be "Caused By".

As far the relationship types , here you can read more detailed informations:

https://wiki.hornbill.com/index.php/Service_Manager_Relationships

Thanks

Armando

[neilcg]

Hi Folks - we are not seeing this option on the Service Manager menu - We have the "In-App" role assigned...

Any pointers?

thanks

Neil

[James Ainsworth]

Hi Neil,

In some cases a user may need to log out and then back in again to see any changes to their rights.  Give this a try and let us know if you still can't see the menu option.

Regards,

James

[James Ainsworth]

Sometimes is can also be a cache issue.  If logging out and back in does not work, try clearing your browser cache.

[neilcg]

@James Ainsworth We found the Service Manager Reports area by working out the url, but we still don't see the menu option anywhere.

Presuming it will be here;

 

[James Ainsworth]

Hi Neil,

Yes, that is where it should be displayed.  Did you try clearing the browser's cache?

[neilcg]

10 hours ago, James Ainsworth said:

Hi Neil,

Yes, that is where it should be displayed.  Did you try clearing the browser's cache?

yes, cleared everything, logged out, new browser session. No change on the menu.

[Guest Ehsan]

@neilcg,

To access the Service Manager Reports, you'll need to:

• Enable "app.experimental.inAppReporting" application setting (Admin Tool > Applications > Hornbill Service Manager > Application Settings Tile).
• Assign "Service Manager In-App Reporting" role to your user, then login again to see the changes.

There is some documentation on this here: https://wiki.hornbill.com/index.php/Service_Manager_Experimental_Features

I hope this helps?

Ehsan