Multi-tier reporting in Control Center?

Amy.Mizzi.RP

I am looking for a way to set up a report in my parent blueprint so that:

Every time a new child project is provisioned, a specific sheet from that project is added to the report at the parent level. The parent report is a collection of that sheet in all child projects.

Things I tried that didn't work:

-Adding the child sheet to the parent report in my template folder (newly created parent report looks at template child sheet and scope doesn't update to include its own new child sheet)

-Linking the blueprint template workspace to my parent report (like above, newly created parent report looks at blueprint template workspace and scope doesn't update to include its own new workspace)

-Dynamic Reports (seem to only work for portfolio-level reporting, not workspace reporting)

Does anyone know how to accomplish this through Control Center, or have other ideas to try?

Philip Robbins

Hi @Amy.Mizzi.RP,

I came across this limitation in multi-tier Control Center builds back in the autumn. There is no way I've found of enabling dynamic scope reporting at the mid-tier level, which I believe you are trying to do. Dynamic scope reporting only works for the very top level of your solution, regardless of how many tiers you have.

To pull roll-up data through the mid-tiers you need to route it via the summary sheet and then have a report in the blueprint that pulls from that sheet.

I have raised my concerns about this limitation with Smartsheet, as it makes it hard to justify going to the trouble of building multi-tier solutions. I thought there might be a way around it using Bridge, but currently there aren't any API endpoints that allow you to manage which sheets feed reports, so I hit a dead-end there too.

Sorry it's not better news.

Philip Robbins

Hi @Amy.Mizzi.RP,

I came across this limitation in multi-tier Control Center builds back in the autumn. There is no way I've found of enabling dynamic scope reporting at the mid-tier level, which I believe you are trying to do. Dynamic scope reporting only works for the very top level of your solution, regardless of how many tiers you have.

To pull roll-up data through the mid-tiers you need to route it via the summary sheet and then have a report in the blueprint that pulls from that sheet.

I have raised my concerns about this limitation with Smartsheet, as it makes it hard to justify going to the trouble of building multi-tier solutions. I thought there might be a way around it using Bridge, but currently there aren't any API endpoints that allow you to manage which sheets feed reports, so I hit a dead-end there too.

Sorry it's not better news.

Amy.Mizzi.RP

@Philip Robbins thank you so much for your response. I'm so puzzled, because we had briefly worked with Smartsheet's team on the first version of our blueprint, and nearly all its functionality was based on mid-level reporting. I've mimicked their report configuration (including entire scope of blueprint template workspace) and it hasn't worked at all.

I booked time with the Pro Desk next week to walk through it and reach a final conclusion, whether the news is good or bad. I'll follow up here afterwards. Thanks again!

Philip Robbins

@Amy.Mizzi.RP I'd love to be proven wrong on this one, but I spent a lot of time in the autumn working on a solution only to find that any sheets provisioned from the lower level blueprints were feeding into blueprint template reports and not provisioned reports. At that point I asked around my contacts at Smartsheet and came to the conclusion that what I was expecting/attempting wasn't possible.

Part of the issue is around how multi-tier is presented, as it sets this expectation that roll-up reporting can happen at any level. Technically this is true, but even some of the sales engineers at Smartsheet don't appreciate that there are two types of roll-up reporting: summary metrics and dynamic scope reports. Summary metrics can indeed roll-up through multiple levels (via summary sheets), but dynamic scope reporting only works at the very top level.

If the Pro Desk team tell you otherwise, ask them to demonstrate it to you on screen and ensure any sheets that are intended for mid-level dynamic scope reports get added to project reports provisioned from the top level blueprint, not the template report within the blueprint. And if they can show you that, please come back and explain how they managed to do it!

Amy.Mizzi.RP

@Philip Robbins I also would have loved for you to be wrong on this, but my Pro Desk session confirmed that this functionality doesn't exist.

I have no idea what to make of my experience with the Solutions Team and the Blueprint they built (which still to this day successfully creates several mid-tier reports).

But, I certainly can't recreate it in my own Blueprint, so I'm faced with either removing them completely or setting them up manually. Bummer! I will ping you if I ever stumble upon the secret solution.

Take care!

Philip Robbins

@Amy.Mizzi.RP I'd be happy to jump on a call so you can show me the blueprint doing its thing. I'm not saying I'll be able to unpick the magic there and then, but I remain curious to see it happening, as from what I've been told it shouldn't be possible.

Amy.Mizzi.RP

@Philip Robbins that would be great! Drop me an email with some times you have available? amy.mizzi@realpage.com

Gina Smith

@Amy.Mizzi.RP @Philip Robbins - inquiring minds are just dying to know if you two figured anything out with this. We are getting ready to build multi-tier blueprints and it would be great to confirm this or know what we might need to do differently. Are either of you available to help us understand how we need to create our intake sheets for multi-tiers? Does each tier get its own intake sheet? Do we need columns in each intake sheet to create a "relationship" between them? Any help would be appreciated as I cannot find it in help articles and we are taking the Blueprint Builder course and they don't cover anything multi-tier in the course. Thanks!

Amy.Mizzi.RP

@Gina Smith I stopped pursuing the mid-tier reporting when our needs changed, so I never figured that specific piece out. (This is specific to automatically adding child sheets to parent-level reports each time a new child project is created under that parent.)

I can speak to setting up the relationship between parent and child projects though. In Control Center, it's really just a few settings in each blueprint, on the first screen you see when editing, called "Blueprint Basics."

• For the Parent Project's blueprint:
  • select "Standalone / Top tier" on the Blueprint Basics screen, and make note of the Blueprint Name.
  • advance to the Intake Settings screen, and make note of which Intake column you choose to put into the Project Name Column field.
  • advance through the rest of the settings screens and save.
• For the Child Project's blueprint:
  • select "Linked to a parent project" on the Blueprint Basics screen, then select the parent project's Blueprint Name in the next dropdown.
  • advance to the Intake Settings screen, and be sure to turn on the "Show Advance Intake Options" slider.
  • The linkage between the tiers occurs on this screen, in the Parent Project Selection Column field. This is where you put the Intake Column that contains the Parent Project Name.
  • advance through the rest of the settings screens and save.

I'm attaching a very rudimentary diagram of the fields that need to match to create the tiered connection.

tiered blueprint setup.png

I do use a single Intake sheet for both, but you could also use separate sheets if that makes more sense for you. With separate sheets, you'd need the Parent Project Name to appear on both sheets to complete the linkage between the tiers.

Hope this helps, feel free to shoot me an email if you have more questions. :)

Gina Smith

@Amy.Mizzi.RP thank you so much! This is the piece that I was missing

"With separate sheets, you'd need the Parent Project Name to appear on both sheets to complete the linkage between the tiers."

I am currently in the Building a Blueprint Smartsheet course and the instructor told us we need to add "some columns" to our intake sheet but she could not remember what those columns were.

I do appreciate your taking the time to assist us!!

Philip Robbins

@Gina Smith I'm glad that @Amy.Mizzi.RP was able to help you out.

On the mid-tier dynamic scope reporting, this still isn't possible. However, I was at ENGAGE London this week and heard some of the plans for where Smartsheet wants to take AI. Specific to this issue is their desire to generate solutions based on prompts, which could only work if additional API endpoints were surfaced, particularly those for reports. In the medium to long term I expect to be able to set source sheets and filters in reports via the API, which would fully unlock multi-tier.

In the meantime, if you have access to Pivot App, it is possible to create more complex mid-tier metrics, such as rolling up multiple budget categories against a monthly timeline. Rather than having to create a summary field for each category/month combination and roll it up, you can feed your budget sheet into a top-level dynamic scope report and then create a pivot sheet off that. If you include the mid-tier name or ID in the pivot, you can then set up a mid-tier metric sheet that filters on it.

I'm going to be dropping a video relating to this on the Prodactive YouTube channel soon that demonstrates the principles of this, so keep an eye out if this is something you'd be interested in implementing.