Metric Widget on Dashboard - use a saved report

TaliRXM

Hello,

When adding a Metric widget to a Dashboard and trying to 'select data' - select a Report, the report doesn't show up and available o be selected. Actually none of my saved reports show and available to be selected to be reflected in a Metric widget.

Thanks!

Andrée Starå

@TaliRXM

Apologies, I got confused. I was thinking of the Chart and Report Widget.

You're correct that the Metric Widget can't reference a Report.

Jeff Reisman

@TaliRXM your existing Summary Report is not going to work for what you need. Let me show you an example of how I collect metrics for display on our dashboards.

In our warehouses, we keep records of Bill of Lading, the status for each, the $ value, and who is working them. We collect this data in one sheet for each warehouse. For our primary warehouse this sheet is called BOL Active Tracking. We have dashboards on huge TVs displaying everyone's totals, how many are in each status, total $ closed for the day, etc.

In order to display these metrics, I have to first collect them from my sheet. For this I use multiple metrics sheets. For example, to collect the number of BOLs with In Progress status assigned to individual employees, I use a COUNTIFS formula that references BOL Active Tracking to show me how many rows are assigned to the Employee@row, which also have a Status = "In Progress".

This particular data I display in a donut graph on the dashboard, however in the metrics sheet I then total up all the counts of in progress BOLs I'm collecting for all the employees. It this TOTAL that I display in a Metric Widget:

So in a nutshell, use one new metrics sheet to collect the SUMs from your other sheets, total them up, and reference that total in your metric widget.

Jeff Reisman

Metrics widgets can only draw data from sheets, not reports. That's the problem!

Andrée Starå

Hi @TaliRXM

I hope you're well and safe!

Make sure that the report is Sheet Summary Report because it won't work with a Row Report.

Make sense?

Did that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

TaliRXM

@Jeff Reisman & @Andrée Starå Happy New Year and thank you.

What I want to display is a total of multiple sheets (for individuals' activities) that is summarized in a Report. Each sheet separately has a SUM running to calculate a total and the report sums all sheets.

How can I display this one total # if cannot do it via the Metric Widget???

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315165#Comment_315165

In many implementations here I've used a Metrics sheet to make all these calculations, collecting totals from other sheets. Then the metrics can be displayed together in the Dashboard widget or in individual widgets.

Andrée Starå

@TaliRXM

Excellent!

Happy to help!

I'd recommend either adding the Sheet Summary Calculations and then using Sheet Summary Report and referencing that in the Metric Widget, or creating a so-called Master Metrics Sheet and collecting everything from the sheets on it using cross-sheet references and/or cell-linking.

Would any of those options work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

TaliRXM

I need to display one single total number.

I also tried to use the Chart widget but it forces the whole report that breaks it to the individual totals. I can't chose only the one top level total for all.

I want to show only 35, but the whole report is being selected by default which shows 30 & 5

Jeff Reisman

Create a Metrics sheet.

On that sheet you can use cross-sheet links or formulas to pull in your SUM values from all your sheets. Then, in one cell, add up all those values, and that's your SUM total from all sheets.

Create your metric widget and reference that cell from your metrics sheet.

TaliRXM

@Andrée Starå I created a sheet summary report and that is the one that cannot be displayed using the Metric widget and when using a Chart widget it shows the whole sheet summary report by default.

I need to show one total number

Andrée Starå

@TaliRXM

Apologies, I got confused. I was thinking of the Chart and Report Widget.

You're correct that the Metric Widget can't reference a Report.

Jeff Reisman

@TaliRXM your existing Summary Report is not going to work for what you need. Let me show you an example of how I collect metrics for display on our dashboards.

In our warehouses, we keep records of Bill of Lading, the status for each, the $ value, and who is working them. We collect this data in one sheet for each warehouse. For our primary warehouse this sheet is called BOL Active Tracking. We have dashboards on huge TVs displaying everyone's totals, how many are in each status, total $ closed for the day, etc.

In order to display these metrics, I have to first collect them from my sheet. For this I use multiple metrics sheets. For example, to collect the number of BOLs with In Progress status assigned to individual employees, I use a COUNTIFS formula that references BOL Active Tracking to show me how many rows are assigned to the Employee@row, which also have a Status = "In Progress".

This particular data I display in a donut graph on the dashboard, however in the metrics sheet I then total up all the counts of in progress BOLs I'm collecting for all the employees. It this TOTAL that I display in a Metric Widget:

So in a nutshell, use one new metrics sheet to collect the SUMs from your other sheets, total them up, and reference that total in your metric widget.

TaliRXM

Thanks much Jeff!

This is exactly what I ended up doing yesterday after many other tries...

Just was about to email you

Have

Yasutaka Ito

It is surprising why Smartsheet still doesn't see the importance/need to be able to use the Report as data source. Why isn't that possible still?