How can I automate dashboard charts?

Hi everyone, I'm fairly new to Smartsheet and looking for some advice.

I'm in the process of building a project portfolio dashboard and would like to automate a 'Workload Distribution' chart. Currently, I have to manually update a separate metric sheet that serves as the data source, however, with how frequently the data changes, maintaining it manually isn't really practical. Is there a way to automate this?

Thanks in advance for any help!

Find more posts tagged with

Dashboards

Automations

Project Management

Portfolio Management

charts

Accepted answers

Paul Newcome

@c.reynolds_SP The answers for this are scattered throughout the Community, and there is also a link in my signature to register for the 10xTimeSavers group. That group has access to a 3 part master class involving the COLLECT function that goes over this very scenario.

Here's a quick rundown to consolidate everything for you…

Starting with your metrics sheet, insert a text/number column called "Number". Manually fill this column in starting with the number 1 and increasing as you go down the rows until you have entered as many as you think you will need plus a buffer. So if I think I will never need to show metrics for more than 10 people, I would go to 20 just in case.

The next column over will be called "PM" and use this column formula:

=IFERROR(INDEX(COLLECT({Source Sheet PM Column}, {Source Sheet PM Column}, @cell <> ""), Number@row), "")

Then the next column is your COUNTIFS.

What this does is give you a dynamic list of every PM on your source sheet that is in use. If you add a new PM to the source sheet, they will automatically be added to the PM column in your metrics sheet. Removing one from the source sheet also automatically removes it from the metrics sheet.

The next step is to create a row report. This will pull the PM and Count columns from your metrics sheet and be filtered to only show rows where the PM column is not blank.

This report then feeds the chart on your dashboard.

The reason for this is that when you reference a range in a sheet to build a chart, that range is static. This means you would need to go ahead and select all 20 rows in the sheet which provides for a rather odd looking chart if you only have a few people listed.

But… If you use a report to populate your chart, any rows added to the report are automatically added to the chart.

Paul Newcome

My apologies. I actually have a fix for duplicates that doesn't need the helper column:
=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet PM Column}, {Source Sheet PM Column}, @cell <> "")), Number@row ), "")

The DISTINCT function takes care of that.

All comments

JR90

Are you using cross sheet references on your metric sheet?

c.reynolds_SP

https://community.smartsheet.com/discussion/comment/490433#Comment_490433

Yes, currently pulling data from the master project list. It's a basic COUNTIFS formula

JR90

If the data in the data source (metric sheet) is updating automatically then so should the graph.

c.reynolds_SP

https://community.smartsheet.com/discussion/comment/490446#Comment_490446

It does, but what happens when PM F leaves or the company hires PM G? At the moment, I would have to manually remove/add PM's from the metric sheet. Is there a way to automate this?

Paul Newcome

Here's a quick rundown to consolidate everything for you…

The next column over will be called "PM" and use this column formula:

=IFERROR(INDEX(COLLECT({Source Sheet PM Column}, {Source Sheet PM Column}, @cell <> ""), Number@row), "")

Then the next column is your COUNTIFS.

The next step is to create a row report. This will pull the PM and Count columns from your metrics sheet and be filtered to only show rows where the PM column is not blank.

This report then feeds the chart on your dashboard.

But… If you use a report to populate your chart, any rows added to the report are automatically added to the chart.

JR90

@Paul Newcome this is genius!

Paul Newcome

Happy to help. 👍️

c.reynolds_SP

https://community.smartsheet.com/discussion/comment/490453#Comment_490453

Thanks Paul, really appreciate the help and looks to have solved it. I had to add a unique identifier column to the metric sheet to avoid duplicates in the row report. What happens in the metric sheet:

1	PMA	8
2	PMB	5
3	PMC	3
4	PMA	8
5	PMB	5
6	PMC	3

When I made the buffer, the metric sheet was duplicating the PM's (and subsequently the COUNTIFS column too) so I set it on the basis of how many projects that could be running at one time rather than how many PM's we could have. Either way, the unique ID column works - but I was just wondering if I have added an extra step which isn't needed?

Paul Newcome

The DISTINCT function takes care of that.

c.reynolds_SP

Thanks Paul, all working. Appreciate the help.

Marcela

Happy to see threads like this with such great examples of collaboration!

Paul Newcome

Happy to help. 👍️

MichelleWatkins_GPP

@c.reynolds_SP - while metrics sheets can be painful to maintain with lots of great solutions from other above, I thought I would weigh in on my preferred approach with metrics for dashboard charts.

After Smartsheet finally provided a way to group and total REPORTS, these also became sources for charts on dashboards.

My guide my team is to leverage reports for metrics/charts 1st because then anyone who wants to (a) validate (b) see/analyze the DATA supporting the chart/metric can click into the chart widget and open up that actual data rather than just a bunch of #'s and formulas that's hard to validate the accuracy.

Just group your reports in a folder 'reports for dashboards' to keep the workspace clean.

Before Smartsheet reports could group/total > the only way we could get metrics for charts for dashboards was the traditional metrics sheet.

Paul Newcome

@MichelleWatkins_GPP Yes. There are times where grouped reports can be used to generate chart data, but that is not always the case. Also, when using a metrics sheet to generate chart data, you can still have the chart interaction lead to a separate report instead of just the metrics sheet by selecting the "Smartsheet Item" in the wdiget behavior.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.