Cumulative record of live sheet summary reports

Madeline

Hi everyone,

I have a sheet summary set up on every project, which tracks a number of weekly KPIs - e.g total tasks that week, total completed that week etc.

The sheet summaries feed into a larger sheet summary report which shows these KPIs, but only for this week.

I'd like to capture the info in the sheet summary report at the end of a week and add this to a larger, cumulative record, and be able to see over a project lifecycle how these KPIs were achieved long term.

Is there a way I can do this?

I'm currently stumped as I cannot create a workflow from the sheet summary report to move this information into another sheet, that is where my first instinct was.

Any help would be appreciated

Ryan Sides

Hi @Madeline

I created a "History" sheet that captures my roll up metric data at the end of each week. I can then report against that weekly data. I recently recorded the same explanation here...

https://www.youtube.com/watch?v=7GfWkW4Mvnk

Let me know if that makes sense and if that solves your same problem.

Madeline

Hi @Ryan Sides

Thank you, this looks like it's exactly what I need.

One more question, the metric data I use lives in the sheet summary only, as well as various summary reports.

In order to replicate the workflow you linked, I will need to move the data out of the summary and into the sheet somewhere, correct? From research it looks like I can't directly reference the summary from this metrics sheet.

Thanks,

Madeline

Ryan Sides

@Madeline correct, you can't reference sheet summary data from another sheet.

You can leave it in the summary if you want, so your existing reports, dashboards, etc. can stay connected. I would create a separate metrics sheet that just summarizes the same data in the same way; the only difference is the data is captured on a row in the metrics sheet.

raghurajsharma

Ryan, Thanks for sharing details. I need some help here. I am implementing PMO template and at portfolio level I created a "Portfolio Sheet Summary" report. Project managers will update their projects on "Sheet Summary" at "Project Plan" level and from there data is pulled in to "Portfolio Sheet Summary" report. I want to cumulatively copy the data of all the projects from "Portfolio Sheet Summary" report to another sheet ("Portfolio Sheet Summary - History"). From the video you shared I understand the Automation, but for report it does not show me option to automate.

Ryan Sides

https://community.smartsheet.com/discussion/comment/412596#Comment_412596

reports do not have automations. You may want to recreate your report as a sheet. Build the formulas to pull the data from the Project sheets into your “Portfolio Report Sheet”.

But this will not scale well as you add projects.

if you are using control center, you can add automations to offload these to a history report automatically.

Alternatively , you can build a project level sheet that is structured the same as your report and have it automatically send the rows to a history sheet. Then you can build a separate report that looks only at the history sheets. This way, you have one sheet and one report that looks at current data. And another sheet and report that looks at history data.

do those options help?

raghurajsharma

Ryan, Thanks a lot for prompt reply. I will try the last option you suggested. Appreciate your help!