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

Tags:

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.
    Genevieve P. Employee Admin
    Answer ✓

    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.
    Genevieve P. Employee Admin
    Answer ✓

    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:


    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

Answers

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • 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.
    Genevieve P. Employee Admin
    Answer ✓

    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, 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.
    Genevieve P. Employee Admin
    Answer ✓

    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:


    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

  • 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!