Child Rows not populating in report

Luke Serrato

Hi,

I’m trying to build a report with hierarchy, but the child rose keep disappearing once I complete the report build.

I understand that reports will not display hierarchy indentations, but the child rows aren’t even showing up in my report.

Any help would be greatly appreciated

-Luke

Andrée Starå

Hi @Luke Serrato

If they show depends on what criteria you've selected, and you might need to add so-called helper columns to make it work.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Luke Serrato

These are from my phone, as I’m currently putting my toddler to bed, but in the off chance that you might be able to read this and offer a solution before I have to work on this in a few hours, I figured I’d make the effort now.

I’ve tried multiple criteria under the new report builder options that are now called “filters”, none of which will populate anything other then the blue grand-parent rows.

I've read a few of your previous posts and have begun building helper columns to count the grand-parent, parent, and child rows.

Ideally, the report would show the grand-parent row “policy update” (in blue), and each of those 3 parent rows under the column titled “phases” (in grey). This would demonstrate how close each process is to completion.

This would likely be displayed on a dashboard that could then be clicked on and linked to the source sheet where a collaboration would take place by using the Smartsheet “proofing” function, prior to updating any status’ to “Draft Approved”, “Final Approval”, etc.

@Andrée Starå , any help you can offer would be greatly appreciated.

-Luke

Note: The “2” in the primary column of row 3 was just to test if the presence of data in the primary column was required for the report to populate it. Obviously that didn’t work.

Luke Serrato

Hey @Andrée Starå ,

I tried replying to this earlier but it appears to have gotten deleted somehow.

I’m trying to create a report that will display each “policy update” (grandparent, row in blue), along with each “phase” (parent row, in grey).

The report would be imbedded in a dashboard to display the individual progress for each update, and would be a clickable link to the source sheet where collaboration could take place using SmartSheet’s proofing functionality, and eventually could be approvals could be applied.

Here are some screenshots:

Note: “Policy Update” Column, row 3, has a “2” in it, to see if the report builder needed data in the primary column to be displayed in the report. That didn’t work.

Any help you can offer would be greatly appreciated.

-Luke

Andrée Starå

@Luke Serrato

Happy to help!

It looks like you could use the helper column, Hierarchy Count, and set it to include levels 0 and 1.

Would that work?

Luke Serrato

Hey @Andrée Starå ,

My goal was to create a report that allowed those sub-tasks (Phases) to display along with their corresponding progress bars, but I later realized I would also like one of the 9 approving/collaborating parties to see where different users might like to see input was required of them in order to move the process forward.

Once I abandoned the ability to create a formula that could be populated throughout the entire column, and instead chose to make a repetitive template that could have its visibility toggled on and off when blank, in use, and ultimately completed, I found my solution.

The key column that my report would revolve around would be users instead of hierarchies. In header columns such as those previously displayed "Policy Updates", "Phases", would have a column with multiple users selected (All 9 users), while a formula would be specific to the 5 initial authorizing rows static collaborators, and the next phase would hold a formula that would populate based on the presence of 9 static collaborators.

If a condition existed in a column (In this case, a "Stop", or "Hold"), it would cause the Status User displayed elsewhere in the row to populate.

I started by using a "VLOOKUP" formula to drag the contacts back into the sheet. After briefly encountering a problem here, I realized that smartsheet was trying to alter my lookup function to find non-exact matches and was returning a "#NO MATCH" error. Once I added the optional "False" parameter at the end of the VLOOKUP formula, it for some reason forced smartsheet to stop pulling back non-matches and instead returned the correct result.

This made me rethink the need for the alternate page that housed my VLOOKUP table, and I instead used a cross sheet formula by using the following:

Here's what I ended up with:

Formula: =IF([?]@row = "No", Contacts@row, IF([?]@row = "Hold", Contacts@row, [(H)Column19]@row))

Rows 1, 2, 8, and 16 will have a static group of multiple selected users in column "(H) User Visibility"**.

Rows 2-7, 9-15, and 17, all have specific/static formulas that check the "?" column for the condition before returning a result. If the "?" Column had a "No" or "Hold" status, it would pull the contact from the static location in the same row. If it held any other status (Leaving only "yes" or blank), it would pull from (H) Column19 (Which should be retitled to "(H) View All Users"). Row 1, Column (H)Column19 is where I can readily select which users I want to view all rows, so I can toggle myself and/or any admins who might want to see all status' under the "?" column, rather than having to manually enter them on each row mentioned at the beginning of this paragraph. Explanation on this photos:

Rows 2-7, 9-15, and 17, Formula: =[(H)Column19]1

**("H" is the prefix I use to signify columns I can thoughtlessly collapse after I've needed to "Expand All" for some development tweak without excessive deliberating if I was leaving it visible for some reason)

Finally, it allowed me to generate a single report that the logged in user can view what items need action, while still allowing me to add users to a "master" view including all status' that can be easily selected or deselected via the top column. Like This:

User View:

Master View:

Lastly, this template will be copied and pasted multiple times to this sheet, where I will prefill the update number to reflect the 4 digit year and the sequential policy update. When a new year rolls around, the sheet can be replicated easily.

Don't know if anyone will read this, but feel free to hit me up if you need anything. Thanks!

-Luke