Jump to content

Reporting/Advanced Analytics by Service Category


Recommended Posts


When creating a new service, you choose a Service Category. The most obvious reason for this is to help filter services on the Service Portal. I was hoping to use this to help me with reporting and advanced analytics. The problem I have is that I keep having to update all of the reports and all of the measures when I introduce a new service. For example, I exclude Estates, Security and Motion from all IT reports and analytics. As soon as I introduce a new service, such as cleaning, I have to update everything. Conversely, I can include all IT services in this report/analytic but as soon as I introduce a new IT service (Exhibition AV for example) I have to update everything.

I was hoping to use Service Category but it doesn't seem to be in the requests table.

Does anyone have any suggestions?


Link to comment
Share on other sites

Hi @chrisnutt

You can be creative with your "where" criteria in your measures by including a Sub Select statement - which will query a table that is not the main request table to help establish the criteria that needs to match to bring back results. 

So based on your example, I could include the following in my measure which will bring back all the service names from the h_itsm_services table that do NOT have a category of, for example, "IT Services". Then based on that subset of data, only return results from h_itsm_requests that have a service that match one of that subset:




h_fk_servicename IN(SELECT h_servicename FROM h_itsm_services WHERE h_fk_servicecategory != 'IT Services')


To take this a step further, you don't even have to use the Service Category - you could use a Custom Attribute against the service itself, and perform your filtering criteria around that. So in this example, I have created a new Custom Attribute called "Include in Reports" which is actually h_custom_b. And when I came to do my Sub Select, I would use


h_fk_servicename IN(SELECT h_servicename FROM h_itsm_services WHERE h_custom_b = 'Yes')


A similar set up can also be performed in the Reporting function in Hornbill. 

I hope this helps

Kind Regards

Bob Dickinson



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...