Counting Function

[Former Community Member]

I am fairly new to reporting in WF. I have what I feel like is a very basic question. I feel like I am missing something very simple. I'm trying to figure out how to count things in WF. Specifically I would like a report that returns two columns of data. One column is the project reference number The second column is a count of the number of tasks in the project I know how to get this info by grouping, but I want to generate a report that returns only the two columns when I export the data to an excel file. Matt Strong

[NicholeVargas]

@Matt Strong If you were to create a Project Report, add a column for Reference Number and then create a new column, switch to text mode and paste the following, that should pull in the record count of tasks: displayname=# of Tasks textmode=true valuefield=totalTaskCount valueformat=HTML If you want to rename the column, just change the line, displayname=, to whatever you would like! If you have any questions, let me know Nichole Vargas Workfront

[RandyRoberts]

I used your code and it works perfectly, so thanks. I added sort code but it doesn't sort. Is it possible to sort this column? Is there any documentation that mentions "totalTaskCount"? I looked and cannot find any.

displayname=Task Count

textmode=true

valuefield=totalTaskCount

valueformat=HTML

sortOrder=1

sortType=desc

[NicholeVargas]

@Randy Roberts‚ I don't think our documentation specifically calls out that field, but I did try adding an additional line for querysort=totalTaskCount and that didn't work either.

Most likely, this is a dynamic field and is subject to change at any time if someone adds/removes tasks, so that would make it not "sortable." Again, I don't know if there is any documentation out there that states which fields in Workfront are dynamic and which aren't, but I can look into getting something added!

If you have any other questions, let me know!

[Former Community Member]

@Nichole Vargas , Thank you very much. I didn't realize that text mode would allow me that amount of flexibility. Looks like I need to do some reading. Thank you again. Matt Strong

[Former Community Member]

@Nichole Vargas One more question. When I first attempted to create this report I tried using a task report. In that report I was able to remove all parent tasks by creating a filter with Task:Number of Children equal to zero. Is there a way to do this same sort of thing in a project report? I don't see this as a filter option in a Project Report. Matt Strong

[Doug_Den_Hoed__AtAppStore]

Hi Matt, To my knowledge, there's no way to conditionally filter a collection (i.e. count only the tasks with no children in a Project) on a Project level report. You could drop back to a (filtered) Task report, though...but switch it to be a Matrix report and: Show only Project Name and Task Number in the view, with COUNT on the Task Number (NOTE: in order to do a COUNT, you must choose a numeric field) Group Horizontally by Project Name Group Vertically by Project Update Type (NOTE: assuming that all Projects have the same Project Update Type, this is a trick to force all of the counts into a single column) Filter for Number of Children = zero Here's an example of the output: If that suits your needs: fabulous. But if not, I invite you to consider our "https://store.atappstore.com/product/ubercalc/" UberCalc solution, which is a swiss army knife of functions you can use to enhance Workfront, such as generating aggregating data (such as what you're really after), job numbers, designing custom tabs with specialized workflows, and much more. Regards, Doug Doug Den Hoed - AtAppStore

[NicholeVargas]

@Matt Strong Like Doug had mentioned, there is not currently a way to filter collections within a project report. I know you are looking for a single view you can export that shows project name, project reference number and total number of tasks, so like a matrix report, you could also do the following: 1. Create a Task Report 2. Filter for Task > Number of Children > Equals > 0 3. Before you add/remove any columns, go to the Groupings tab and group the results by Project Name 4. Go back to columns and remove all. Add in Project Name, Project Reference Number (In the column settings, summarize this column by MAXIMUM) and this one for total tasks, using the below text mode: aggregator.displayformat=int aggregator.function=MAX aggregator.valuefield=project:totalTaskCount aggregator.valueformat=intAsInt displayname=# of Tasks textmode=true valuefield=project:totalTaskCount valueformat=HTML 4. Save and close your report. 5. This should give you two tabs - Details & Summary. The Details tab will ultimately be a row for each task that meets the criteria, but the Summary tab is what is shown in the grouping bar. In your case, the Summary tab will include Project Name, Project Reference Number, Total # of Tasks and Total Count (which is the # of tasks that meet the criteria of number of children = 0). You can then export this tab into Excel and remove the Total Count column so that you have 3 columns. If you have any questions, let me know! Nichole Vargas Workfront

[KatherineCh]

Hi Nicole, Have you tried counting the number of projects within a portfolio? I was trying to apply the same logic you mentioned, but its not working out. Any advice would be appreciated! Katherine Chatham