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?
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
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
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?
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.
Hi all! @Genevieve P. (unfortunately for you, I chose to annoy you with this).
I'm trying something similar and even more basic.
Here's the screenshot from the original sheet - has indent subs
Here's the screenshot from the report - does not have indent
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!)
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:
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!
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.
We have a spreadsheet with data that needs to be updated/validated twice a year. We wanted to use a workflow, but some owners/approvers of the data have 150 rows to their name. We don't want to flood their email with that many emails or have to go through 150 lines that way. We are working with the idea of sending an…
Hello- I am trying to remove the item names from the right side of the gaant chart. Is there a way to get rid of those and preferably add other column information instead? I see the settings pop up but there is no option to get rid of those labels. Thank you!
We would like to request the ability to add multiple conditions to a Conditional Formatting rule. For example: If Step="Backlog" and Flag=Yes, mark row orange.
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2024. All Rights Reserved Smartsheet Inc.