Show both parent and child rows in report, even if the Parent doesn't meet the criteria

Netanel yosef
Netanel yosef ✭✭✭✭✭
edited 05/25/20 in Smartsheet Basics

i have sheet with parent and children tasks and in my report i want to see the children tasks with the parent row.

when i try to do this i show only the children..


there is any way to show chldren tasks with parents in report>??

«1

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Netanel,

    Yes, there is.

    Do you want to show all parents and children or only specific ones?

    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@getdone.se)

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    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.

  • Netanel yosef
    Netanel yosef ✭✭✭✭✭

    hi,

    here the link to sheet:


    im have report that show the row that not has a "Eng. approveed" and the "status" "ממתין לאישור חוזר של יוני"

    (status in hebrew sorry)

    and i want to shw in report the children row and the parent.

    only the children get the yes /not "Eng. approved"

    the parent not specific value - can be empty or hold ...

  • I'm also curious how to do this, Andrée. Is there an easy way to describe how if the parent and child rows already exist?

  • Netanel yosef
    Netanel yosef ✭✭✭✭✭

    hi,

    i email with Andrée and he wrote a formula that meets with my needs. 

    you can edit and use to you needs.


    =IF(AND([Eng. Approved]@row = "No"; Status@row = "value"); 1; IF(COUNTIFS(CHILDREN([Eng. Approved]@row); "No"; CHILDREN(Status@row); "value") > 0; 1))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Netanel yosef Thanks!

    @Michael Ponte Let me know if you need additional help!

    Have a fantastic weekend!

    Best,

    Andrée

    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.

  • Harish Dharmarajan
    edited 04/09/20

    @Andrée Starå - Looking for some help... What if I need to show all parents and children in the report ?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Harish Dharmarajan

    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@getdone.se)

    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.

  • @Andrée Starå I have been stuck by this issue as well. How can I get the same Sheet Filter "include parent rows" effect in Reports?

    I have sheets that have different levels of indents, and some tasks that have the same names belong to different indent parents (different phases for example). I want to create reports that shows tasks that are upcoming/at risk/overdue (meeting certain criteria). The formula works, but they only show the children. Because some of my children belong to different parents (phases), I have no idea which task it is.

    So I need a way to display the parent rows (parent as well as grant parents preferred) in the report to differentiate. If the report could also intent them like in the sheet filter, that will look even better.

    For example,

    I would like the report to look like this after meeting the report criteria:

    Grandparent 1

    Parent 1

    Child 1

    Child 2

    Parent 2

    Child 1

    Child 2


    Thanks,

    --Annie

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Annie Cheng

    It depends on the filter and what you want to show. Usually, I create a so-called helper column to Indicate if something in the children is true then show the parent.

    It can get tricky to make it work!

    Make sense?

    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@getdone.se)

    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 help the Community by marking it as the accepted answer/helpful. 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.

  • Hi Andree, Smartsheet community,

    I have the same question. My scenario:

    Parent Task 1 - Owner: Patty

    • Child task 1 - Owner: Rick
    • Child task 2 - Owner: Jay


    Rick needs a report that shows every project he owns (Filter: Owner = Rick) - using the Report Builder.

    And this report view also needs to show any parent or child associated to his task(s) - have visibility to the entire "family". The report should also show "Parent Task 1" and "Child task 2" as well.

    Is there a way to do it using Reports? do I need to create a column with some additional flags?

    Thanks,

    Lorena

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Lorena Cuadra

    You’ll need to add helper columns, and it can get tricky.

    How many different owners will there be?

    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@getdone.se)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.

  • Irma Hypes
    Irma Hypes ✭✭
    edited 08/12/20

    @Andrée Starå Would love your help on this. For the formula that was provided earlier in this thread:

    =IF(AND([Eng. Approved]@row = "No"; Status@row = "value"); 1; IF(COUNTIFS(CHILDREN([Eng. Approved]@row); "No"; CHILDREN(Status@row); "value") > 0; 1))

    I have three columns (there is also a date column, but not included in this example).

    Workstream/Task - this is description of the creative asset (at the Parent level) --- or --- the description of the task necessary to produce the creative asset

    Parent checkbox - checked if Workstream/Task contains name of the creative asset

    Task checkbox - checked if this is a milestone i want to track


    300x250 banner (Parent checkbox ticked)

    • creative brief complete
    • copy submitted
    • images submitted
    • initial layout
    • client feedback due (Task checkbox ticked)
    • version 2
    • version 2 - client feedback due (Task checkbox ticked)
    • final
    • client approval (Task checkbox ticked)
    • live on website (Task checkbox ticked)


    In the report, i want to be able to see all items checked and in order, so i can track by date. I want to be able to do this because I have multiple sheets with the same columns (different teams working off of multiple sheets)

    300x250 banner

    • client feedback due
    • version 2 - client feedback due
    • client approval
    • live on website

    Can you write out the formula for me?

    Thank you!!

    Irma

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/12/20

    @Andrée Starå I would think that for @Lorena Cuadra you could get away with only a single helper column and a basic

    =JOIN(CHILDREN(Owner@row), ", ")


    Then in the report builder set it up to include the rows where Owner is Rick or Helper contains Rick.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Paul Newcome

    Yes, that makes sense. Nice workaround!

    @Lorena Cuadra

    Have a look at Pauls solution above.

    Would that work?

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Irma Hypes

    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@getdone.se)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.