recommendations on how to solve a reporting problem
I'm looking for recommendations on how to solve a reporting problem.
I’m looking to build a consolidated report showing all open risks and issues of active projects.
Each active project has it’s own folder and each folder has multiple supporting sheets
My “active project workspace” contains in-flight project folders of active projects and completed (active for reporting) projects. (~1300 total sheets)
I can’t move the completed projects to another workspace due to different reporting constraints
I want to filter the new consolidated report by Active projects then by
- Risk Log
- Issue Log
- Status = Open (at the row level in the respective sheets)
- All with a defined list of column information from each report displayed at the row level
I want to group the report by:
- Status = Active (this value is on the In-take Sheet and project charter – not currently in the risk log or issue log so would need a way to add and filter)
- Customer Projects name (this value is on the In-take Sheet and project charter – not currently in the risk log or issue log)
- Sheet name [hyper linked so user can open the source sheet]( defaults with Issue Log / Risk Log – Blueprint assigned project number)
The only way I have been able to get my needed data into my consolidated report, is to add new columns of Project status and Customer Project Name to both the Issue log and the risk log and then
Populate each row of each report with the value of Active and Customer project name. (manually completed on a few project sheets to validate the report information)
- I'm filtering by Sheet name with a:
- Contains -Risk log
- Contains - Issue log
Retrofitting the above for over 100 sheets and getting the blueprint aligned for new projects, would be a large lift.
I would want the report (and row values) to be synced with the Intake sheets values (for changes to project status and project name)
I don’t want to have to update and maintain all these status changes via cell links on all the log sheets and at each row
I was hoping there are better options to construct the comprehensive report without the level of effort I’m looking at above.
Any design recommendations would be welcome.
We have an Enterprise plan with control center / blueprint design
Note: My blueprint design for project management includes both a risk log in an issue log and a project in-take sheet. Along with a number of other supporting sheets which account for the ~1300 sheets mentioned above.