Reports: Sorting groups of rows by the end dates of their respective master rows

Chris Morin

Hello! I am hopeful that I can find the solution to this issue. This is a bit of a long one, so get comfortable!

 

I am using SS to organize the workflow of active jobs at our precision manufacturing facility. Each job requires almost 100 independent job steps from start to finish. Each individual job task is on a separate row. I have broken the workflow into phases, represented by task groups (etc. Sales, Quality, Manufacturing, with several tasks in each) to more easily keep track of job status as they move from one department to another.

 

I created a variety of reports that focus on individual departments (example: rows 25-40 = raw materials...one report, rows 45-60 = manufacturing...another report, etc.) This lets me see exactly what jobs are running through each department at any given time, helping us stay focused during production meetings. I also have a high level overview that I'm calling the Active Jobs Report, showing the status of all jobs in all departments.

The department-specific reports are designed using Who, with dummy accounts representing different departments, and also What, which includes only rows with certain status colors (green, yellow and red, to represent all current activity on any given step).

The Active Jobs Report is designed in the same way, but without the Who stipulation, thus showing all departments at once. In this report, I added an additional What stipulation, to include any row in the sheet with the symbol "$". The only place in any sheet where there is a dollar symbol is what we are calling the Master Row, which lists the value of the job. The Master Row also includes the master start and end date of the job, which I want to see at all times in the Active Jobs Report.

 

As a note, this problem affects all reports, but I will use the Active Jobs Report as my example.

 

I have sorted the report based on Sheet Name. The report neatly shows all currently active job steps grouped together by sheet/job, with the Master Row of each sheet/job at the top of the group. It is sorting alphabetically, which means that the sheet names/jobs are the main governing factor and not the end dates.

 

The Good: Jobs are well sorted and differentiated from one another.

The Bad: Sorting by Sheet Name leaves the job master end dates out of order. This is unacceptable, as due date is the primary motivating factor of what gets done in what order.

When I sort the report by end date instead of sheet name, the tidy groups of job-specific steps are torn apart, as the end date of every row is considered independently from all other rows. The reports do not recognize that I want to keep the job-specific steps together within their groupings and only sort by master dates.

 

Ideal Solution: As I have seen mentioned in several posts, a new feature that allows reports to honor task groupings would solve this problem and then some. I would be able to neatly roll up all the job-specific tasks into the Master Row of each job and go about my business sorting by date, or anything else, for that matter, without the tasks being thrown all over the place. I am unsure as to why that feature is taking so long to roll out, as I really feel like it's a huge hole in reporting functionality.

 

I am seeking a workaround that will allow me to sort job master dates, while still locking all job-specific tasks together with the Master Row. Changing the end date of all subtasks to the same as the master end date defeats the whole purpose of the Gantt chart, which is one of the main reasons why we invested in SS in the first place!

 

I have attached a few screenshots:

Screenshot 1 shows our job sheet template. All tasks have individual start and end dates and are configured into a complex Gantt flow.

Screenshot 2 shows the Active Jobs Report. The screenshot shows this report sorted by sheet name, which keeps each sheet/job distinct from others. The problem is that sorting this way does not sort by the master end date of each job, requiring us to browse around the report, which is confusing and time consuming.

Screenshot 3 shows the Active Jobs Report sorted by end date. Notice how the sheet/job-specific groupings are torn apart and all rows are sorted based on their individual due dates.

Please note that I had to black out customer names, hence the missing area to the left of screenshots 2 and 3.

 

Thank you very much in advance for anybody who can point me in the right direction!

Chris

Andrée Starå

Hi Chris,

Have you tried to sort by sheet name and then with end date so that it's combined?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Chris Morin

Andree,

 

When I do that, it keeps the sheets organized practically the same way as always, because the tasks that show up under each Master Row are in that order anyway.

 

I found a workaround and tested it out this morning...it seems to do the trick:

 

I added a row called Master End Date to the right of End Date, where I add the master end date of the job in every single row of the job sheet. In this way, each job sheet will have a column that has the date the job ends. Getting that bit of info into every single task lets me sort by Master End Date and it puts them in the right order. I can also sort second by Sheet Name, which will properly organize multiple jobs that happen to end on the same date.

 

This seems to work for now, but I REALLY want to see heirarchy in reports!!!

Andrée Starå

Great to hear that you found a workable solution.

I think that we will see a lot of improvements to the reporting in Smartsheet this year.

Best,

Andréee