Tracking "Support Type" Across Events

Draykov

I am attempting to create a dashboard that pulls from reports/sheets I've made that relate to event support for my organization.

There are three types of support that my team offers for events. They are not mutually exclusive. The names of each support type are "Streaming," "Podium Support," and "Photography."

On the form side, a requestor of event support can select any or all of these three support types (represented by a column called "Event Support Type," a dropdown list column in the intake sheet).

Now, when it comes to adding data to reports/dashboards, Smartsheet doesn't like cells that contain multiple values, so I've created helper columns (checkbox type), 1 for each support type. If an event support type is selected in "Event Support Type," a formula causes a box to be checked in the corresponding column of that type.

I've tried building a single report with multiple groups/summaries. I've tried building three separate reports (one for each type of support requestors can request). I'm struggling and could use some insight.

Because one event (represented by an individual row) may include requests for anywhere from 1-3 types of support, and because I can seemingly only choose one data source for a chart, I'm not sure how to weave all of this into a single chart widget within my dashboard that shows the number of requests for each type of support (think pie chart with 3 pieces showing number of each support type, similar to the one below - I faked this one based on a different metric that was easier to figure out, but this is what I'm hoping to accomplish).

What I would ultimately like to accomplish in a dashboard is a breakdown of how many times each type of support was requested. With the path I've used to get this far, is that even possible? Maybe counting only the true/checked values of the individual helper columns in some way? Thanks!

Draykov

Been experimenting with applying filters/filter groups based on certain conditions in the report. There might be a solution there somewhere, but I haven't cracked it yet.

DKazatsky2

Hi @Draykov ,

You can collect the counts in a "metrics" sheet and then use that as a data source for the chart.

Here is how to setup the metric

And the formula would be =COUNTIF({Event Support Type}, HAS(@cell, [Primary Column]@row))

The {Event Support Type} range is the multi-select column you show above.

Hope this helps,

Dave

Draykov

Thanks, @DKazatsky2. I think I'm missing a key component though.

Am I placing the COUNTIF formula in the metrics sheet, or the original intake sheet? In the formula you provided, what is [Primary Column] referencing?

I'm still not sure I understand how to pull my intake sheet metrics into a separate "metrics sheet." I understand on a very basic level that one sheet can reference another, I'm just not sure how to build the metrics sheet.

A_C

Hi Draykov,

Have a quick look a the video in this link. Will give you the overview of what you are looking for.

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

Also, if you need to do the calcs quickly, whilst you are getting up to speed with the Metrics Sheet side of things, you can add rows to your existing sheet and create the calcs in the new rows (you can later use the values to test your metrics sheet results. once they are ok remove the rows from your sheet.)

A

Draykov

Thanks, @A_C. I think that has me a bit closer, though my current solution is probably not as elegant as it could be.

I have my intake sheet which has individual helper columns that each register a count each time the "Event Support Type" contains the type of support pertaining to that helper column.

I now have a metrics sheet that looks at each of my 3 helper columns from the intake sheet to SUM those counts per support type (CNT PEND). Since I'm referring to an intake sheet and what is effectively an archive sheet (pending and previously supported events, respectively), I also included a column referencing the archive sheet to SUM the corresponding helpers from said archive sheet (CNT PREV). Finally, I have a third SUM column in my metrics sheet that SUMS CNT PEND and CNT PREV for a total of all requests for each support type. My plan is to use this (or rather, a report generated from the metrics sheet) to make my chart in my dashboard.

Thanks @DKazatsky2 and @A_C for the help!

Draykov

Okay, so...if I build a chart in a dashboard, I'm effectively counting my count...

...and what I want to do is create a pie chart that resembles the report...that is, I want to report the 20 streaming, 16 podium support, and 11 photography counts...not CNT PEND vs CNT PREV vs CNT TOT.

I would try to create a chart widget by relying directly on the metrics sheet as a data source, but when I attempt that, I get the "Selected data cannot be charted. Please select a new range" error.

I'm missing something obvious.

Draykov

I think the issue was that I was not selecting the cells for the specific data range from the metric sheet that I wanted to pull into the chart widget.

Draykov

Here is where I ended up by end-of-day. Unfortunately, I couldn't select the "Pie" type of chart for Events/Year (presumably because I'm only looking at two metrics).

Paul Newcome

Try just creating a basic row report that brings in every row (only pull in the three checkbox columns) then use the summary feature within the report to get the count in each of the checkbox columns.