Report question: User report with list of projects they own

[Femke_Crijns]

Dear community members,

I want to check whether all colleagues with a Planner license currently manage projects.

So, I'm looking for a user report that filters all colleagues with a Planner license and one (nested) column will give me the projects that that user is the owner of.

Does anyone have the slightest idea how to do this?

[Christina_Jarosz]

Hey there! Create a project report and use the filter as Owner's access level and if you want to group by project status, use that under your grouping tab.

That should give you what you need.

[Femke_Crijns]

Hi Christina,

Thanks for your reply!

With your suggestion, I get a list of Owners that do have their own projects, but I want it the other way around. I also want to see the users without any projects they own.

But thanks for thinking along!

[skyehansen · Accepted solution]

hey, if you check the API explorer (look at the user object and check the collections tab) you'll see this probably isn't possible. You cannot call on the project collection from a user report, so no nested column for you.

As Christina mentioned, a project report filtering on access level, grouped by owner would get you a list of projects owned by your planner license people.

From the user report you could use EXISTS statements to pull out a number of things, such as users who don't own projects, users who don't create projects, users who haven't owned a project in the past year, and/or so on. In addition to all your user filters (e.g. user is active, user has a planner license) you would also add at least the following:

EXISTS:a:$$EXISTSMOD=NOTEXISTS
EXISTS:a:$$OBJCODE=PROJ
EXISTS:a:ownerID=FIELD:ID

i.e. there is exists no [line1] project [line2] where the project's owner ID field is the same as your user's ID [line3].

Obviously, swap out ownerID with enteredByID if you want to find who hasn't been creating projects. Or add entry dates if you want a feel for who hasn't owned a project in the past year (for example)

[Femke_Crijns]

Hi Skye,

Thanks for your answer. I'm not very familiar with Exists filters, so this is a nice one to start experimenting with!

[Madalyn_Destafney]

Hi there, how big is your group of plan license holders? If it's a manageable number you could deduce from a project report, I don't think you'll get exactly what you're after with a user report.

I'd do a project report with a filter of 'owner >> license' (not access level) = Plan, add any other filters like project entry (looking for past year, past month?) and add a grouping of owner name. This will show you not only who owns projects but how many within the groupings.

If you have a smaller group of people, you can see who doesn't have a project. Would be hard with a big group to cross check to see who is missing but I don't have a way for you to pull in those that own no projects.