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

Christiana Gkini · 01/11/22

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!

Genevieve P. · 01/19/22

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. · 02/28/22

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 columnhasa specific selection.

For example, a COUNTIFS works like this:

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

如果你想在多选专栏中,你的“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

然而本月函数需要一个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

seattlevigilante · 01/11/22

Hello@Christiana Gkini

This is currently not supported in Reports. When you can, please submit anenhancement requestto have this be considered for future development in Smartsheet!

In the meantime, an alternate solution is to change the background color for parents/children OR use conditional formatting to differentiate the parents/children in the report. See image below as sample.

Hope this helps!

Thank you~

Genevieve P. · 01/19/22

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

Michael Berman · 02/10/22

Hi @Christiana Gkini<\/a> <\/p>

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

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. <\/p>

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<\/a><\/p>

Cheers,<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Christiana Gkini\",\"userID\":143328}}},{\"insert\":\" \\nI see you marked \"},{\"insert\":{\"mention\":{\"name\":\"seattlevigilante\",\"userID\":143327}}},{\"insert\":\"'s response as not answering your question. Would you mind identifying what did\\\/did not help from the above post? \\nIf 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. \\nThis 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: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481251-filter-data-in-reports\"},\"insert\":\"Use Filters on Reports\"},{\"insert\":\"\\nCheers,\\nGenevieve \\n\"}]","format":"rich","dateInserted":"2022-01-19T15:33:01+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-25T20:36:29+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/316642#Comment_316642","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/316642#Comment_316642

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!

Christiana Gkini · 02/23/22

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.

Genevieve P. · 02/23/22

https:\/\/community.smartsheet.com\/discussion\/comment\/316642#Comment_316642<\/a><\/p>

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? <\/p>

"Who are the children of Parent XYZ?" <\/li>

If Parent = XYZ and IS A CHILD then show CHILD<\/li><\/ul>
That's what I'm looking for in the report filters!<\/p>","bodyRaw":"[{\"insert\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/comment\\\/316642#Comment_316642\",\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/comment\\\/316642#Comment_316642\"}},{\"insert\":\"\\n\"},{\"insert\":\"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? \\n\\n\\\"Who are the children of Parent XYZ?\\\" \"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"If Parent = XYZ and IS A CHILD then show CHILD\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"That's what I'm looking for in the report filters!\\n\\n\\n\\n\"}]","format":"rich","dateInserted":"2022-02-10T22:08:08+00:00","insertUser":{"userID":130628,"name":"Michael Berman","url":"https:\/\/community.smartsheet.com\/profile\/Michael%20Berman","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m6FY_7G7Z_w!!INsf3t9kHTf","dateLastActive":"2022-03-01T21:42:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/319325#Comment_319325","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/319325#Comment_319325

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!

Genevieve P. · 02/23/22

Hi @Genevieve P.<\/a> <\/p>

The solution you provided was very helpful.<\/p>

However now I have another request to achieve. <\/p>

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?<\/p>

Let me know if this doesn't make sense to you. Your help is much appreciated.<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Genevieve P.\",\"userID\":91566}}},{\"insert\":\" \\nThe solution you provided was very helpful.\\nHowever now I have another request to achieve. \\nThis 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?\\nLet me know if this doesn't make sense to you. Your help is much appreciated.\\n\"}]","format":"rich","dateInserted":"2022-02-23T12:11:26+00:00","insertUser":{"userID":143328,"name":"Christiana Gkini","url":"https:\/\/community.smartsheet.com\/profile\/Christiana%20Gkini","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf-hNm8WhOgG9NXB7Zou6oQaUyDHZZ2iwrHmrrz=s96-c","dateLastActive":"2023-07-21T14:43:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/320512#Comment_320512","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/320512#Comment_320512

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

Christiana Gkini · 02/28/22

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. · 02/28/22

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 columnhasa specific selection.

For example, a COUNTIFS works like this:

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

如果你想在多选专栏中,你的“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

然而本月函数需要一个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

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

Best Answers

Answers

Categories