Jump to content

Reporting Query - Measures


Stephen.whittle
 Share

Recommended Posts

Hoping someone can help, trying to create a measure which records a count of requests assigned to a specific support team in a monhtly period but only count unique reference numbers so I have a true count of demand on that team. Is anyone able to help with the query where clause. 

Link to comment
Share on other sites

Hi Stephen, 

 thanks for your post. Just to clarify what you're after, would I be correct in saying that you're looking for the number of assignments that a team has had during a month, whether that be an initial assignment of a ticket or an escalation? Essentially, how many tickets have passed through that team within the month, regardless of which team the tickets started with and which team they ended with?

Thanks,
Dan

Link to comment
Share on other sites

You've hot the nail on the head. We have some teams where they are heavily involved in a request but are not seeing it through from end to end so do not have a statistic against that activity as for some BPMs we are not recording activities (we are doing so moving forward). I have a measure running for requests assigned to teams which shows extremely high volumes in some teams but this is largely due to requests being moved between teams which is why I would like to filter to only see "unique" reference IDs to get a true picture of how many requests are being handled. 

Link to comment
Share on other sites

I'd suggest creating your measure using the table h_itsm_request_team_assignment. This table stores a record of all ticket assignments that take place in Service Manager and stores the previous team who had the ticket (in the column h_previous_team_id) and also the team to which it was assigned (in column h_team_id). It also records assignments to individual agents too.

For your situation, we're really only interested in the fact that it was assigned to that team, but of course there could be more records in this table indicating assignments between agents in that team.

So, we want to count the records where h_team_id = "the/team/id/of/the/team/you're/interested/in" AND h_previous_team_id != h_team_id

For the benefit of others who might come across this thread, I'll just to try and illustrate where I'm coming from here, below are two records relating to the assignment of IN00000026. Lets say I want to only understand how many tickets have been given to DANCORP/ITSERV/SUPP over the course of the month so I'm not interested in the reassignments to individual agents within that team (i.e. the record with h_id = 20). This means I want to ignore records where the previous team id is the same as the new team id because if these two columns are the same, I know the assignment action has been within the team, rather than between two teams.

image.png

The Data Source Configuration in your measure will look something like this:

image.png

There is of course one scenario where this query would include multiple assignment records for the same ticket and that's if DANCORP/ITSERV/SUPP worked on a ticket, assigned it elsewhere, and then the other team assigned it back. Whether this matters will really depend how your teams work together. If the assignment back to that team constitutes a second round of effort, it might be worth recording in the count.

Let me know if that helps

Dan

 

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
 Share

×
×
  • Create New...