Changing Dashboard Graphs via Report

Smartsheet Batmon

Hello Community,

Hoping there is an answer to this question. I know how to build a bar graph in smartsheets; but recently i was asked by a client if i can build them a bar graph that will change based on criteria they choose at any given time. For example:

Let's say we want to build a Quarterly Graph for 2022 (i.e. Q1, Q2, Q3, Q4) that displays the following:

• Open Items
• Closed Items
• Canceled Items

Keeping in mind that all my data for 2022 is in one sheet; I want to only filter for those 3 items during Q1 Jan-Mar 2022. This would give me the following numbers on my graph:

• Open Items -50
• Closed Items - 25
• Canceled Items - 20

On another day, using the same graph, I now want to see Q2 Apr-Jun 2022. This would give me the following numbers on my graph:

• Open Items -42
• Closed Items - 30
• Canceled Items - 11

I know this sounds tricky, but i would think somehow this can be done. I dont want to build it into a metrics sheet, I just want to be able to change it as needed via a Report. Is something like this concept possible? My goal is to give the client the flexibility to do this on his own without having me update formulas on a metric sheet, especially if i am not around. I Really Hope there is a solution to this idea or some workaround i can do. Thank you!!!!

Jeff Reisman

@Smartsheet Batmon

I do this all the time.

You DO need a metrics sheet, but you can give the users a very limited report based on that metrics sheet, where they can change a value that the formulas in the metrics sheet use to do their calculations. Then if your graphs are using those same metrics sheet columns, the graph will change based on what the users enter on the report.

For example, I have a metrics sheet pulling totals of order types from a sheet. Elsewhere on the metrics sheet, I have a start date field and an end date field. My metrics columns use COUNTIFS referencing a remote data sheet but using the start and end date from the metrics sheet.

=COUNTIFS({Data Sheet Order Type Range}, [Order type]@row, {Data Sheet Order Date Range}, >= [Start Date]$1, {Data Sheet Order Date Range}, <= [End Date]$1

Now you create a report based on the Metrics Sheet. Only show the Start Date and End Date columns. You can also make it only show the first row by using a checkbox field elsewhere on the metrics sheet, and use that as criteria in the report.

Create your graph based on the data in the Order Type and Total Orders columns from the metrics sheet. When the users enter dates into [Start Date]1 and [End Date]1, your formula results change and the graph updates.

Jeff Reisman

@Smartsheet Batmon

I do this all the time.

You DO need a metrics sheet, but you can give the users a very limited report based on that metrics sheet, where they can change a value that the formulas in the metrics sheet use to do their calculations. Then if your graphs are using those same metrics sheet columns, the graph will change based on what the users enter on the report.

For example, I have a metrics sheet pulling totals of order types from a sheet. Elsewhere on the metrics sheet, I have a start date field and an end date field. My metrics columns use COUNTIFS referencing a remote data sheet but using the start and end date from the metrics sheet.

=COUNTIFS({Data Sheet Order Type Range}, [Order type]@row, {Data Sheet Order Date Range}, >= [Start Date]$1, {Data Sheet Order Date Range}, <= [End Date]$1

Now you create a report based on the Metrics Sheet. Only show the Start Date and End Date columns. You can also make it only show the first row by using a checkbox field elsewhere on the metrics sheet, and use that as criteria in the report.

Create your graph based on the data in the Order Type and Total Orders columns from the metrics sheet. When the users enter dates into [Start Date]1 and [End Date]1, your formula results change and the graph updates.

Smartsheet Batmon

@Jeff Reisman ,

You are a GENIUS! Love, Love, Love this idea. I tried it out myself and it WORKS! Well written and explained, thank you so much for taking the time to help me out with this. Happy holidays!

Jeff Reisman

@Smartsheet Batmon

Happy to help!