Creating a report by task owner and RAG status

Options

Hello Smart Sheet-ers,

I am fairly new to SmartSheet so excuse me if this is breathtakingly simple. I need to create a report of the following in dashboard. I assume from using Sheet Summary.

It needs to show Task Owner, then how many tasks that are RAG status red, yellow, green, blue.

I'm using the below columns:


So for example I'd like to see the person's name on the Y axis, then 4 x bars (red, yellow, green, blue) next to them in the dashboard.

So I need to know (I think) what formula to set up in the sheet summary to enable me to get that data into a report....

Thanks in advance!

Ollie

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Ollie Cater

    If the goal is to get this data into a Dashboard, you'll actually want to create a separate blank Grid sheet to be your "Metrics" sheet and use formulas to create your calculations.

    For example, you can use a COUNTIFS formula to count how many times a specific user appears with a specific status:

    =COUNTIFS({Source Sheet Assigned To}, Contact@row, {Source Sheet Status}, Status@row)

    See: Creating Cross-Sheet Formulas / COUNTIFS function

    Then you can use this data as the source for your Chart widget, but you'd need to select each person's data as a separate chart:


    Two different charts:


    Is this what you were looking to do? I might suggest that a Pie Chart (see here) may look best in this case.

    Let me know if this makes sense or if you need any additional help!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Ollie Cater 

    Hope you are fine, you can use the new report builder and use the grouping option first by task owner then by RGB and use the summary option to count for you.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Ollie Cater
    Options

    Hi Bassam,


    Thanks for the quick reply...do I need to have any Summary fields set up for this?

    Ollie

  • Ollie Cater
    Options

    Is there a way to make this more easily readable, say on a chart?

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Ollie Cater

    If the goal is to get this data into a Dashboard, you'll actually want to create a separate blank Grid sheet to be your "Metrics" sheet and use formulas to create your calculations.

    For example, you can use a COUNTIFS formula to count how many times a specific user appears with a specific status:

    =COUNTIFS({Source Sheet Assigned To}, Contact@row, {Source Sheet Status}, Status@row)

    See: Creating Cross-Sheet Formulas / COUNTIFS function

    Then you can use this data as the source for your Chart widget, but you'd need to select each person's data as a separate chart:


    Two different charts:


    Is this what you were looking to do? I might suggest that a Pie Chart (see here) may look best in this case.

    Let me know if this makes sense or if you need any additional help!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Ollie Cater
    Options

    Amazing. That's spot on. Thank you!

  • Genevieve P.
    Options

    No problem! Glad I could help.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Ollie Cater
    Options

    Hi Genevieve- can I just check one thing...

    In the first formula above, where you have written "Source Sheet", presumably here I use the title of the sheet I would like to use as a source sheet?

    Thanks in advance!

  • Genevieve P.
    Options

    Hi @Ollie Cater

    Do you mean this bit: {Source Sheet Assigned To}?

    In this case, you would need to create a Cross Sheet Reference to the sheet you're looking to pull information from. If you simply adjust the text you'll get an error because the formula won't know where to go.

    As you're typing the formula, click on this link that says "Reference Another Sheet"

    It will bring up a pop-up window where you can search your Smartsheet account for the correct sheet, the select the column within that sheet to be the reference for your formula.

    You'll need to do this twice... once for {Source Sheet Assigned To} and once for {Source Sheet Status}

    This Help Article has more information and a brief video tutorial that you may find helpful: Cross-sheet formulas

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now