Workaround for Indent/Outdent in Reports?

Adriana Herrera

Hi there? Any way I can pull out a report in the next X Days that only shows "Main Deliverable" and not all the Indented tasks?

Kelly Moore

Sorry Adriana,

I'm still unclear how the data in your sheet is organized, and exactly what you're trying to collect.

If you want all of the parent rows to be pulled in, add the first formula to your SHEET. This formula does not differentiate parents and sub parents. Add that helper column to the WHAT in your report.

If you only want specific levels of parents to be included in the report, add the 2nd formula to the SHEET. Use that column in your report with the levels designated that you want.

To pull days also into your report, this assumes the parent rows contain that data. If you haven't already, you might want to look at one of the project management templates in the Solution Center (in your menu under the + sign. Examples there will show how data can be rolled up to parent rows

Kelly

Kelly Moore

Hi Adriana

Yes, I can quickly suggest two ways to accomplish this, either one needs a helper column in your sheet. The indents are Child rows. Some children can also be parents- introducing another level of indentations. I usually refer to these as Sub Parents. I'm not sure from your description if your Main Deliverable has only one level of indents, or multiple levels of indentations, and if does have multiple levels, do you want all parents or just the top level.

Because I like to have any parent, including the sub parents, designated as a Parent row, I have a helper column in every sheet called Parent. By definition, a parent will always have at least one child, therefore I can count children. I could make my Parent column a checkbox column, but I keep mine as a Text/Number field because it's easy for me to quickly scan formulas and find the reference to "." in the formula text. It's my preference. If you want the any level Parent rows (top level and sub parents), this is your solution. You can put this helper column in your report and pull in any row that is not blank (or is checked if you used a checkbox)

In the Parent column add this formula. (If using a checkbox column, substitute "." with a 1

=IF(Count(Children([Main Deliverable]@row)>0, ".")

If you have multiple levels of Parent rows (different indent levels) and you only want the top level, smartsheet has a function called ancestors. You can use this to designate a specific level of hierarchy to collect on your report. For this, the helper column must be a Text/Number column. Put this formula in the helper column. The top most parent-rows will have a designation of 1. In the report , pull in this column name (whatever you called it) with the criteria = 1.

=IF(COUNT(CHILDREN([Main Deliverable]@row)) > 0, COUNT(ANCESTORS()) + 1)

I hope this helps

Kelly

Adriana Herrera

My deliverable has multiple indents :)

If I pull a report will I be able to filter just those ones with the "parent" criteria + business + X Days?

Kelly Moore

Sorry Adriana,

I'm still unclear how the data in your sheet is organized, and exactly what you're trying to collect.

If you want all of the parent rows to be pulled in, add the first formula to your SHEET. This formula does not differentiate parents and sub parents. Add that helper column to the WHAT in your report.

If you only want specific levels of parents to be included in the report, add the 2nd formula to the SHEET. Use that column in your report with the levels designated that you want.

To pull days also into your report, this assumes the parent rows contain that data. If you haven't already, you might want to look at one of the project management templates in the Solution Center (in your menu under the + sign. Examples there will show how data can be rolled up to parent rows

Kelly

Ashley Pierce

KDM: I'm trying to accomplish something similar, but the formula provided is not working. Here is what I'm trying to accomplish: I'm making a FMS Status Report that will pull the teams status sheet, along with the Project Plan; however, the project plan has hierarchy's that disappear when it's in the report. How do I update the Project Plan sheet to show the hierarchy in the report? I added a "Parent" column as a checkbox, with the Parent rows checked, but I don't know what to do from there.

Thanks!

Ashley Pierce

Jeff Casto

Hi all! @Genevieve P. (unfortunately for you, I chose to annoy you with this).

I'm trying something similar and even more basic.

• I have a main status sheet that I want the Client and the Media team to see and edit. However, I don't want them to see certain columns. So, I was thinking the most elegant solution in Smartsheet is to create separate reports for the Client and the Media team (assuming each of them have different, specific views).
• I created one of those reports (or views as is the objective) and it looks gorgeous. The only issue I have is the indents don't come into the report. The group head is in line with the subgroups. Here are a couple screenshots to show what I'm saying.

Here's the screenshot from the original sheet - has indent subs

Here's the screenshot from the report - does not have indent

• The report looks good to me, but someone may say something. I thought of coloring the group heads to signify they have subs or perhaps putting other columns in the original sheet so I could group the heads and subs, but that seemed a bit over-kill when the most elegant solution seems to be just pulling the columns into a report. I am semi-following the above previous comments, but not fully getting it.

If you have a moment, is there possibly either a better/more elegant solution for creating editable sheet views that restrict some columns per user or if not, perhaps a solution to pulling in the indent that doesn't take a lot of re-structuring the original sheet? (A little restructuring is fine, but looking for a clean/elegant process...much like what Smartsheet already did for me!)

Thank you!!!!!!

Genevieve P.

Hey @Jeff Casto,

Thanks for the tag, I'm happy to help! 🙂

Currently Reports evaluate sheets on a row-by-row basis, meaning they do not keep hierarchy structure and read each row separately, as you've found.

What I would personally do here is have a helper column set up in the sheet to grab the Parent titles:

=PARENT([Primary Column]@row)

You can make this a Column Formula and hide it on your source sheet.

Then in the Report, use this as the column to GROUP by. That way it creates a Report Header with the Parent name as the Grouping! (Note that this will organize your Parent titles alphabetically.)

You can also filter out the Parent rows to make sure that the "Parent Name" helper column is not blank.

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

Jeff Casto

Thanks @Genevieve P. ! I'm trying it out and it looks promising. There are a few adjustments I'll try to make, but I understand what this solution does and will kick it around! I'm trying it on a couple other reports too and those are working nicely! Thanks you again for the great advice.

Jeff