Parent Row Only Report

Alyjon14
Alyjon14 ✭✭
edited 12/09/19 in Smartsheet Basics

Is there a way to build a report consisting of only parent row information? I am trying to show overarching HR Processes in my sheet (parent row) without pulling in the sub-tasks. I tried a checkbox report but the sub-tasks are still showing up in the report although they were not selected. Perhaps there is a workaround or another method of showing this information? Ultimately, I would like to display the parent row info ONLY in calendar view. Thanks

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Yes, there is, but the checkbox workaround should work. Can you share a screenshot of the Report Builder?

    (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Chak Khiam
    Chak Khiam ✭✭✭✭

    It does happen that when rows get promote to parent / demoted to child, the associated boxes are NOT checked / unchecked accordingly.

     

    Recommended to have a more robust means (you can find this in the online tutorials / COE videos with Conditional Formatting).

     

    Create a Column (called it Level) with the following 

    =IF(COUNT(CHILDREN(Primary@row))>0, COUNT(ANCESTORS())+1)

    The highest level parent in this case will be 1

    The next highest will be 2 and so on.

     

    In the Report formatting, Select the desired Level(s) of Parents.

     

     

     

  • I've done something similar to what Chak describes above.  I create a column called Hierarchy and in it use the functions =COUNT(ANCESTORS()).  In this case, the "Parent" rows will have 0 in the Hierarchy field, subtasks 1, 2, 3, etc.  With this, you could restrict your report that displays the rows to only those where Hierarchy = 0.

    This is also a handy method for Conditional Formatting as you can apply different formats based on Hierarchy values to kind of replicate the nesting in sheets that don't show up nicely in reports.

    Hope this helps.

    - Jeremy

  • Paul E Rayburn
    edited 08/07/22

    I had a similar issue. I wanted to export parent rows only to excel. I didn't find the other two solutions worked for me as any attempt to copy or export, even when filtered, still exported the child tasks. So I came up with a workaround. I'm not sure it will work in all instances. I had over 3000 rows and when filtered down it was only 86 I actually needed. Now, follow these directions carefully as simply copy-pasting the filtered rows into a new sheet still held onto the parent/child data. Create a new sheet called Columns Only from your desired sheet by "Save as New". Be sure to deselect all the data etc. you only want the columns. Maybe there is another way around this by copy the sheet then delete the data but basically the same.

    Then within your original sheet select the cells you want from the filtered set, paste them at the bottom of the same sheet by pasting special "values only" THEN copy that set and paste into the new Columns Only sheet you created. (Trust me on this, Smart sheet still wants to hold onto those child tasks even though you have filtered, copied and pasted values only it will still export the child rows. I tried multiple ways and that was the only successful way.) From the new sheet you can export to excel or I suppose continue working within Smartsheet to create reports etc. but those pesky child tasks are finally removed.

    Hope this helps someone. Or if someone has a simpler method or can determine what I may have been doing wrong with the original suggestions. Maybe there is a global setting I was not aware of etc..

    The only other issue I ran into was I had over 70 columns so I had to get that below 50 for copying from sheet to sheet, so I created a copy of my original data sheet and then deleted unnecessary columns then start the above process.

    *EDIT

    Also, with this method, I was able to use the check box filter so I suppose with this method you could use any type of filter.

    Cheers

    Paul