Help on project status reporting solution requested!

bkw1962

Hi all -

I am tasked with the following and need some assistance and hope you can help.

Firstly, let me tell you up front that our organization does not have now. nor can we purchase Control Center, Dynamic View, or any other add-ons at this time. The good news is that my ask MAY not require any, but let me tell you my problem before we make that assumption. :-)

I am tasked with creating a SS mechanism for our 8 PMs to submit project information (name, type, sponsor, initial status, initial health and other initial information) for the purpose of weekly status updates as the projects progress. There are multiple projects within several Programs, and those multiple Programs are within our single Portfolio.

Leadership is to be provided a Portfolio view dashboard that shows key Portfolio-wide roll-up metrics and a list of the current Program names that they can select from and click which brings up a Program view dashboard that shows key Program-wide roll-up metrics and a list of all of the project names that are currently active in that Program that they can select from and click which brings up a dashboard view of the most recent weekly status report for that project. (It's a drilldown from Portfolio → Program → individual weekly project status report). The other feature they are looking to have to to ALSO have on the weekly status report view is a running clickable list of all historical weekly status reports for that project so they can easily choose and view any older report that is stored. That view does not need to be side-by-side with the current view - the historical report should replace the report they are already viewing.

The requirements for the solution are:

• No project plan sheets are involved in this initiative so no user (PM or executive) should be required to access any SS sheet or report directly.
• All project meta data and initial status information is entered through an intake form and updated weekly by the PMs using an Update Request workflow.
• The Master Project Status sheet should contain ONLY the most recent weekly report values for all projects in all programs across the portfolio. (It's built and it does.)
• The Archive Project Status sheet should contain ALL previous status reports for ALL projects. (It's built and it does.)
• The solution cannot require manual administration for adding/removing project or program data sheets, dashboards, or reports.

I am hopeful that because there is just a single sheet each of current and past project status data, (yes, I've included unique auto-number project IDs in each already in the existing data), this is easier than I am making it. 😊

Any and all information and suggestions will be helpful!

Thanks in advance!

BKW

kowal

Hi bkw1962,

but what is the question / issue here? It looks like requirements to create typical Smartsheet Solution for Project Management - does not look too complicated if you already built some solutions on Smartsheet.

Do you have any specific question / issue here we can help with?

IMHO it can be easily built without SCC & Dynamic View it will just need quite a lot of manual work.

bkw1962

Apologies of the questions were vague.

The questions are:

1. How can I build out a set of dashboards that does not require anyone to manually maintain or update the project status information sheet (or project status archive information sheet) even when new projects are added via the intake sheet.
2. Is it possible to build widgets for my dashboards with clickable dynamic lists of projects (as more are added and subsequently close out over time) that update solely based on data (not requiring manual updating).

I have been building SS solutions for over 5 years and have not encountered this before.

Thanks for any help in advance!

Ipshita

Hi @bkw1962 Just to add - if you are a licensed Smartsheet user, you now already have access to all the paid features such as dynamic view, data shuttle, pivot and many more great add ons.. now to answer your other questions -

1 a) Create a dashboard that derives data from a master project status report that changes as and when project status changes. You can easily achieve this by creating project templates with all the features you require - contact list, forms, project status column that changes as and when the project progresses (this is very easy to build) and then replicate this template for all new projects that get created. Save all of these projects in the same workspace

1 b) Create an automation workflow to move the completed projects to Archive. Create a separate projects archive folder to save these archive projects

2) The dashboard widgets should point to the projects that are in 'Active' status as well as the 'Completed' by adding the report and charts widgets that point to the project templates. Just make sure you change the source report for every new project being added.

P.S. there will always be minimal manual efforts involved for checks and balances.

Let me know if you need further help or have any other questions.

Hope this helps:)

Cheers,

Ipshita

bkw1962

Thank you for responding, however, your solution requires someone to manually "replicate this template for all new projects…". That is what I mentioned needing to avoid. Projects need to be able to be added solely via an intake form and NOT require ANY human intervention to have them be included in the program and subsequent portfolio level reporting dashboards. We have an intake form for new projects and an Update Request scheduled for each week to collect new and updated info, respectively. As this is not a project management process, per se, and JUST a project status reporting process, we are not using ANY project plan sheets. The only data we need for the individual project dashboards we collect weekly from the PMs via the Update Request forms.

That is why I think this may be much simpler than I am making it. I just can't nail it down, however.

Thank you anyway for thinking through it!

kowal

hi @bkw1962,

Now it's clear thanks!

IMHO it would be really tough to build a proper dashboard without Control Center to automatically add all the new projects info. But it's still doable - Instead of Sheet Summary that is crucial for Portfolio Dashboard I would create a Report that Reads Data from Sheet Summary and in the projects plan you will be using I would put most importand data into SheetSummary so this would be for question 1.

For question 2: Only Reports are partially dynamic (if you setup a filter to read from the sheets in the folder and then you add new sheet to the folder) but all the other Dynamic Widgets would be difficult to configure.

Your problem seems bit complicated to just explain with text :)

Ipshita

Hi @bkw1962 I see what you're saying and I am sure what you are asking is doable to a certain degree if not completely. Plus it would be simpler if only I could explain myself a bit better :) I know you don't want to replicate templates and avoid as much manual updates as possible. The project files (and that is what I meant by templates) are getting updated through the intake forms in the backend, But if you want to avoid this, may be create a Master Project Sheet that consists of all the projects starting with your primary column that mentions the Project Name then another column for Project Manager name, basically just the columns from your intake form. Now this master project sheet could have summary values that calculate the points you need for your consolidated dashboard (and you can create a summary sheet report as @kowal has rightly pointed out above) or you can simply create a row report to pull in the relevant data for your dashboard. I also agree with him on explaining your solution with just text:)

Here is an example of a master sheet that has an intake form attached to it and this sheet would populate data derived from this form. You can create all your reports and charts from this sheet and also create an automation workflow to move "completed" projects to an archive sheet.

Hope this helps? Let me know?

Cheers,

Ipshita