Help With Collections

[sydneyp50459827]

Hi Community, My manager came back from Leap with a lot of information - she tasked me with some post leap to-do's based on her takeaways and what she thought could be useful to our organization and how we use WF. One of my to-do's is to learn about collections (advanced reporting / text mode) that could, from what I understand (which is very little without the whole context) allow us to report on PROJECT AND TASK data within a single report. I can't find anything on the help site about this, but I'm hoping there's a reporting wizard out there that can shine a light on how I can use collections. -How Can We Use Collections - Notes / Scenario: -We have a dashboard that has 4 project reports and 1 task report. -We have to pull 1 task report because we have set up some of our email projects as a master project - where we have multiple send dates in a single project , versus 1 project for every send date. -We've created sets of parents tasks for each individual send date , and the tasks underneath each parent represent the work required to deploy that single email. -We have to set the planned completion date of the whole project as the last send date of the whole series , otherwise, we can't build out the whole schedule correctly. -Now, if we pull a project report, it doesn't give us the individual send dates, just the planned completion date of the whole project. (we report on variance, and this kind of makes for wonky data) -What we're currently doing is pulling a task report with a column for: "default baseline > planned completion date" - for tasks that contain the word "Send Date" then we are comparing this baseline to the task "planned completion date" -Is there a way to use collections so we can still pull a project report, but somehow combine task and project dates into it? This way all of our reports are structured the same way and we don't have to use default baseline task dates? I hope I haven't lost everyone out there in the community! I'm happy to explain this visually if needed! -Sydney

[NateBa]

I'm genuinely excited to see what happens with this post... -Nate Bagley --- Workfront Community Manager - Work Smart, Work Happy Message me directly at:

[KatherineHa1]

We do reports for daily status, where we have one line per project, with a collection of tasks. The attached example has two columns with task collections: one for 'tasks due today' and one for 'tasks due next week'. A limitation is that you cannot sort the tasks in the collection by due date - which is annoying. They are pulled in randomly. You might be able to add a task level custom field to tag certain tasks as milestones, and then only include those tagged tasks in your collection by referencing the custom field? Hope this is helpful. Tasks For Today: displayname=Tasks For Today listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF({numberOfChildren}=0,IF({plannedCompletionDate}<$$TODAY+1d,IF(ISBLANK({actualCompletionDate}),CONCAT({name}," (",{plannedCompletionDate},") // ")))) valueformat=HTML Tasks For The Next Week: displayname=Tasks For The Next Week listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF({numberOfChildren}=0,IF({plannedCompletionDate}>$$TODAY+1d && {plannedCompletionDate}<$$TODAY+1w,IF(ISBLANK({actualCompletionDate}),CONCAT({name}," (",{plannedCompletionDate},") // ")))) valueformat=HTML

[DavidCo3]

Hi Sydney, Katherine's answer is obviously fairly tailored to your specific example. In terms of general knowledge on how to use Collections, I previously made the following notes: How to use a collection (this example is to list all assigned Job Roles for each User in a user report): View the API Explorer at https://developers.workfront.com/api-docs/api-explorer/ Find the object you want to get the list of items relating to (project, task, issue, user etc). Click on the Collections tab. Click on the data type you want. Copy the API Key into the green field below: Click on the URL to the collection object and click on the field you want and copy the API Key in the blue field below. Add a new column to a view or report and paste the below in text mode. Note that the report needs to be of the same type as the object you chose in step two. displayname=Call this whatever you like listdelimiter= listmethod=nested( roles ).lists textmode=true type=iterate valueexpression={ name } valueformat=HTML Cheers, David

[Doug_Den_Hoed__AtAppStore]

Hi Sydney. In addition to the Katherine and David's suggestions for collections, I invite you to review my Project Status Report blog post, which will walk you through creating an external page dashboard that will present a number of aspects about a Project (namely: Project info, Milestones, Issues, and Tasks) into a single spot. It is a primitive version of what we now offer as Magic Reports , whereby we develop a made-to-measure custom report with whatever data, logos, colors, logic, and graphics you need. Regards, Doug

[sydneyp50459827]

Hi David, I think I'm getting stuck on the value expression - would you mind posting an example using tasks and the planned completion dates for the tasks? Are you saying that if my object is task, I need to be in a task report, not a project report? I've taken all three advanced reporting courses, but the API explorer is still pretty much a mystery to me. Most of the code I need for our reports and dashboards, I've just memorized at this point. Thanks for your help! (and yours too Katherine!) -Sydney

[DavidCo3]

Hi Sydney, Try adding this as text-mode into a new column on your Project report: displayname=Project Tasks listdelimiter= *** listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=CONCAT({name}," - ",{plannedCompletionDate}) valueformat=HTML This will list all tasks and their planned completion dates for each project within the project report. As Katherine mentioned however, the order of the items in a collection is pretty random and you can't sort it by date unfortunately. There is however a way to filter the items it shows but it just ends up leaving gaps and looks messy. Anyways, see how you go with this! In terms of understanding the API Explorer, it helps to think of it in terms of the various main object types (Projects, Tasks, Issues etc). If you look at a Project Object in the API Explorer, and if you are using a Project Report then you can use the normal Project API keys shown on the fields tab. However, if you are using something from the Collections tab (e.g. tasks) then you need to use the field names / api keys from the relevant object type (e.g. a Task) for the valueexpression. Have a play around and let me know if any queries. I'd be happy to have a quick remote screen session if you like. Regards, David

