How to include specific parent and child rows from multiple sheets in a report

01/11/22
Accepted

Hello, I would like to create a report from multiple sheets but I was wondering if there is a way to select specific parent and child rows.

Your help will be much appreciated.

Thank you!

Best Answers

  • Genevieve P.Genevieve P. admin
    Answer ✓

    Hi @Christiana Gkini

    I see you marked @seattlevigilante's response as not answering your question. Would you mind identifying what did/did not help from the above post?

    If you only want to select very specific rows and exclude a number of others, but there's no set criteria for this (ex. you know exactly what rows are important but it's not consistent across sheets), then I would recommend creating a helper column in all of your sheets.

    This column could be a checkbox column that says "For Report" or something like this, where you can check what rows you want to have included. Then you can use the Filter function in a Report to filter out any rows that are not checked. See: Use Filters on Reports

    Cheers,

    Genevieve

  • Genevieve P.Genevieve P. admin
    Answer ✓

    Hi @Christiana Gkini

    Was the column you were looking to choose a multi-select column? Currently grouping can only be applied to single-select columns.

    An alternative would be to create a "metric sheet" and use cross-sheet formulas to create your numerical calculations. It sounds like you would want to use a COUNTIFS formula, and if you're looking at a Multi Select column, you can use HAS as well to see if a column has a specific selection.

    For example, a COUNTIFS works like this:

    =COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

    If you're looking in a multi-select column, your "Criteria" would be

    HAS(@cell, "Criteria")

    So something like:

    =COUNTIFS({Employees Column}, "Employee Name", {Region Column}, HAS(@cell, "Region Name"))

    Then if you're looking for a specific Month, as long as you have a Date column in your sheet, you can use the MONTH function. So the "Criteria" if you were looking for February, or 2, would be:

    MONTH(@cell) = 2

    However the MONTH function needs an IFERROR Statement around it, so it can ignore any blank cells:

    IFERROR(MONTH(@cell), "") = 2

    Here's a full example with the MONTH

    =COUNTIFS({Employees Column}, "Employee Name", {Region Column}, HAS(@cell, "Region Name"), {Date Column}, IFERROR(MONTH(@cell), "") = 2)


    Keep in mind if you're looking into multiple sheets you'll need to create multiple formulas:

    =COUNTIFS(formula) + COUNTIFS(formula 2) + COUNTIFS(formula 3)

    If you have lots of sheets it may be better to adjust your column type to Single Select to be able to use it in a Report, instead.

    If you'd like further help with formulas, you may want to start a New Question here in the Community with Screen Captures and either myself or one of our wonderful members will be happy to help!

    Cheers,

    Genevieve

Answers

  • Genevieve P.Genevieve P. admin
    Answer ✓

    Hi @Christiana Gkini

    I see you marked @seattlevigilante's response as not answering your question. Would you mind identifying what did/did not help from the above post?

    If you only want to select very specific rows and exclude a number of others, but there's no set criteria for this (ex. you know exactly what rows are important but it's not consistent across sheets), then I would recommend creating a helper column in all of your sheets.

    This column could be a checkbox column that says "For Report" or something like this, where you can check what rows you want to have included. Then you can use the Filter function in a Report to filter out any rows that are not checked. See: Use Filters on Reports

    Cheers,

    Genevieve

  • I have the same issue. I have a master Project Plan - the Parents in the project plan are Mini-Projects that are not related to the other parents. I would like a report to break those up to show individually. The only method I can figure out right now is to create a column with a "Flag" and manually assign each task to a parents then filter by that column. What is the point of the Parent and Children functionality?


    • "Who are the children of Parent XYZ?"
    • If Parent = XYZ and IS A CHILD then show CHILD

    That's what I'm looking for in the report filters!

  • Hi @Genevieve P.

    The solution you provided was very helpful.

    However now I have another request to achieve.

    This report was created in oder to display some specific rows in a dashboard. However, now I need to display in the same dashboard 2 pie charts from the data/rows that are not selected for the report ( ex 1 chart employees by region per month and chart 2 type of transactions per month). How can I accomplish this?

    Let me know if this doesn't make sense to you. Your help is much appreciated.

  • Hi @Michael Berman

    Reports evaluate sheets on a row-by-row basis, as you've found. In your scenario, I would suggest creating a helper column in the source sheet to bring the data from Parent "XYZ" into each child row so that each child has a cell that says "XYZ" and you can use this in your Report.

    The formula to do this is a simple PARENT formula, like so:

    =PARENT([Task Name]@row)

    You can turn this into a Column Formula so it applies to every row, then use this in your Report. Let me know if you'd like to see screen capture examples!

  • Hi @Christiana Gkini

    Depending on how your source data is set up, you could create two different Reports to be the sources for each of your Chart widgets. You can use the GROUP and SUMMARIZE feature to create numbers out of your rows, which would then be the numbers for your Pie Charts.

    There's a webinar that goes through how to use Grouping and Summary here: Redesigned Reports with Grouping and Summary Functions

    Cheers,

    Genevieve

  • Hi @Genevieve P.

    Thank you for getting back to me.

    I did have a look in the webinar and tried to go this way but it didn't work, as the column option I wanted was not available for me to choose in the group and summary features.

    Any idea that could help me here?

    Do you by any chance provide support on a zoom call so I could show you how it looks like and what I am trying to achieve?


    Thank you.

  • Genevieve P.Genevieve P. admin
    Answer ✓

    Hi @Christiana Gkini

    Was the column you were looking to choose a multi-select column? Currently grouping can only be applied to single-select columns.

    An alternative would be to create a "metric sheet" and use cross-sheet formulas to create your numerical calculations. It sounds like you would want to use a COUNTIFS formula, and if you're looking at a Multi Select column, you can use HAS as well to see if a column has a specific selection.

    For example, a COUNTIFS works like this:

    =COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

    If you're looking in a multi-select column, your "Criteria" would be

    HAS(@cell, "Criteria")

    So something like:

    =COUNTIFS({Employees Column}, "Employee Name", {Region Column}, HAS(@cell, "Region Name"))

    Then if you're looking for a specific Month, as long as you have a Date column in your sheet, you can use the MONTH function. So the "Criteria" if you were looking for February, or 2, would be:

    MONTH(@cell) = 2

    However the MONTH function needs an IFERROR Statement around it, so it can ignore any blank cells:

    IFERROR(MONTH(@cell), "") = 2

    Here's a full example with the MONTH

    =COUNTIFS({Employees Column}, "Employee Name", {Region Column}, HAS(@cell, "Region Name"), {Date Column}, IFERROR(MONTH(@cell), "") = 2)


    Keep in mind if you're looking into multiple sheets you'll need to create multiple formulas:

    =COUNTIFS(formula) + COUNTIFS(formula 2) + COUNTIFS(formula 3)

    If you have lots of sheets it may be better to adjust your column type to Single Select to be able to use it in a Report, instead.

    If you'd like further help with formulas, you may want to start a New Question here in the Community with Screen Captures and either myself or one of our wonderful members will be happy to help!

    Cheers,

    Genevieve

Sign In or Register to comment.