Reporting: list all users assigned to a job role

[laurence_jayawardane]

Hi Community,

I'm looking for a way to list all users assigned to a particular job role in a report. As a new Sys Admin I'm still not super versed in the API explorer world, but I did take a peek at it and didn't seem to find what I was looking for. Can this be done? Do you do this?

Thanks,

Laurence

[KatherineLa]

Hi Laurence,

If you're wanting to filter a report to show only the users that have that specific role as one of theirs, then a simple filter would do it. No need to get into the API. There's a quirk though, when you're filtering on any field where there can be multiple possible values assigned, you have to look really carefully at the field you're filtering on to get the expected results.

If I say 'Only show me Users in which the User -> Role ID equals 'Email Specialist'' then my results will include Users where their ONLY role is Email Specialist. Any users with more than one role will not appear in the results.

If I say 'Only show me Users in which the User -> Role IDs equals 'Email Specialist' then my results will include Users where 'Email Specialist' is ANY of the roles they are assigned. Users with more than one role will appear in results, as long as one of those roles is Email Specialist.

Notice the plural form of IDs changes in the second statement. Group ID, Group IDs, Team ID, Team IDs etc. Similarly for other objects, it's their way of allowing us to say 'Find me a perfect match' or 'Find me a match where this is anything in the list of options'.

Katherine

[Heather_Kulbacki]

This seems like it would be a great Quick Tip - @Kyna Baker - inactive‚

[skyehansen]

Agree, a quick tip would be great. I have a slightly different understanding of this -- or else misread the first answer completely. For me, the ID is the "primary" value, and the IDs are where ALL values are stored (an easy way to access a collection).

If you're searching for "ID=" this will search the primary value field only. My distinction is that users with more than one role would still appear in these results if their primary role is listed as "email specialist" -- because this is the only field being searched and the IDs "collection" is being ignored.

If you're searching "IDs=", this would search the full collection (which contains the primary).

For groups and teams, the primary value is stored in Home Group ID and Home Team ID. The other groups and teams (or rather, ALL groups and teams) are nested in the respective IDs collection.

[KatherineLa]

Hi Skye,

Interesting build, I hadn't notice the primary part playing a role in my test but you are correct.

The main place I'd run into the singular/plural quirk was in projects/tasks with a particular custom form attached. Category vs Categories will get wildly different results in many reports for us. Ditto for 'Assigned To' versus 'Assignments' (especially when building calendars, that bit me a couple times).

[skyehansen]

I agree. The "category/categories" functionality was so inconsistent that I eventually started a list for myself to keep track of:

-Views (how to see the primary category vs all the categories)

-Filters (how to filter on primary vs all)

-Any gotchas (e.g. how to filter in the negative, what didn't work)

[kynabaker16]

Hey thanks guys! I'll work this into a Quick Tip soon.

Kyna

[TinaHu1]

Hi Kyna, I saw the post about the report recipes/cookbook... are these quick tips for reporting collected in one place as well? Sorry if I missed communication on this thanks!

[kynabaker16]

Hi Tina!

We do have all of the Quick Tips collected as a topic here: https://one.workfront.com/s/topic/0TO4X000000bqnOWAQ/quick-tip?tabset-21363=3 (just click on the discussions tab if it doesn't drop you right there from that link). This will take you to all Quick Tips, not just the reporting-focused ones.

We also are working a slight tweak of the community page that will make it VERY easy to find these Quick Tips so, stay tuned for that as well.

Thanks,

Kyna

[William-- · Accepted solution]

Hi Laurence. You never mentioned what kind of report it is that you are trying to create. This can be open to lots of interpretation. To get the best answer, I suggest mocking something up and highlighting where you want the user's names to appear.

The best results may come from a User report filtered and grouped by role; or something else depending on what you're looking for.

Sometimes when I'm looking at Job Roles in the Setup area, I want to see more than just the list of job roles - I also want to see which current (or not current) users are assigned to the role. The below text mode columns will work in the Job Roles setup area as columns in a view; or in a Job Roles report:

A list of everyone assigned to the role:

displayname=All Members

listdelimiter=<br>

listmethod=nested(userRoles).lists

shortview=true

textmode=true

type=iterate

valueexpression={user}.{name}

valueformat=HTML

A list of users who are not deactivated, and have logged in within the last six months, assigned to the role:

displayname=Active Members

listdelimiter=<br>

listmethod=nested(userRoles).lists

shortview=true

textmode=true

type=iterate

valueexpression=IF({user}.{isActive},IF(NUMBER(DATEDIFF({user}.{lastLoginDate},$$TODAY))>-180,CONCAT({user}.{name}," - ",{user}.{lastLoginDate}),""))

valueformat=HTML

A list of users who are either deactivated, or have NOT logged in within the last six months, assigned to the role:

displayname=Inactive Members

listdelimiter=<br>

listmethod=nested(userRoles).lists

shortview=true

textmode=true

type=iterate

valueexpression=IF({user}.{isActive}="false",CONCAT({user}.{name}," - DA"),(IF(NUMBER(DATEDIFF({user}.{lastLoginDate},$$TODAY))<-180,CONCAT({user}.{name}," - ",{user}.{lastLoginDate}),"")))

valueformat=HTML

[laurence_jayawardane]

Hi everyone,

I want to say a big thank you to all of you - I keep going back to this post and not knowing how to respond, because your answers are so good but realistically beyond my current level of knowledge.

@Katherine Stibley‚ and @Skye Hansen‚ , thanks to your replies, I was able to solve another issue I was having in a View with the singular/plural differentiation. I didn't know there was a difference until I read your words! This is going to make my (Workfront) life this much easier moving forward. I could not figure out why Workfront had Home Group vs a list of Groups that a user could be assigned to, and now at least I know how to handle the differentiation.

@William English‚ , you bring a good point, I didn't mention what kind of report I was thinking of. I was thinking of a User report listing all job roles each user is assigned to - that's what makes the most sense for auditing and reporting to management. But a Job Roles report with the list of all users assigned to each job role works just fine, too! I added columns to the Job Roles report I had started working with, for your three examples - they are great! This will accomplish what I need it to do, and then some!

Thanks again for all your expertise, it's so helpful!

[skyehansen]

this is the nicest thank you letter! Thank you!

[KatherineLa]

Thank you, and glad we were able to help! The reporting tools can be a little hard to use until you get a solid grasp on the data architecture for sure. You'll get there, and hopefully with some guidance here and not frustrated hair-pulling. I about lost my tiny mind before I figured out Category/Categories.

Best advice I can give a new admin is to ask for help BEFORE you over-engineer some crazy workaround. If you can't figure out how to do something that seems like it should be fairly simple, chances are it DOES do that - but the path there isn't always intuitive.

Best of luck!

Katherine

[laurence_jayawardane]

That's also really good advice, thanks Katherine!