Reporting/Advanced Analytics by Service Category

[chrisnutt]

Hi,

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?

Chris

[Guest]

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:

 

String:

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

 

 

[chrisnutt]

Great, thanks @Bob Dickinson

I'll give these a try but it certainly looks like this will work!

Chris