How many users (licenses) in a Portfolio? Report help needed!

[petep1975]

Hi all!

I´m trying to count how many licenses do we have in use / portfolio.

I've been trying to create a report in multiple ways, but with no success. Mostly, my reports are based on logged hours within a portfolio, but it's still not there. I'm more interested to find how many users and job roles are working in it. What I'd like to have is:

• A report chart showing all portfolios.
  • Portfolio, amount of users working in it, f.ex. how many users have logged hours inside the Portfolio.
  • Amount of Users should tell the size of the chart's bubble/etc., not logged hours in total.
• A details or matrix report of Job Roles worked within that Portfolio. (Job Role/Count)

And 2nd,

• Within a Portfolio, how Job Roles and Users are divided to portfolio's Programs. Just like above, but within a portfolio's programs.

Hard or easy? Help me out! =)

[AnthonyIm]

Hi Pete - this is going to be very difficult for you because 1) people and roles are not tied to Portfolios, and 2) people can work on multiple portfolios so your total number of licenses is going to be way off from your total number of license.

I had an agency ask once to see all the users ever assigned anything in a certain portfolio, so I'll give you the steps for that and you can see if it helps you. But you will need to export the data to Excel and do the pivot table there.

I did an Assignment Object Type report that way all the various assignees on tasks are included. Since you want all portfolios, the only filter you really need is Assigned To ID is not blank. If you only want it for a certain time period (like 2020), you might want date filters like Task >> Planned Completion Date is greater than 1/1/2020 and then do an OR filter and do Issue >> Planned Completion Date is greater than 1/1/2020 (and don't forget to repeat the Assigned To ID is not blank filter in the OR statement).

For fields, you will want Assigned To Name, Job Role Name, Assigned To License, and then two fields in text mode...

valuefield=project:portfolio:name

textmode=true

valueformat=HTML

querysort=project:portfolio:name

displayname=Portfolio

valuefield=project:program:name

textmode=true

valueformat=HTML

querysort=project:program:name

displayname=Program

Then you will want to export to Excel (if you have a ton of data, I recommend doing Report Actions -> Send Report and send it to yourself.)

Once in Excel, you highlight everything, go to the Data tab, and then hit Remove Duplicates. Have all the columns selected.

Now you are down to unique data and you can do a pivot table to group by portfolio, program, and role.

For the report with number of hours logged to a Portfolio, that one you might be able to do in Workfront as a chart with the Hours Object Type, but since we don't log time in Workfront, I'm not 100% sure about that.

Hope that helps.

[petep1975]

Thanks @Anthony Imgrund‚ !

I ran through your solution, it's good and giving results, but not quite still there.

The end result needs to be calculated in logged hours & job roles. Separate all Portfolios and amount of users been logging hours in there.

The problem (in human process): Multiple assignments can be done to tasks, but in real, the many of the assigned users might not do anything to the task (f.ex. multiple assignments in a task, only one will really do something to the ask.)

Second thing is, I'm avoiding excel as much a possible, too lazy to maintain continuous reports in different systems or speadsheets. My goal is get everything out from WF. A report.

Your ideas and thoughts are highly appreciated! Thx for that! =)

(if I'll find the solution, I'll share it here)