Formula for grouping in a timeline report

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I manage a Project Plan application. There are three tables - Project Phases, Deliverables and Tasks. Phases have Deliverables, Deliverables have Tasks. 

In order to make the records show up in the appropriate order I have a numerical field named Sort Order. This works great for Grouping and Sorting in table reports in the correct chronological order. 

I am trying to build a Timeline report now, and when I Group by Phase Sort Order, Phase Name, Deliverable Sort Order, Deliverable Name, the grouping headers take up too don't look good/are confusing. 

I tried a few different formulas combining these values but it wouldn't sort numerically after that. 

I attached an image of how it looks now. Ideally, all four values will show up on one line, and sort numerically based off the Phase and Deliverable Sort Order numerical fields. 


Photo of Heather Bryant

Heather Bryant

  • 30 Points

Posted 4 years ago

  • 0
  • 1
I think that you were on the right track to combine the fields.  I'm not quite what you want to Group by and what you want to just Sort By, but I'm guessing that you want to Group by the Phase and then sort on the deliverable, but both in your desired sequence.

So make a new field called [TimeLine Phase] with the formula

List(" ", Right("00" & totext([Phase Sort Order]),3), [Phase Name])

so that should read like 003-Dev of Future Services in Contract

and then similar for the [Timeline Deliverable]

List(" ", Right("00" & totext([Deliverable Sort Order]),3), [Deliverable Name])

You should then be able to Group by [TimeLine Phase] and sort by [TimeLine Deliverable]

Photo of Heather Bryant

Heather Bryant

  • 30 Points
Thanks Mark! That worked perfectly. I added a screenshot so you can see how nice it looks
Yes, it's beautiful now. Thx for letting me know.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

I have a bit more challenge. How do I show the grouped projects with earliest and latest start and finish dates.  Currently, if I group, these columns are blank.