I have a project plan in Smartsheet and would like to generate a chart that shows workstreams stacke

Gary W
Gary W
edited 03/11/22 in Smartsheet Basics

I would to display the functional workstreams above stacked in a chart and show milestones

Answers

  • Hi @Gary W

    Charts require numerical data to map on to a graph or chart. Do you have associated numbers for each of these workstreams? For example, are you counting how many rows are beneath each parent row, or the percentage of work for each stream?

    If you're looking for a Count, what I'd suggest doing is add a column to this sheet which brings the Parent name into each Child row. That way you can use the GROUP feature in a Report to Group by that helper column and automatically Summarize based on each group.

    Here's another Community Post where I have screen captures explaining how to do this: Way to tell a report to include the parent row info for child tasks?

    If I've misunderstood what you're looking to do, it would be helpful to know more information about this sheet. What exactly is it you're looking to put on a Dashboard? Do you just need the titles of the workstreams, or are there calculations you're looking to do? Can you post another screen capture or image of your ideal output?

    Thanks!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Thanks Genevieve. I'm having problems, or at least think i am with the PARENT function. I enter the function and it highlights the correct parent. When i tab out from the field nothing appears. On other tasks it works. Very confusing. Below is a snip from my Project Plan.


    As an example, I'm looking to generate something like the below. I'd also like to show dependencies between work streams of that's possible.


  • Hi @Gary W

    You want to use @row instead of 2 in the Parent function. You're looking for the Parent of the current row, which will end up being "Major Milestones". But since [Task Name]2 is a Parent, it will return a blank cell.

    Try:

    =PARENT([Task Name]@row)

    However it sounds like you actually want the opposite... you only want the Parent rows in the Report, is that correct? If so, you'd actually want a formula to say "Parent" if it's a parent row. You can use COUNT to see if the current row has any Children:

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, "Parent", "Child")

    Then you can Filter on this column in your Report so you only have the Parent rows. Is this closer to what you'd like?

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Genevieve,

    I'm fairly new to smartsheet. I'm unable to find how to create a Group report. I don't see the option on the toolbar.

    Also, If i go the COUNT path, where exactly would i put that command?


    Gary

  • Hi @Gary W

    A Report is a separate type of Smartsheet item. You can create a Report by looking at a source sheet (or multiple sheets) and filtering by something specific, then Grouping and Summarizing in this Report. Here are a couple free webinars that run through these features: SmartStart: Reporting and Redesigned Reports with Grouping and Summary Functions

    However, it sounds like you actually just want to pull those top Parent rows instead.

    If we use the IF statement I have above:

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, "Parent", "Child")

    What this does is it checks to see if the current row is either a Parent or a Child (see: Hierarchy: Indent or Outdent Rows)

    You put this in a column in your sheet and it checks each Task Name to see if it has indented rows beneath it or not. Now that you have this in your sheet, you can use it as a Filter in a Report.

    In your folder, create a new Report:

    This will be a Row Report, see: Build a row report

    Then find your Source Sheet and select the relevant columns you want to pull through. Then you can Filter by this helper Parent column and only bring in rows that say "Parent" (see: Use Filters on Reports)

    The Report can be displayed as a Gantt view if you prefer seeing the timeline that way, however dependencies won't show up in a Report as it's evaluating each row separately.

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now