Creating a Dashboard with non numerical data

I have a sheet created from a form that is essentially a Security Activity Report Form. Officers enter their daily duties such as Patrols, suspicious activity, emergency responses, etc.

I want to create a dashboard that will show how many patrols each site is conducting, how many suspicious activity reports, vandalism, etc.

right now I am exporting to Excel, creating a Pivot chart, and so forth. Not difficult but time consuming.

Is there a way to build the dashboard in Smartsheet? I have tried but it will only do it for numerical data, where as Excel will allow a pivot table/chart based on the number of entries in the drop down.

So essentially, is there a way to create and or link my main sheet to another sheet that will calculate how many of each event, in realtime then build the dashboard off of the sheet with the numbers?

If so what formulas do I use?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You can use a separate sheet with formulas, or you can create a row report that pulls in all entries, group the report, then use the Summarize feature within the report to get your counts. Then you can use this report to create a chart or graph on your dashboard.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You can use a separate sheet with formulas, or you can create a row report that pulls in all entries, group the report, then use the Summarize feature within the report to get your counts. Then you can use this report to create a chart or graph on your dashboard.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul, thank you for your response, you wouldn’t happen to know where I can find how to do that would you?

    And would those row reports update live or would have to keep pulling them?

    Sorry, I’m not new to Smartsheet just new to pulling data from here rather than exporting.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 04/30/24

    Reports will update live as the source data updates. Here's a link for more info:

    https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • you’re a lifesaver Paul. Thank you 🙏


    one last question because I can’t find a solution. Each one of my columns in my sheet have multiple selections from a drop down. In Excel when I create a pivot table I have to go in and sort the data by attaching another sheet that has all of the associated drop downs listed and I can sort/filter for each of the individual duties. For example: if I want to know how many vandalism activities has occurred at a site I can see that reflected in the chart (see pic) but I cannot for the life of me figure out the same work around for the Smartsheet


    in Smartsheet (bottom pic) it just gives me the total count of the column. Not the individual counts of the types in the column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot of the source data?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • source data is Smartsheet from a form created to capture these events. But no , I can’t screenshot the sheet due to sensitive information.