Grouping with Parent Rows in Report

Valerie Olson · 03/24/22

I would like to groups by quarter in a report, but keep the parents rows repeated in each quarter. Am I able to do that some how? When I use my quarter column to group all of the parent rows group at the bottom.

For instance, my parent rows are

Task 1

a - Q1

b - Q1

c - Q2

I want Task 1 to be broken into two groupings like below:

Task 1 (Group1 - Q1)

a - Q1

b - Q1

Task 1 (Group 2 - Q2)

c - Q2

Thanks!

Paul Newcome · 03/24/22

You are going to need to include a helper column (that can later be hidden) on the source sheet that brings the parent row data into every row. Something along the lines of...

=IF(COUNT(CHILDREN()) = 0, PARENT([Task Name]@row), [Task Name]@row)

Then you can do an initial grouping on this helper column and then a secondary grouping based on the quarter.

Paul Newcome · 03/24/22

You are going to need to include a helper column (that can later be hidden) on the source sheet that brings the parent row data into every row. Something along the lines of...

=IF(COUNT(CHILDREN()) = 0, PARENT([Task Name]@row), [Task Name]@row)

Then you can do an initial grouping on this helper column and then a secondary grouping based on the quarter.

CBRNDLREZI · 01/05/23

I developed this solution for a similar problem. It came from various other posts and investigations.

First I had to create a helper column as mentioned in multiple posts about this problem.
Then, I added in this column formula.

=IF(COUNT(ANCESTORS([Task Name]@row)) = 0, [Task Name]@row, JOIN(ANCESTORS([Task Name]@row), " / "))

I am including a screen shot of the result which basically concatenates the hierarchy in the helper column. This column can then be included in a report where you would then have to use groups and filters to get the set of info desired or use additional formulas in the sheet to use for additional dissection's.

Paul Newcome · 01/06/23

@CBRNDLREZI Yes. This has been done before in a number of other posts. It works for multiple layers of hierarchy, but for a single layer of hierarchy such as in the original post, it is a little more simple.

CBRNDLREZI · 01/06/23

Thanks @Paul Newcome . I'm very new to the Smartsheets world. Glad to hear the solution is posted out there in other threads too.

Jane b · 04/27/23

Is there a way to do this that only lists the topmost parent in the helper row?

Paul Newcome · 04/28/23

@Jane b You would use this:

=INDEX(ANCESTORS([Task Name]@row), 1)

Dave H · 03/20/24

@Paul Newcome Thank you for the answers here. This formula is working for me, but I need one adjustment - how would I make the Parent Record helper column cell BLANK if it's the parent record? Basically, I only want the parent helper field name populated if its a child record, not the parent.

Decall95 · 04/11/24

https://community.smartsheet.com/discussion/comment/356092#Comment_356092

@CBRNDLREZI I used your exact formula but it's only giving me the parent name without the " / child" showing up - any suggestions on how to fix what I've got going on?

CBRNDLREZI · 04/17/24

@Decall95 - I would need to know more about your set up. Can you provide more info/ context?

Grouping with Parent Rows in Report

Best Answer

Answers

Categories