Reporting issues trying to scale to other departments

[Michael Sharp]

We're looking to scale to other teams within our firm however having major issues with reporting which is stopping us dead.  All reporting metrics etc are currently looking at the platform as a whole and I would like to be able to segregate each team/department.  How is best to do this?  My initial thought is to somehow leverage the service categories however I'm struggling to incorporate these into our widgets, measures, dashboards and reports.

Regards and thanks,

Mike.

[Michael Sharp]

have used Service Types to get around this

[Kevin Kennedy]

@Michael Sharp

Thank you for posting your solution to this.  We will be facing the same questions over the next few months, as we begin to incorporate Quality Dept. Gage Repair Requests & possibly Human Resources within our Hornbill Instance.  I had not even considered that this might be an issue until I stumbled across your post.  You were gracious enough to come back and post a response when you solved this, for which I am grateful.

[Michael Sharp]

@Kevin Kennedy no problem, let me know if you need any more info on this.  The below screenshots should give you an indication of what's been required at our end:

[Victor]

Umm.... 

h_fk_serviceid IN (select ...)

Subqueries... that's usually not a good idea, especially on measures... Any chance to not use the subquery here?

[Michael Sharp]

@Victor do you have a better solution given my predicament? :-)   I'm sure this isn't a unique circumstance

[Victor]

@Michael Sharp is that a list of services where the custom field has the "IT" value? Would it be possible to actually put the id values in the list rather than using the subquery to retrieve that list?

[Michael Sharp]

@Victor ideally I would filter by service domain but I was told by prof services that this was not possible?

[Victor]

@Michael Sharp well.. might not be possible (haven't checked) but even so I would still advocate for no use of subqueries even if there is a way to filter by service domain... so perhaps the filter should have the query like:

h_fk_serviceid IN (1, 5, 24, 85, 16)

Just an example, the IDs will be the relevant ones from your instance...

[Michael Sharp]

@Victor seems very cumbersome as you would have to adjust ALL reports, ALL metrics and ALL filters in order to introduce a new service for example.  Is there no easier way to do this?  Obviously capturing service domain into the main requests table automatically would be more ideal?  How do other companies achieve this to separate IT from HR for example?

Mike.

[Michael Sharp]

Doesnt seem practical in my view

[Victor]

1 minute ago, Michael Sharp said:

seems very cumbersome as you would have to adjust ALL reports, ALL metrics and ALL filters in order to introduce a new service for example. 

Very true, and yes, not very practical... However, the main (only) reason for my comment is that the subquery can be (become) even worse in some scenarios... Now, there might be that the subquery is the most practical way to do this but it can (not a certainty but a possibility) cause performance issues especially on measures. So this is what I was trying to find out if we can not use it... 

[Michael Sharp]

@Victor would you be able to take this back to development please to see if this can be made any easier or if that particular field can be included on the requests table?  Really isn't a practical approach here?