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

[LindenRh]

In a project report, I need a custom column labelled as a particular month (June '16 for example) that searches within it's project task list for two sets of criteria: 1. The task has a custom form field called 'Type' applied to it 2. The planned completion date of the task finishes within a date range - 06/01/16-06/30/16 Then; if both criteria are met, I'd like to return the data selected within the 'Type' custom form. Additional info: We apply the 'Type' custom form to tasks that are an internal deliverables but do not qualify as a Milestone. The field is a drop down selection with names of the deliverables such as Phase 1 or Samples. Desired end result is a nice one line summary of a project (with 12 custom columns as months) returning custom form task data. I have attached an example for reference. I'll send beer to whichever WF guru helps me on this one :) Thanks in advance, Linden

[EricLu2]

< > 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. ☺

[NRYNR]

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

[JimBr1]

#ERROR!

[LindenRh]

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

[BeckaJo]

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.

[BarryBu2]

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?)

[LindenRh]

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

[LindenRh]

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

[BarryBu2]

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.

[DavidCo3]

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.

[VictoriaPe]

Hi Becka, How do you reference back to the template task? We use templates for every timeline so this sounded like a great solution for a similar advanced report that I am trying to build. My only problem is when I try and filter based on Task Name I get a giant list of all the copied tasks on all the subsequent projects and can never find the template task. I tried the reference ID and ID from the web address and no results appeared. Thanks in advance!

[BeckaJo]

Victoria: This is going to sound silly, but I've actually built a report to help me with report building. I created a Template Task report so I could dig out the information related to the template tasks that I need to reference while I'm building reports - most importantly the Template Task ID. You're right ... trying to use the Template Task Name field as a filter is tough when you have different Template Tasks with the same name. I've attached a screenshot of my "Template Task Query" report. I use prompts to do the focused filtering I want so I'm only looking at the specific information I want to see (ahhh, the beauty of reports). You could create a custom view to use while viewing your Project Templates, but I prefer to have access to all of the Project Templates without having to navigate to and fro. Does this help answer your question?

[VictoriaPe]

That was so helpful! Thank you Becka! In Reply to Becka Johns:

Victoria: This is going to sound silly, but I've actually built a report to help me with report building. I created a Template Task report so I could dig out the information related to the template tasks that I need to reference while I'm building reports - most importantly the Template Task ID. You're right ... trying to use the Template Task Name field as a filter is tough when you have different Template Tasks with the same name. I've attached a screenshot of my "Template Task Query" report. I use prompts to do the focused filtering I want so I'm only looking at the specific information I want to see (ahhh, the beauty of reports). You could create a custom view to use while viewing your Project Templates, but I prefer to have access to all of the Project Templates without having to navigate to and fro. Does this help answer your question?

[Former Community Member]

displayname=June 2016 listdelimiter=‚Äö√Ñ√ß‚Äö√Ñ√ß‍ listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF(YEAR({plannedCompletionDate})=2016,IF(MONTH({plannedCompletionDate})=6,{DE:Task Parent Type})) valueformat=HTML Replace Task Parent Type with your field name and copy for each month by adjusting year and month. The ‍ < & z w j ; > as the list delimiter, it keeps disappearing on edit)is a zero width delimiter. What this does is check each element in your collection (ie tasks) and if it is in the proper year check the month, if that's right too put in the value of the custom field (which could be blank if it doesn't exist). If you have multiple tasks that meet the criteria you will get each value with no space between. You can use other delimiters but it will print out a delimiter for each element of your collection. It does the value expression on each element of the collection seperately. I haven't found a way to perform an aggregate calculation on the set. *drops mic* *walks away* Melinda Layten melinda@mlayten.com Independent Workfront Consultant

[BarryBu2]

Melinda, this is an amazing resolution. But I think you left two things out, namely: *drops mic* *walks away*

[NateBa]

Here you go Barry. This is on Melinda's behalf. In Reply to Barry Buchanan:

Melinda, this is an amazing resolution. But I think you left two things out, namely: *drops mic* *walks away*

-Nate Bagley --- Workfront Community Manager - Work Smart, Work Happy Message me directly at:

[LindenRh]

Melinda, I am currently OOO but look forward to trying this tomorrow when I return. Thank you kindly, Linden

[LindenRh]

Melinda, WOWWWWW! Thank you so much. This is eactly what I needed and it works a treat. Thanks to all who helped or gave input on this one. This resolution is huge! WF help told us this would not be possible so I am so excited to present to my team. Cheers, Linden

[LindenRh]

David, I'm not sure why but never got this repply in my email - apologies. I think this could be a great feature request! The ability to select multiple milestone paths in projects and then show certain paths in reporting and not others would be fantastic. For instance, if I could have two (Lets say customer facing vs internal) and then show them separately on a gantt, this would be very beneficial. Would that benefit you as well? Thanks, Linden

[DavidCo3]

Hi Linden, Apologies for the delay as I have been on a good long holiday... I do like your idea of being able to have multiple milestone paths on a project and then selecting which you want to use when reporting. Whilst not quite the same, I actually submitted a feature request for a Visio-style Timeline view of a project. Details are in the thread below: https://community.workfront.com/p/fo/st/topic=39&post=1930#p1930 Regards, David