Need a customized column in project report that returns custom form field information from tasks.

< > Wow. The solution to this is going to be well over my head. I’m going to stand back and see which of the Text Mode experts can suggest something. ☺

Hi Linden, I do not believe this can be a project report because I do not think it's possible to search on sub-objects. I think the only way to do this in Workfront is using the "Collections" feature, but I still don't know if it's possible on tasks or issues. If it is, I'm hoping someone tells me how this can be done! I think you can accomplish this with a task report though, where you filter on tasks where the "Type" is not null, and then add your columns as needed. It can look like a project report, see the screenshot attached. In your report, you'll need to add 12 columns for the dates, formatted as shown here but with the month values adjusted for each month: displayname=Mar 16 textmode=true valueexpression=IF(YEAR({actualCompletionDate})=2016, IF(MONTH({actualCompletionDate})=3, {DE:Type})) valueformat=HTML displayname=Apr 16 textmode=true valueexpression=valueexpression=IF(YEAR({actualCompletionDate})=2016, IF(MONTH({actualCompletionDate}) =4 , {DE:Type})) valueformat=HTML My only concern here would be if you have multiple tasks within one project that have a "Type" value. If this is the case, they'll all show in your report. Perhaps this is what you want. And, if this is the case, you can just group your tasks by Project Name. Is this what you were looking for? Narayan

#ERROR!

Hi Narayan, Thank you for the response. I tried your suggestions below and whilst the custom columns do work, you were right with your concerns below in that the report pulls multiple line items per Project.The visual is not at all what we are trying to achieve. I appreciate your help and input. -Linden

Any chance the tasks that you want to see in the report are connected to a template task? If so, then you can filter based on the template task so you only see those specific tasks and not all tasks with the type field not null. Template tasks are the foundation to the majority of our Workfront structure these days - makes life a lot easier.

Could I ask a couple of questions to clarify what you're trying to achieve: In the case where a project has multiple tasks that have an entry for 'Type', what is it you want displayed? Will there only be one in the current month, for example, and others can be ignored or will be possibly be several tasks with different types that full in June, and you want the earliest (or latest) one that fall in June? Do you want the month to be dynamic - in the example you gave June 16, is that because we are in June 16? Next month do you want July 16? If that is the case, do you need to choose and how far back (would this month / last month do, or might you need to look back 7 months on occasion?)

In the case where a project has multiple tasks that have an entry for 'Type', what is it you want displayed? The name of the drop down option selected in the Type field. Will there only be one in the current month, for example, and others can be ignored or will be possibly be several tasks with different types that full in June In rare cases we could have two tasks within the month period with this custom field attached (see example below that shows an instance where there are 2 deliveries (two tasks with type selected - delivery 1 and delivery 2 are examples of the drop down selection) and you want the earliest (or latest) one that fall in June? Ideally it would show both. [cid:image001.png@01D1D214.1126F640] Do you want the month to be dynamic - in the example you gave June 16, is that because we are in June 16? Next month do you want July 16? If that is the case, do you need to choose and how far back (would this month / last month do, or might you need to look back 7 months on occasion We would always have 9 columns - one for the current month and then the remaining 8 for each month after. Each month I would delete the prior months column (may in the case above) and add another custom column representing the next month (February in the case above). Each of those columns should only return data if the tasks planned completion date falls within that particular month, and the task has ‘Type’ custom form field attached to the task. I should also note I could have the option of returning a milestone name. So if the planned completion date of a task within a project falls within the month range we specify for that column, and there is a milestone attached, return the Milestone name/s in the column. Ø Reason I am trying to do this with the custom form field instead is that our Milestones are only built to show customer deliverables. I built the custom form to be able to show important dates within the project that don’t count as Milestones. As shown above the desired end result is a sort of calendar view for each project, that gives a visual of when the critical dates are occurring. Thanks Barry, Linden

Becka, Thank you. Yes, the tasks I need to see are all from template tasks. Are you saying that I could create a project report, create a custom column for a month and then return information from the custom form if the date criteria + template task name criteria is met? Thanks, Linden

So here's the complexity. Generally, reports give you one line per whatever type of report it is - so if it's a project report, it's one row per project. If it's a task report, it's one line per task. You want one line per project, so it should be a project report. But you want to access task information. In reports, you can pull information down a level, not push it up. A project has many tasks. Within a task, you can report on the parent (project) information, but within a project you can't get to the task details. One way around this is with matrix reporting - which consolidates multiple line entries into a single row. So you could do a task report, but in a matrix group by Projects and only get one row per project, with a repeating column-per-type for each month. Except Matrix reports only show you numbers, not text. (See 'Raw Matrix.docx'). Narayan has shown how to do a custom column for each month of the report. You could replace the type name with an incremental number (1 for Delivery 1, 2 for Delivery 2 etc) and then Sum them in the Matrix report which would be fine EXCEPT for if you had two in the same month (if Delivery 1 was July 1st, and Delivery 2 was July 15th, you'd just get '3' for July - is that Delivery 1 and 2 OR Delivery 3?). The other alternative would be to do a calendar, with a coloured filter for each type. (See 'Type Calendar.docx). Not exactly the format you originally requested, but maybe functionally suitable for your purpose.

I think what you need (and this would benefit us and other customers most likely) would be for Workfront to let you add custom milestones per project - not just select from the standard milestone path. Then have some kind of new calendar report view which can plot the names of the milestones on a single line calendar (like your example). I know our project managers would love this. If you think it's a good idea, then please submit it. I am happy to do submit it also once we get the idea fleshed out.