Dashboard

Roxanne Basgen

Created this in my Metrics sheet, but on dashboard, it looks like this:

What am I doing wrong?

Brian_Richardson

Did you double check the chart range? That error typically means it cannot find any data to chart…ie your range might only have the names or something.

One pro tip also is to setup a report that pulls the status rows and columns, then chart the report instead of a range on the sheet. The reason is…charts always chart the entire report, so you don't have to adjust ranges later if you adjust your metrics.

Roxanne Basgen

Hmmm I see what you mean, but it's a schedule health field, I have red/yellow/green so I wanted to provide to the Higher Ups the count of what's in green/yellow/red? How best to do that?

Brian_Richardson

Couple of things you can do.

One easy thing is to setup a report that looks at the entire workspace or workspaces where you have the project sheets that have status on them. If you select the workspace instead of individual sheets, then anytime a project is added it will auto-add to the report.

Once you are pulling in all your project sheets, filter to show only the row that has the status on it. Maybe by name or something, and include the status column.

Then group the report by status, and add a Summary to count the status column.

Finally, add a chart widget to your dashboard and chart the report. And voila you will have a chart showing status of your projects, which will automatically include new projects, and shows status.

If you set the widget to Open Data Source, when people click the chart they will go to your fancy new report and be able to see more details about which projects contribute to the number, rather than just a block of numbers on a sheet.

Roxanne Basgen

is there a video link showing me how to do this? It may be easier to watch a video than to read it since I'm only 3 weeks into learning how to navigate Smartsheet?

Brian_Richardson

https://smartu.smartsheet.com/surfacing-project-details-with-dashboards-elearning

https://smartu.smartsheet.com/path/2023-project-management-path-elearning

If you have access to Smartsheet University, log in and go through the Project Management track (second link) which includes the session on dashboard (first link).

Brian_Richardson

There's also some best practice webinars that cover these topics:

https://help.smartsheet.com/learning-track/best-practice-webinars/visually-display-your-data-dashboards

https://help.smartsheet.com/learning-track/best-practice-webinars/reports

https://help.smartsheet.com/learning-track/best-practice-webinars/build-effective-dashboards

There's also some articles around using Summary Fields to capture a metric like status, as well as using chart widgets.

https://help.smartsheet.com/articles/2476786-define-your-work-with-sheet-summary

https://help.smartsheet.com/learning-track/level-3-advanced-users/charts-dashboards

I don't know of an article or video that specifically walks through what I described above, but you can do the following in steps:

1. If your status information is on a row in multiple project sheets, then click Create….Report…Row Report. If your status information is in a summary field on multiple project sheets, then click Create…Report…Summary Report.
2. In the report menu, it should open up automatically to ask you to pick the sheets to report on. Browse to find the workspace that holds your project sheets and select it. If you have multiple workspaces with project sheets in them, select them all.
3. In the columns menu, add the column that has your status info, whether that's a symbol or a word, doesn't matter. You can add other columns of interest too if you want to.
4. Click the Filters menu and use some filters to ensure you only see the status information. If you're using a Summary field, this is pretty easy, just need to filter to ensure that your status isn't blank. If you're using Rows, this is a little harder - find a combination of filters to pull in only the top row or wherever you have the status information for each project. You may also need to filter the Sheet Name to pull in only the sheets that have status on them, and not other sheets like RAID or RACI or whatever else you have in those workspaces.
5. Click the Group menu and group by Status
6. Click the Summarize menu and select Status, then choose Count.
7. When all that is done, you should have a report that shows you the status information for each project, one project on a row, grouped together by the status (so…all the red projects shown in a group, all yellow, etc), with a count of the number of red, yellow, green projects at the top of each group.
8. Now, on your dashboard, add a chart widget. Click Add Data on the chart widget and browse to find the report that you just created.
9. You should now see a chart with your status counts on it, which you can change to pie, bar, etc and format to your liking. In the chart widget menu, select Widget Behavior and then select Open Data Source