Include Parent Rows in Reports

Jim Hartman

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.

KrisWalsh

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)

Jim Hartman

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.

KrisWalsh

It makes perfect sense... I'm trying to figure it out...  Stay tuned. 

J. Craig Williams

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

J. Craig Williams

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

 

J. Craig Williams

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

 

 

 

KrisWalsh

TY, JCW! I love learning these shortcuts. 

 

So, in TRUE/FALSE columns:

#1) "= 1" is assumed

#2) "else 0" is assummed

 

Correct?

 

J. Craig Williams

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

Michael Schalip

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

KrisWalsh

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.  

Example Sheet

J. Craig Williams

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

KrisWalsh

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

Lynnette Batt

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.

J. Craig Williams

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

 

Brad Jones

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.