[Doug_Den_Hoed__AtAppStore]

David, if you (or any of us) do invest some time touring others around the API Explorer, I suggest we record the sessions and post them here, al fresco: it would make for a revealing introduction that I'm sure future generations would appreciate. Regards, Doug

[MingDi]

Thanks, this really helps.

[sydneyp50459827]

Hi David, I'm pretty psyched to say that worked! There's no way I would have come up with that text mode on my own so I appreciate your help and time! While I do have more questions (like how to filter once in text mode, "only show me tasks with the name "scheduled to send") for our email projects, I don't want to drag this thread on or give you a larger project. I think my email is visible to others via my community profile, so if you did have some time to show me a few more text mode tips and tricks for my specific scenario, feel free to contact me! (no worries if you don't) Thanks again and it looks like others might have found this helpful, so that's great! The WF Community rocks! -Sydney

[NateBa]

I knew something magical would happen here! -Nate Bagley --- Workfront Community Manager - Work Smart, Work Happy Message me directly at:

[Mohini__Mini_Si]

Nate - no magical GIF?

[NateBa]

Of course! What was I thinking?! This one feels right... In Reply to Mohini (Mini) Sinha:

Nate - no magical GIF?

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

[Mohini__Mini_Si]

PERFECT!!! hahahahaha

[sydneyp50459827]

Ok, not to break up the fun but...I have more questions! I have the collection of tasks (task name) as a new column in my project report. I also have the collection of planned completion dates for the tasks as a new column in my project report. (Thanks, David!) NOW, Is it possible to do the following in text mode? (using filters?) -Only show me tasks that equal (or contain if that's easier) " Scheduled to Send " OR " Approved List Ready and Posted " (we have list projects assigned to people on our data team that are structured the same way as our email projects and pulled into the same report / dashboard) -Only show me tasks where the planned completion date is Less Than Equal $$NOW+31d I can't seem to attach the screenshots here right now, but I have them for when it's working again. This might be too much text mode for one report, but maybe not! Thanks! Sydney

[sydneyp50459827]

I was able to attach the PDF to my original post for reference. Thanks again and Happy Friday! -Sydney

[DavidCo3]

Hey Sydney. I couldn't see your email address so have sent you a connect on LinkedIn. Cheers Sent from my HTC David Cornwell PMO Quality Assurance Analyst M: +61 422 453 599 E: david.cornwell@pentanasolutions.com [ http://www.pentanasolutions.com/media/images/Pentana-Solutions-Sig.jpg] http://www.pentanasolutions.com/?utm_source=Emailsignature&utm_medium=Email&utm_campaign=Emailsignat... [social] https://plus.google.com/b/102545672428646020884 [social] [social]<>[social]<>https://au.linkedin.com/company/pentana-solutions>[social]<>[social]<>http://www.pentanasolutions.com/?utm_source=ES&utm_medium=emailsig&utm_campaign=emailsiggeneral Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. ----- Reply message -----

[AnthonyIm]

Hi Sydney - Just remember when you start filtering out items in a collection, you will have blank spaces if you continue to use a paragraph break. As Kathy mentioned above, we use " listdelimiter=" and then we put // or || in the concatenate formula. It reduces white space but still gives you an option to find and replace in Excel. (Paragraph break in Excel is Ctrl+J)

[NRYNR]

Hi David, I'm not sure why the following isn't working when trying to return a list of Project Users in a project report. Any ideas? displayname=Project Users listdelimiter= listmethod=nested(projectUsers).lists textmode=true type=iterate valueexpression={userID} valueformat=HTML

[DavidCo3]

HI Narayan - I have the same issue with that one….perhaps someone else has an idea why? David Cornwell PMO Quality Assurance Analyst M: +61 422 453 599 E: david.cornwell@pentanasolutions.com [ http://www.pentanasolutions.com/media/images/Pentana-Solutions-Sig.jpg] http://www.pentanasolutions.com/?utm_source=Emailsignature&utm_medium=Email&utm_campaign=Emailsignat... [social] https://plus.google.com/b/102545672428646020884 [social] [social]<>[social]<>https://au.linkedin.com/company/pentana-solutions>[social]<>[social]<>http://www.pentanasolutions.com/?utm_source=ES&utm_medium=emailsig&utm_campaign=emailsiggeneral Pentana Solutions accepts no liability for the content of this email, or for the consequences of any actions taken on the basis of the information provided, unless that information is subsequently confirmed in writing. If you are not the intended recipient, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[AdinaPi1]

Try this: displayname=Project Users listdelimiter= listmethod=nested(projectUsers).lists textmode=true type=iterate valuefield=user:name valueformat=HTML