Parent/Child Hierarchy when Building Reports

Susanthi Kodali

Hi - I'm trying to build a report for "Upcoming Tasks" in the next 7 days. I would like to show parent and child hierarchy in the report. However, I am not able to figure out the logic in the report builder? Any help will be appreciated.

Paul Newcome

Reports do not show indentation. I use a helper column on the source sheet that inputs a number based on the row's level of hierarchy and then use conditional formatting to apply the same format to each of the same hierarchy levels. This "color coding" helps visually.

Being able to see the indentation in reports would certainly be nice though. Feel free to SUBMIT A PRODUCT ENHANCEMENT REQUEST when you have a chance.

Susanthi Kodali

Hi Thank you soo much for the feedback. I'm learning Smartsheet as I'm doing it now. Will you be able to send me a screenshot of the numbering system you are referring to? that will really help me understand how you do this as suggested. Thank You

Paul Newcome

You would insert a new column (it can be later hidden to keep the sheet looking clean). Then you would use a formula such as

=IF(COUNT(CHILDREN([Other Column Name]@row)) > 0, COUNT(ANCESTORS([Other Column Name]@row)))

This will only output a number for parent rows which will be based on which level of hierarchy the row is. Drag-fill this down all of the rows, and you should be good to go.

Alex Scott

Hello. This is helpful, thank you. I wish the reports would reflect the hierarchies.

Thai La

Here's my parent row workaround:

"> Insert a symbol then 2 or more spaces before TEXT"

Add this to your rows, then you'll see indentations

Paul Newcome

@Thai La How were you able to get this to work? When I enter additional spaces, Smartsheet still only displays 1 space.

Thai La

In the Smartsheet, I've inserted the symbol ">" followed by 2 spaces

The report doesn't quite ident as much, but still shows the 2 spaces

Paul Newcome

@Thai La When I do that it only displays a single space no matter how many I put in between the > and the rest of the text. What happens if you put in maybe 5 spaces? Does it show all 5 for you? I wonder if it is a browser thing.

Thai La

2 spaces is the only thing that will show.

If you enter 5 spaces, the sheet will only 2 spaces.

If you edit the task, all 5 spaces are still there, but the display still only shows 2 spaces

Paul Newcome

Hmm. What browser are you using? I can't even get it to show the 2. Like you, the spaces are still there when I go to edit the cell, but the displayed spaces are limited to 1 for me.

Thai La

Using Microsoft Edge Chromium, but also use Google Chrome and Firefox too.

All 3 browsers show 2 spaces for me

Paul Newcome

That's interesting. I use Edge, Firefox, and Chrome as well.

James Keuning

I really enjoyed this discussion and wonder if anyone found a way to hack this, maybe using CHAR or UNICHAR?

Mike Mills

Is this something that can be accomplished with a Smartsheet Add-on? It would be nice to identify if these types of items are purposely not available because it's an "Upgrade" option with an add-on or if it's an enhancement request.

James Keuning

I use this formula:

=(IF((COUNT(ANCESTORS([Primary Column]@row))) = 0, "", IF((COUNT(ANCESTORS([Primary Column]@row))) = 1, "__", IF((COUNT(ANCESTORS([Primary Column]@row))) = 2, "____", IF((COUNT(ANCESTORS([Primary Column]@row))) = 3, "______", IF((COUNT(ANCESTORS([Primary Column]@row))) = 4, "________", IF((COUNT(ANCESTORS([Primary Column]@row))) = 5, "__________", IF((COUNT(ANCESTORS([Primary Column]@row))) = 6, "____________", IF((COUNT(ANCESTORS([Primary Column]@row))) = 7, "______________", IF((COUNT(ANCESTORS([Primary Column]@row))) = 8, "________________", IF((COUNT(ANCESTORS([Primary Column]@row))) = 9, "__________________", IF((COUNT(ANCESTORS([Primary Column]@row))) = 10, "____________________", ">")))))))))))) + [Primary Column]@row

to turn this on a sheet:

into this on a report: