Report filter help

Christine Brown (SAI)

HiQ I'm using a report to pull tasks from several different sheets with project schedules.

 

As a project manager planning a sprint, I want to filter the report to include:

1. Tasks scheduled to be started or completed within the current sprint
2. Due Date / is between / [start and end dates of sprint]

OR

1. Start Date / is between / [start and end dates of sprint]

OR

1. Tasks that are overdue from a previous sprint
2. Due Date / is less than / [start date of sprint]

AND

1. Status / is not equal to / Complete

 

We don’t have the option to relate condition groups 1 and 2 as an OR rather than the overall AND that applies to all condition groups.

 

Desired result:

 

 We tried adding all 4 of these conditions to the same group, but then Smartsheet forces the two “Due Date” conditions to be subgrouped together – there is no way to have 1a/1b and 2a/2b as the subgroups.

Can anyone please help?

Thank you!

 

Paul Newcome

I would suggest creating a hidden helper column on each of the sheets (I personally would use a checkbox type). You can then enter an IF formula into this column to check the box for any row that meets your specific criteria then build your report off of this checkbox.

I believe this should be a good start to the formula:

=IF(Status@row <> "Complete", IF(OR(AND([Due Date]@row >= [Sprint Start]@row, [Start Date]@row <= [Sprint End]@row), [Due Date]@row <= [Sprint Start]@row), 1))