Metric Widget on Dashboard - use a saved report

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!

Best Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @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.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/06/22

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


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • TaliRXM
    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
    Jeff Reisman ✭✭✭✭✭✭

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • TaliRXM
    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
    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • TaliRXM
    TaliRXM ✭✭✭
    edited 01/06/22

    @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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @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.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • TaliRXM
    TaliRXM ✭✭✭

    Thanks much Jeff!

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

    Just was about to email you


    Have