Parent and child relationships in reports

sdolan

I'm trying to figure out a way to show parent/child relationships in reports.

I've watch this youtube video which seems to have found a sensible workaround but they are using dates and the suggested formula is throwing me.

What i'd like to do is include the parent/child relationship in a report if there is text in the status column. FYI I will have multiple project on one sheet ie food show is one project all the other lines will be the same categories for every other project.

Smartsheet Reports | Use This Trick to Get PARENT/CHILD Hierarchies (youtube.com)

Any help/suggested formulas much appreciated.

Thanks

KPH

Hi Suzanne

I have not watched the YouTube so don't know if this is what you are trying to do, but you can add a column to the grid that contains the parent name, or even a breadcrumb showing parents and grandparents. When you produce reports you can use this column to filter.

This formula, for example, will return the value in the Production column for the first generation:

=IFERROR(INDEX(ANCESTORS(Production@row), 1), Production@row)

It will look like this:

You can make it a column formula and hide the column so it won't bother you in the grid, but use in to filter your reports.

You can find more about parent and ancestor functions here:

https://help.smartsheet.com/function/parent

https://help.smartsheet.com/function/ancestors

KPH

Hi Suzanne

I have not watched the YouTube so don't know if this is what you are trying to do, but you can add a column to the grid that contains the parent name, or even a breadcrumb showing parents and grandparents. When you produce reports you can use this column to filter.

This formula, for example, will return the value in the Production column for the first generation:

=IFERROR(INDEX(ANCESTORS(Production@row), 1), Production@row)

It will look like this:

You can make it a column formula and hide the column so it won't bother you in the grid, but use in to filter your reports.

You can find more about parent and ancestor functions here:

https://help.smartsheet.com/function/parent

https://help.smartsheet.com/function/ancestors

Adrian Mandile CHESS

And you could use Grouping in the report via this new ancestor column to simulate the parent/child row summaries you see in the sheet's grid view.

sdolan

Thanks so much. That seems to help. Sorry another question. Is this all soooo new and like learning another language.

I'd like to check a box if the status column is either blank or has N/A in the box. This if statement seems to be working for if its blank but I don't know how to add the OR if its N/A

=IF(ISBLANK([Status]@row), 0, 1)

Thanks so much

KPH

Hi Suzanne

You need to add an OR function to the IF, like we did for the other Status dropdown options on the other thread.

=IF(OR(Status@row = "N/A", ISBLANK(Status@row)), 0, 1)