Grouping with Parent Rows in Report
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!
Best Answer
-
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.
Answers
-
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.
-
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.
-
@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.
-
Thanks @Paul Newcome . I'm very new to the Smartsheets world. Glad to hear the solution is posted out there in other threads too.
-
Is there a way to do this that only lists the topmost parent in the helper row?
-
@Jane b You would use this:
=INDEX(ANCESTORS([Task Name]@row), 1)
-
@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.
-
@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?
-
@Decall95 - I would need to know more about your set up. Can you provide more info/ context?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives