Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Include Parent Rows in Reports
Can the option to include parent rows be added to the report builder? This would be similar to the checkbox that is offered on filters for columns when in a sheet.
Comments
-
I use the checkbox columns below for report filters.
I create a checkbox column on just about every sheet & template I make and hide it.
Column Name: Parent
Formula: =IF(COUNT(CHILDREN($[Primary Column]1)) > 0, 1)
(If this row has any children then check this box.)
This can be used as a Report Filter "Parent: Is checked" to view parent rows and "Parent: Is NOT checked" to view child rows.
However, the top row of a lot of my sheets are the 'Summary Row' for reporting, so I also have another checkbox column and I only check Row1 of that column.
Column Name: Report Filter
Formula: None (Only check row 1)
-
I would like the ability to include only parent rows of child rows that are selected in the report, and not all parent rows, if that makes sense.
I have created a report for team members to be able to view all incomplete tasks from a number of different project plans. I can bring in their individual tasks without any problem, but it would be very helpful to bring in the parents of those tasks to allow them to see which section of the plan that particular tasks falls under.
-
It makes perfect sense... I'm trying to figure it out... Stay tuned.
-
I also check for milestones (which I use "MS:" to differentiate)
=IF(OR(COUNT(CHILDREN(Primary23)) > 0, FIND("MS:", Primary) > 0), 1)
I've been working on a way to determine which level in the hierarchy we are
1
1.1
1.1.1
1.1.1.1 (no children)
but haven't quite got it without strict naming conventations for summaries and tasks.
Craig
-
Kris,
In your third column, you used
=IF(COUNTIF(CHILDREN(Child1), 1) > 0, 1, 0)
which means you define as "having a child" as only items that are not themselves parents.
In my experience, a grand-parent is a parent (only), a parent is both parent and child, and a child is only a child.
So as long as I have children, I am a parent.
=IF(COUNT(CHILDREN()) > 0, 1, 0)
Can you elorborate on that?
Craig
-
Sorry, just realized something else to add
This:
=IF(OR(Child1 = 1, PofC1 = 1), 1, 0)
is the same as
=OR(Child1,PofC1)
Since we are dealing with 3 checkboxes.
Craig
-
TY, JCW! I love learning these shortcuts.
So, in TRUE/FALSE columns:
#1) "= 1" is assumed
#2) "else 0" is assummed
Correct?
-
Kris,
Yep.
A true value is 1, a false value is 0.
In other languages, sometimes, true is non-zero, but I've learned that depending on vintage and compiler options and such is a sure way to spend a weekend trying to figure out why something is broken.
Craig
-
Hi folks - I'm relatively new to Smartsheet myself, and have a related problem/question. We have ~8 business units - each with their own list of projects - and their project lists can be anywhere from 4-5 projects to ~30-40 projects, (granted - most of these are very small, simple "projects" with few - or even no - related tasks.)
First - I'm trying to run a report based on this premise of a "checkbox" column with the aforementioned formula embedded, (=IF(COUNT(CHILDREN($[Primary Column]1)) > 0, 1)
The problem is that I've got "projects" (parent rows) that have NO "tasks" (no children). When I run the aforementioned formula down the colum - any parent rows that have children get checked - but any parent rows without childred do NOT get checked. Am I understanding correctly that I have to have a 2nd checkbox column to calculate for those parent rows that have no children?....and then, when running a report, check for the checkbox value in both of those rows?
How did we get to this point? Well - rather than having each project on a different sheet (which would mean hundreds of sheets) - I've opted to have them manage their project lists on a single sheet per business unit. (The 2nd reality check is to ask: Is this model reasonable?)
If I have to run the 2 columns - that's fine.....I was just hoping to take the shortcuts....
Michael
-
This isn’t very pretty, but it works. Example Sheet
(You don’t really need the Parent Column, but I like having it.)
Checkbox Column1: Parent
Formula says “Check this box if this row has children”
Checkbox Column 2: Child
Formula says "Check this box if this row does NOT have any children."
Checkbox Column 3: Parent of Child
Formula says "Check this box if I am the parent of Child Rows."
Checkbox Column 3: Parent and Child
Formula says “Check this box if ‘Child’ or ‘Parent of Child’ is checked.”
Step 1: Create your 4 Checkbox columns. (Parent, Child, PofC, PandC)
Step 2: Paste this formula and copy down for all rows of the Parent column.
(change ‘Primary’ to the name of your Primary Column)
=IF(COUNT(CHILDREN(Primary1)) > 0, 1)
Step 3: Paste this formula and copy down for all rows of the Child column.
(change ‘Primary’ to the name of your Primary Column)
=IF(COUNT(CHILDREN($Primary1)) = 0, 1, 0)
Step 4: Filter so only the Parent rows are showing
Step 5: Paste this formula and copy down in the PofC column.
*The formula only works on parent rows.*
=IF(COUNTIF(CHILDREN(Child1), 1) > 0, 1, 0)
Step 6: Paste this formula and copy down for all rows of the PandC.
=IF(OR(Child1 = 1, PofC1 = 1), 1, 0)
Now add the PandC filter to your reports & you’re good to go.
-
How can a row be a parent if it has no children?
If I understand correctly, you have taken a short-cut by assuming that any row with children must be a project.
How can a project have no tasks?
I assume you are taking the tasks for granted and/or not tracking them in Smartsheet.
I could have a home project to "paint the fence" and since I am experienced know that includes going to the store to buy the paint, sanding/scrapping the old paint away, doing the actual painting, and later cleaning up. It helps if that is broken out into step to teach my kids how to paint a fence, but otherwise, not needed.
And that's OK too.
If you have no tasks that have sub-tasks
PROJECT
> Task A
>> Task AA
>> Task AB
> Task B
> Task C
then your short cut that all parents rows are projects is true.
But not all projects are parent rows.
So, in that case, easiest is to click the check box for those rows.
You've destroyed the formula for that row, but that will only matter if you:
1. later add a sub-task (turning the project into a parent), and expect the checkbox to be checked because it is a parent now (it isn't, though it is stil checked)
2. destroy the last row's formula and then add a new row below it, expecting the formula to auto-fill (it won't)
3. copy the row somewhere else and expect the formula to be there (it won't be)
The second way is as you suggest, create a column or key off an existing column that tells you whether the row is a project heading or not. Got something like "Project ID" that only appears in the header rows? If so, check the box if the row has children OR the Project ID is filled in. If you don't have a column like that, you'll likely need to add one.
Craig
-
Michael,
"In my experience, a grand-parent is a parent (only), a parent is both parent and child, and a child is only a child." TY - J. Craig Williams
Check out this sheet: Parent of Children
With this thought process in mind, tell me which rows do you want to show in your report and I'll tell you the minimum # of columns to get it done.
(that I'm aware of)
-Kris
-
This is crazy. There should be a simple option to include parent rows in reports, just as there is with filtering. Even if you create a parent row column with checkboxes, and check those that are parent rows, you can't include that in the report b/c the "or" "and" toggle goes together. YOu'd need it to be all the things you want in the report ("and" between each), then "or" parent row checked. But even that is a workaround and it would be better to just have the checkbox option.
-
Lynnette,
I believe (based on thought and experience) that the issue with parent row not coming in automatically is that the Reports populate from one, multiple, or even ALL of your sheets. Some of those may or may not have parents.
Some of those parents may be ancestors too.
Report creation is already slow because of the process that is (I assume) already being done.
So it will take a lot of time or processing to get your Report to load - and then you can sort it. What happens to the ancestors then? The Report does not have an "order" like a sheet does. Once sorted, is it going to show what you want?
How will the system know your criteria is meant only for the child rows (for example) and that the parent row is brought over both because it matches the criteria AND it is a parent?
I can think of more reasons why it won't work well.
I'm not advocating NOT having some functionality that will satisfy these needs, I'm only saying it won't be easy.
Craig
-
I think the OP just wanted SS to integrate the option to include parent rows into a report. I too want the ability to add parent rows to a report without adding unnecessary data into the reports.
Whenever you make these extra boxes, placeholders, etc in order to satisfy a report filter, you must remember this same 'junk' is going to be exported each time you email, archive, or print the sheets.
