Adding Parent Rows in reports

Tariq Shaikh

I am still learning my way around in SmartSheets and need some more help!

 

I have created a report to show all "Past due" actions by owner. The report only lists children as we calculate Past Due status at this level - not at Parent level. Is there any way to include parent rows in this report, even if they don't have a "Past Due Status" but one or more of their children may hve this status?

JamesR

Tariq,

Have a value in the parent row that does not appear in any other rows and then include that as part of the Report in the report builder. Perhaps th Name of the Hierarchy.

Tim Meeks

To be specific on what James suggested, we have a Project Status Report that has parent rows and child rows underneath.  We ONLY want to pull the Parent Rows on the Report, so we put a Project # in the PARENT Rows and tell the report to ONLY pull if the Project # is not blank. You could do something similar per James' suggestion and even hide this indicator column.

 

Tim

Tariq Shaikh

Thanks James and Tim. I have tried both options but am not getting the results i need. The Parents in my sheet are jobs requested by customers, while children are tasks assigned to technicians to complete the customer requested jobs. I have added a column for "Project ID" and each Job has a numeric "Project ID", while tasks are not numbered.

 

I am trying to set up a report to show all rows with a numeric Project ID (i.e. the Parent) and any tasks that are either past due or will become due in the next 2 weeksby using the following formula in Smartsheet (in all rows, including Parent rows):

 

=IF(AND(Due15 <= TODAY() + 7, NOT(Due15 < TODAY()), NOT(Status15 = "Completed")), "Due in 7 Days", IF(AND(TODAY() > Due15, NOT(Status15 = "Completed")), "Past Due/No Schedule", ""))

 

For my "Past Due/Due in Next 2 Weeks" report, for "What", I  have selected "Project ID is a number" OR "Status is not "Completed". For "When", I have selected "Due is in the past OR Today OR in the next 14 Days".

 

When I run the report, I only see parents that fall in the same Due/past Due timeframe and not where the project itself may not be past due/due in next 2 weeks, but one or more tasks may fall in that category.

 

 

J. Craig Williams

One simple question, why does your formula check for 7 days and your report check for 14?

You could just add another column for Next14_or_Past

and then run your report from that.

 

=IF(AND(Due15 <= TODAY() + 14, NOT(Due15 < TODAY()), NOT(Status15 = "Completed")), "Due in 14 Days", IF(AND(TODAY() > Due15, NOT(Status15 = "Completed")), "Past Due/No Schedule", ""))

 

If the real problem is that the Project_ID does not show up in the report, try this:

 

Create a new column (Rep_Project_ID).

 

Formula in rows 2+ (does not work on row 1)

 

=IF(COUNT(CHILDREN([Primary Column]2)) > 0, [Project ID]2, Rep_Project_ID1)

 

Replace [Primary Column] with your primary column name.

This will determine if the primary column is a project (parent) or task (child).

If so, you have the Project ID. If not, get the one from the row above.

 

Does not work if you skip rows or indent improperly.

 

Hope this helps

 

Craig