how to get count of unique, active, itil-roled user accounts

[Casey12]

How do I get a count of unique, active, itil-roled user accounts in Reporting?  So far, my report is like the following:

table: User Role (sys_user_has_role)

filters:

State is Active

Role is itil

User.Active is true

To be expected, the User field has some identical values (e.g., there are multiple "John Smith"s in the organization, each with a different account).  And, to be expected, some users can have multiple accounts with the same User value (e.g., user "John Doe" can have account A and account B).  However, I have discovered that, at least in my ServiceNow instance, I have several User values (in this User Role table) that also have the same User ID value.  For example, User "John Smith" appears twice in the table (with an itil role), and both instances have the same User ID.  So, it seems that a given user account can appear in this table 2+ times.  Is it possible that this table can have duplicate rows?  If so, then how can I get a count of unique, active, itil-roled user accounts?

[Shane J]

Have you tried grouping by User ID, and then just scrolling to the bottom right to get your count?

Run your report:

On the bottom right is a count by whatever you grouped:

[Casey12]

I haven't considered that.  That's a really good idea.  Thank you.

[staceybailey · Accepted solution]

You can also report against the User table directly with filters of Active = true and Roles 'is' itil.

There is some extra "magic" behind the scenes that allows you do filter on roles from the user table.

[Adam Stout]

I would report on this by reporting on sys_user with a related list condition on sys_user_has_role.  That will easily handle duplicated since they only have one user record.