Is there a way to show project phases in a gantt bar format?

Jeff Casto

Hello,

We have projects that are happening at the same time. We'd like to see how they overlap in a gantt format if possible. Currently, we see the solid blue bars that say the projects are happening at the same time, but, we are aware of that already.

We would love to see what phases are happening during these projects so we can see not just THAT the projects are happening, but what phase the project is in so we can better understand the status of each and how their status will impact the status of the other projects.

Here's a screenshot of the report we have, but it doesn't say anything other than the projects are happening…

It would be great to see which projects are in the beginning "Strategy" phase or the middle "Creative Concepting" phase or the end "Production" and "Trafficking" phase.

Our project sheet templates break out the project by these phases…Screenshot here

Does anyone have any advice as to if we can see this in a gantt or if there may be a better way to visualize this information as a metric sheet or anything???

Many thanks for any help!

jmyzk_cloudsmart_jp

Hi @Jeff Casto

Below is a solution that uses a row report whose base sheet has conditional formatting for the [Project ID] column and has a [Name & Phase] helper column to show in the Gantt.
The report only displays the project sheet's top parent row. Unfortunately, if you set "Dependencies Enable" in the project setting, you can not set conditional formatting for the parent's Gannt bar.

https://app.smartsheet.com/b/publish?EQBCT=14f494b34a49408783866e422bf24194 (Link to published report)

In the project sheet, the phase value is obtained from the Sheet Summary field with the same name in the first row of the Phase column.
You can set a project's ID, Name, and Phase in the files.

[ANC] =COUNT(ANCESTORS())

[Phase] =IF(ANC@row = 0, Phase#)

[Name & Phase] =[Task Name]@row + IF(ANC@row = 0, ": " + Phase@row)

https://app.smartsheet.com/b/publish?EQBCT=14122de5c63b43f39023c26c230ed701 (Link to published sheet)

Using the same-name Sheet Summary field in each project sheet, you can use the Sheet Summary Report.

The project sheet has [Project Name & Phase] filed to show in the report's Gantt. (The Project ID is not possible to color as it is a Sheet Summary field.)

[Project Name & Phase] =[Project Name]# + ": " + Phase#

https://app.smartsheet.com/b/publish?EQBCT=240dd1c25e6a42739079ac3a8b8603b2 (Link to the published report)

The last solution below looks best but takes time to set up as you have to create multiple ranges from each project sheet. As shown in the image below, Project ID and Phase get their values from each project sheet with a formula like this;

=INDEX({Project Apollo Range : Project ID}, 1)

=INDEX({Juni Project 1 Range : Project ID}, 1)

The hidden columns, Start and Finish, also get value from each project sheet.
So, for each project, you have to create five (name, id, start, finish, phase) ranges. So, though this solution looks nice, it is not scalable.

https://app.smartsheet.com/b/publish?EQBCT=14f494b34a49408783866e422bf24194 (Link to the published sheet)

Jeff Casto

Thank you @jmyzk_cloudsmart_jp ! These are very interesting. I will have to look deeper into them. I'm curious if you may have a more elegant solution to showing overlaps in project phases? One of the goals is to make sure we have the proper resources available for they type of work we need to do (Creative people during creative dev phase, Production people during production phase) as well as to see if there are too many Client meetings all happening in the same week or too close together, which would stretch our presentation resources.

I know this question is goin above and beyond the one for which you provided so many great solutions, but, I thought if it wasn't too much of a pain, I'd ask.

Thanks again for the great help!

jmyzk_cloudsmart_jp

Hi @Jeff Casto

The revised Project Row Report shows the task information of the [Assinged To], the project's phase specialist.

https://app.smartsheet.com/b/publish?EQBCT=14f494b34a49408783866e422bf24194&_gl=11s4pvoz_gcl_auMTcxMDExMzc3Ni4xNzIwNjU5MTc3_gaOTgxMjE2MjAyLjE3MTI4ODIxMjE._ga_ZYH7XNXMZK*MTcyNDE5ODU4MC4zOTEuMS4xNzI0MTk5Mjk2LjYwLjAuMA..

The Juni Project 1 is in the Creative Concepting phase, as shown in the image below.
If you look at the third sheet, "Phase Specialist", we found demo cloudsmart is the specialist for this phase.
With the following formula, the 2nd sheet checks the [Phase Specialist] column;

=IF(HAS(COLLECT({Phase Specialist Range : Name}, {Phase Specialist Range : Phase}, Phase#), [Assigned To]@row), 1)

https://app.smartsheet.com/b/publish?EQBCT=11fe5f52455e4e9b93b29f8f6bc925f3

https://app.smartsheet.com/b/publish?EQBCT=cd34d410d79c446c9fe6a91cec88d8e3

Using the checkbox information as a filter, the report the task information of whose [Assinged To] is the phase specialist.

If we do the same for other projects, we can show the task information of the most relevant [Assinged To] for each phase.

Jeff Casto

Thank you @jmyzk_cloudsmart_jp , I will chew on all these for a while. Many thanks again!