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.

Find more posts tagged with

Accepted answers

All comments

Julio S.

Hi @BO'Neil,

It seems like you are trying to insert new column information to all of your projects so that this can be scoped in your Report. Have you tried running an "Add a New Column" Global Update? This would allow you to implement the desired Columns in your projects at scale. However, you'd either need to create a formula that would automatically assign values to each of them (from the Global update setup) or, otherwise, these would need to be manually inserted, especially if they are meant to be linked to existing data in your Intake sheet.

I've also noticed that your Report is currently scoping 1362 sheets. Although this may be run with no issue at the moment, I'd like to advise that Reports can stop working normally after more than 250 sheets have been scoped. If you'd need to report only on active projects, you could potentially create an archive rule that would archive inactive projects so that your Report would only scope them. Here you can find more information about Archiving in Control Center if this sounds like a possibility.

I hope this can offer some guidance.

Cheers!

Julio

BO'Neil

Hi Julio,

Thank You for taking time to provide some guidance.

I did use the global update feature to add the new columns. I manually preformed the cell linking from my intake sheet to columns in existing projects and I updated my blueprint to address new projects.

Regarding the volume of scoping sheets. Each project created from my blueprint has several supporting sheets, (active project workspace --> Project Folder ---> several SCC created sheets). I'm looking to roll up specific row information from two sheets in each project folder so I can get a consolidated view of all active projects. Granted my source includes 1300+ sheets but my filters get the final results below the 250 sheets. I have other reporting needs (and limitations) that prevent me from archiving any of the 1300+ sheets. I hope I don't run into reporting issues. Thank you for sharing the known sheet limitation challenges. However, until I address other reporting changes (which may require a major blueprint redesign) or receive other options from the community, I may take the chance of going with the large scoping set.

Again thanks for providing some insightful info.

Barry

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.