Metric Widget pulling data from a report

My objective is to create a dashboard where user can see the count of his/her tasks by status: On Track (green), Concern (yellow), At Risk (Red), and Completed (Gray).

Each status is a presented as a metric widget. When user clicks a certain metric widget (i.e. red - tasks at risk), a DynamicView window opens and pulls up the list of said tasks.

The summary dashboard will be accessible to users via Teams, as tab in the respective project’s Team.

I know how to make everything work, except for loading the count of tasks in the metric widgets. In my design, these counts are pulled in a report which has a Contact field filter set to Current User.

It seems a very obvious and ordinary use case, but I couldn’t find a solve for it. I hope somebody here may point me in the right direction.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @jmalaguez

    Currently details in a Report cannot be selected as data to be displayed in a Metric Widget on a Dashboard. (Please let the Product team know of your request by filling in this form, here!)

    You could display the numbers in the Dashboard by putting the Report data on the Dashboard through a collapsed Report Widget instead. Then you could set up 4 Image Widgets with the appropriate colours and links behind them to click on, separate from the data shown in the Report.

    Cheers,

    Genevieve

  • After much bending backwards, I've found a way to solve it. Instead of metric widgets, I'm using chart widgets linked to the underlying reports. I set them as half pie charts with labels showing the actual amount. It looks ok, not ideal, but at least it is functional.

    The problem that I'm facing now is, when the value from the report is "0", the chart shows an error. This is quite an eyesore. How can I solve this so the dashboard looks clean and professional regardless of zero values?



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @jmalaguez

    Currently Pie or Donut charts will display an error if the values to display are 0, as you've seen. There isn't a way to adjust what error message is displayed.

    What about using a Bar Chart instead, with one bar per widget? Bar charts will display 0, and you can still adjust the colour.

    Cheers,

    Genevieve

  • It would be great if I could hide the axis lines and labels, and put the series label at the base of the bar. But none of these are possible...

    Basically, I'm trying to make it look like a metric widget... wouldn't it be easier to simply solve it with a metric widget??

    This is major gap in your product. I've seen this is a recurrent topic in the discussion forums, so I'm not sure if putting another request in will solve my problem.

    Where is this improvement sitting in your backlog? Is it something users can expect to see solved in the next week, month, or year?



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @jmalaguez

    Grouping and Summary lines in Reports is still a fairly new feature, so it is good to hear your feedback on it and to understand how you're looking to use the data.

    I don't have an ETA for when additional updates will be released to Dashboard and Report functionalities, but our Product team does review all of the entries that come through the Feedback form. You can subscribe to the Release notes page if you'd like to be notified of new features as they come out!

    Cheers,

    Genevieve

  • I am following this because of some issues I am encountering. Maybe the solution to my problem is easier. Put on your seatbelt, here we go:

    I am working with a number of sheets that for for technicians working on projects. I am attempting to take the date from each of these 1 sheets and combine them, but only certain columns. I have accomplished this by creating a report which carries back the 6 columns I need.

    From there the report is filtered by date range. Under the current iteration, I have a report for the first quarter of the year by filtering by two critera... a range of dates and status of "complete". In the second quarter sheet I am using the same criteria, but I added another criteria where I am also filtering the date as "before today".

    Okay so now I have my information on a report. I want to take the information from this report and display it on a dashboard. To do this however, I need to further refine the data. For example, I have 11 technicians and I want to count how many jobs they did in that time period. I want to show how many of certain types of job were done in that time period (There is a column for that).

    The only way I can figure to parse the data down from there is using =counif formulas on another sheet. But I am not able to pull numbers from a report.

    IDEAS?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Chris Rolando

    After you've filtered the Report, what about using the Grouping feature to Group by the Job Type column, then use the Summary feature to SUM that column as well?

    You can use the first level of Grouping and Summary in a Chart. This means that you can Group by the Job Type to create numbers (across all of your employees).

    If you wanted to parse this data down per-employee, in this instance you would need 11 separate Reports (one per-person) which are then Grouped by Job Type, and 11 separate Chart Widgets side-by-side in the Dashboard.

    Or, yes, you could use a COUNTIFS to count the Job Type per person.

    Cheers,

    Genevieve

  • Ami Veltrie
    Ami Veltrie ✭✭✭✭✭✭

    @jmalaguez Can you please share with me how you got the semi circle pie to work with only one value? I'm using a report but it seems to require a 'grouping' in order to display correctly.

  • Ami Veltrie
    Ami Veltrie ✭✭✭✭✭✭

    @Genevieve P. Hello. How would I make chart with one bar (data point)? I'm getting this error when I try to use my grouped report:


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ami Veltrie

    If you're using a Report as the source, you'll need to have both Grouping and Summary applied to the Report. The chart will then take the first level as the source.

    If this hasn't helped, could you post a screen capture of your Report (but block out sensitive data)?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!