Remove the need for duplicate reports in a dashboard

KLoom

I have a sheet listing data from several different projects and would like separate dashboards created based upon the project data in the sheet. I can do this easily for one project and just add a filter for "Project = ##" to pull in a specific projects data. However I now have the conundrum of creating about 10 different reports for each project in my sheet to have that data show up on separate dashboards.

My solution is to:

• create a different set of reports per projects with the filter on project number
• re-point each widget in each projects dashboard to point to that projects' set of report

Has anyone set this up dynamically?

TY for any help in advance!

brianschmidt

I think this will depend on if you have Control Center as part of your plan. If you have control center, it can be set up with "template" items that rollup for each project, both generating project-specific sheets, reports, and dashboards dynamically from an intake sheet and rolling up to portfolio summaries to show/manipulate portfolio level data.

However, without Control Center, the only other way I can think of to do so more dynamically would be to have data in separate sheets so that each project has it's own base sheet. This way, you can put all the reports, dashboard, and base sheet into a folder. When making a new project, you can simply right-click the folder, save as new, and edit the base sheet to fit that project's needs. The new reports will roll up to your new dashboard automatically from the new base sheet.

Hope this helps!:)

KLoom

Could you explain how the new reports be able to automatically point to the new project? My reports currently have the Sheet reference "hard coded"....

Scott Orsey

HI @KLoom , I feel your pain. If you have a relatively small set of projects, and you don't plan to add any new projects, then your approach is probably the easiest. Just bite the bullet and build out your reports and dashboards.

However, if you are faced with a lot of projects or the list keeps growing, that solution is not very unattractive. I am not aware of a way within SS to do this easily. For example, wouldn't it be nice to place a drop down on the top of a dashboard that allowed you to select the project and then filter the dashboard ? Unfortunately, this isn't available.

I have tried a few solutions to this problem, and so far I haven't come up with anything elegant. Instead of building a dashboard, I create a sheet that pulls data related to only a specific project from other sheets. Then I use a form to change the project that it pulls. Maybe if I find an hour or two I'll jot down the how-to and post it somewhere for reference. Sorry I don't have a little more time right now.

Be well