Reporting Hierarchy

Options
mg3245
mg3245 ✭✭
edited 04/24/21 in Formulas and Functions

I have a sheet with several parent, child and grandchild rows. I'm creating a Report from this sheet but I am unable to filter and see child or grandchild wise. I have created a WBS System (1, 1.1, 1.1.1 -> Parent,Child,Grandchild) as well but unable to group/filter using this. Is there any work around for this?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Good evening,

    Add a helper column [level] and instead the column formula:

    =COUNT(Ancestors())

    Your parent rows will have a value of 0, children 1, etc. Use your [level] column value to filter.

    Work for your project?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • mg3245
    mg3245 ✭✭
    Options

    Hi Mark,


    I already have this formula applied in a separate column. However, the issue comes up where each Parent row refers to a separate client eg: Hyundai/Toyota and the child row refers to its subdivision eg : Hyundai Cars/Toyota Electric Cars while the grandchild row refers to Hyundai Cars South Africa/Toyota Cars New Jersey.


    In this case, even with the ancestor formula applied, I am unable to filter in my report to see all the grandchild rows which are listed in my sheet under Hyundai. I hope I've explained my problem well. Else can attach a sample report to explain it better.

  • J Tech
    J Tech ✭✭✭✭✭
    Options

    Hi

    Otherwise you can create a help field call parent:

    =Parent(PrimaryField)

    You can then filter than that

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!