Portfolio Gate Reviews

crp321

I have 165 projects in the portfolio, each with 5 gates and ~120 steps in a project. As an example if a project has successfully passed gates 1&2 and is coming up on gate 3 review, I want to be able to see the rollup at the portfolio then "zoom in" to see the project plan details for a specific project and gate 3. With my current report 'gate 3' includes the population, those projects that may be active in gate 2, 3, and 4, but again require us to scroll past those projects we're not interested in if we're looking at the 'gate 3' report. The closest functionality we've been able to obtain is the portfolio "gate 3" report and looking at it in the calendar view and focusing on a specific week. I have helper columns in the project plan that denote hierarchy level, row number, step within the heirarchy. The goal is to hold an integrated gate review mtg on a specific week/day and cover 5-10 gate reviews across the population of projects and all associated gates. The gate review itself is the last substep within the gate. Reverting back to excel language; if I had planned and actual dates for the 5 gates; selecting 'a date' for actual gate 2 and 'blank' for gate 3 would give me all the open work in gate 3 across the portfolio. There are not any conditions in the smartsheet report to select an attribute thats complete for gate 2 and open for gate 3. using % complete filters at the step level.

Melissa Yamada

What you’re trying to do is a pretty common portfolio pattern, but it usually requires one helper logic column to define the “current gate.” Reports alone don’t have a way to say “Gate 2 complete AND Gate 3 not complete” unless that logic exists in the sheet first.

In Smartsheet, the cleanest approach is to calculate which gate a project is currently in, then filter reports on that value.

The idea is to create a column like Current Gate (or Gate Status) in your project sheet or summary. The formula checks your gate completion dates or % complete and determines which gate is active. For example, if you have actual completion dates for each gate:

=IF(ISBLANK([Gate 1 Actual]@row), "Gate 1",
IF(ISBLANK([Gate 2 Actual]@row), "Gate 2",
IF(ISBLANK([Gate 3 Actual]@row), "Gate 3",
IF(ISBLANK([Gate 4 Actual]@row), "Gate 4",
IF(ISBLANK([Gate 5 Actual]@row), "Gate 5", "Complete")))))

Now your portfolio report can simply filter where Current Gate = Gate 3. That automatically returns projects that have finished Gate 2 but haven’t completed Gate 3 yet. It eliminates the problem of seeing projects that are already in Gate 4 or still working in Gate 2.

For the “zoom in” piece, most teams then include a link to the project sheet or a report filtered by Project Name + Gate. When you click a project in the portfolio report, you jump directly to the project plan and can review the tasks under Gate 3.

If your gate review task is always the last child row in each gate, another helpful column is something like Gate Review = Yes/No. Then your portfolio report can show only those rows, which makes it much easier to run a weekly gate review meeting and quickly see the 5–10 projects hitting Gate 3 that week.

The key shift is:
don’t try to express the gate state entirely in the report filters — calculate it once in the sheet and let the report read that result. That makes the portfolio view much cleaner and removes the scrolling problem you described.