Running a report with multiple drop-downs

Hi.

I am running a QI report where we are looking at any kudos for our agents. We want to recognize our top performers, and we have a sheet where we collect that data for specific agents and incidents. On most incidents there will be 2 or more agents receiving those kudos, so we created a multiple select drop-down to enter the agents names to receive those kudos, and run a quarterly report which counts how many kudos each agent has received.

So I have been able to create the report using a single select drop down column for the agents names, but I can't the same report to work using the same data using a multiple-select drop-down column in the sheet. What am I doing wrong?

Thanks,

Adrienne

Find more posts tagged with

Reports

Accepted answers

Genevieve P.

Hi @Office of the Medical Director

It sounds like you're looking to use the Group and Summary features based off of the multi-select column, is that correct? If so, you're doing nothing wrong. This is currently not possible to do in a Report, the column type will need to be single-select (as you've found). See the "tips and tricks" section of this Help Article: Configure Grouping to Organize Results in Report Builder

Please let the Product team know about your request by filling in this form, here!

As an alternative to a report, you could build a Metrics sheet which identifies each specific agent in one column, then uses cross-sheet formulas to do the COUNT per quarter. Let us know if you need help building out those formulas and we'd be happy to help further.

Cheers,

Genevieve

Genevieve P.

Hi Adrienne,

No, you would run into the same issue with the Pivot App since multi-assign columns (Contact Columns with multiple selections enabled) aren't supported.

A single-select column would work with the Pivot app, but if you were to change your current column to single-select then the Report should work for you as well. It sounds like adjusting your intake to ensure only one agent is selected per-row (so, asking your users to create multiple submissions instead of multiple selections) would be the easiest way to get your data.

Cheers,

Genevieve

Genevieve P.

Hi Adrienne,

Multiple columns will sort of work. You'll be able to do a Summary per-column by creating an individual Report per-column, but each Summary number can't then be combined with the column next to it.

For example, if this was your sheet:

Screen Shot 2021-07-27 at 1.52.18 PM.png

Then your Report can group per-person and count the rows, but it would be an individual Report and count per-column. I've put all three Reports in a dashboard to see them side-by-side:

Screen Shot 2021-07-27 at 2.03.11 PM.png

I tested building out some helper columns and formulas to get the COUNT all together, but the way I came up with would need to ensure that anyone who appears in the 2nd and 3rd column is also in the first column as well, since we would use that first column to Group by.

At this point, based on your end-goal, the Kudos set-up would still be best to have multiple, individual rows per-person instead of individual columns or fields in the form.

I hope that helps!

Genevieve

All comments

Andrée Starå

Hi @Office of the Medical Director

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

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!

Genevieve P.

Hi @Office of the Medical Director

Please let the Product team know about your request by filling in this form, here!

Cheers,

Genevieve

Office of the Medical Director

Thank you everyone for the insight. I thought about doing a metrics sheet like I have done in the past; however, I have 700+ personnel that would have to be listed in the metrics sheet which is why I wanted wanted to run a report instead of creating a metrics sheet. In the past, I kept the data in an excel spreadsheet and created a pivot table to display the same information. Do you know if the pivot table add-on will allow something like this in Smartsheets?

Thanks

Adrienne

Genevieve P.

Hi Adrienne,

No, you would run into the same issue with the Pivot App since multi-assign columns (Contact Columns with multiple selections enabled) aren't supported.

Cheers,

Genevieve

Office of the Medical Director

@Genevieve P, thank you for your insight!

The way you are recommending entering the intake data is how it is currently set up with just 1 agent per row, and the report works with with this format.

However, what if I make multiple columns to select the agents but only 1 agent would would be selected per column, would that work too? I know that I would have to create additional conditions, but that's the only other workaround I could think of using. What's your thoughts?

Thanks,

Adrienne

Genevieve P.

Hi Adrienne,

Multiple columns will sort of work. You'll be able to do a Summary per-column by creating an individual Report per-column, but each Summary number can't then be combined with the column next to it.

For example, if this was your sheet:

Then your Report can group per-person and count the rows, but it would be an individual Report and count per-column. I've put all three Reports in a dashboard to see them side-by-side:

At this point, based on your end-goal, the Kudos set-up would still be best to have multiple, individual rows per-person instead of individual columns or fields in the form.

I hope that helps!

Genevieve

Office of the Medical Director

Hi Genevieve,

Thank you for taking the time to test this theory. I didn't think that having multiple columns would work as well, but I thought I would ask.

Thank you for all of your assistance with this, and I did submit a ticket requesting the capability to run a report using the multiple drop-down columns.

Thanks,

Adrienne

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.