sort in report

Netanel yosef

i have a repotr based on sheet with ~1500 rows (parents and children).

as i know there is no way to show the parent and children in repost so that i mark in color the parent and sort the report to show the parent and children.

but, now i want to sort with another level:

1. i want to show in report the parents row and beneath te children row.
2. want to sort by ["size"] 32mm, 50mm, 63mm, 110mm etc.
3. sort by ["latter"]

and here the problem - i cant do that.

any idea?

Paul Newcome

It looks like you are missing the parent helper section from above. You can use a formula such as

=IF(COUNT(ANCESTORS([Line Number]@row)) = 0, [Line Number]@row, PARENT([Line Number]@row))

Then your report would be sorted by

Size

Parent Helper

Letter Helper

Alejandra

Hi @Netanel yosef,

Reports don't currently display hierarchy, but here's a great post by @Paul Newcome that offers a workaround solution: https://community.smartsheet.com/discussion/comment/241114#Comment_241114

When you have a moment, submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet. 

Paul Newcome

@Alejandra Thanks for the shout out!

@Netanel yosef I think that part of your problem is the data that is in the child rows. When sorting, numerical values will always come before letters. So if you look at the two sections where the sort is off within the child rows, if you break the string down and go character by character, you will see that they are in fact sorted correctly based on this logic.

50mm-659A-SP1_1

50mm-659A-SP1_S

This is the correct sorting for these as the 1 comes before the S. If you notice, the last two entries in this section are in order because they are identical until the last letter.

The 50mm-749A-SP1 section is the same logic. The 1 comes before the S in the sort. Identical values after that character until the very end means that they are sorted correctly.

To help with this... You can add in two columns on the source sheet. One that pulls the parent row data only, and one that pulls the final letter only.

Then you can sort by the parent row helper in the first tier and the letter helper in the second tier. I believe this should clear up that particular issue of the child rows not being sorted correctly.

.

.

As for the parent rows not being sorted correctly, it is going to be the same logic. Smartsheet is only seeing a text string, so if you look at the first character in each of the parent rows, 1 comes before 3. Then when comparing the parent rows that start with 3, special characters come before numbers, so 3" will come before 32.

To help with this...

I will suggest another helper column. One that converts the size portion into an actual numerical value (that is common among rows such as all converted to mm). So this column would look similar to the below when compared to the data in the main column.

This will give you common data so that you can sort on size.

So tier 1 of the sort would be on the Size Helper. Tier 2 would be the parent helper, and tier 3 would be the letter helper.

.

Give this a try and let me know if that takes care of our sorting. Also let me know if you need any assistance with the formulas to get this set up. I do feel that we can get this to work for you.

Netanel yosef

hi,

that exaclly what i did.

i seperate the last latter and the first numbers "size" to two different column

but in the report its not sorted right...

can i share the sheet and try to help or explain what should i am need to do

Paul Newcome

Are you able to provide a screenshot of the sheet itself?

Netanel yosef

here screen shot from the sheet:

and the report that show only the row that has a "Eng. Approved"

and here link to sheet abd report

https://app.smartsheet.com/b/publish?EQBCT=cbde379f1b71429486c9e0a3ca388012

https://app.smartsheet.com/b/publish?EQBCT=11cda6d0c95d4ea7ac26f1b7a450f57e

Paul Newcome

It looks like you are missing the parent helper section from above. You can use a formula such as

=IF(COUNT(ANCESTORS([Line Number]@row)) = 0, [Line Number]@row, PARENT([Line Number]@row))

Then your report would be sorted by

Size

Parent Helper

Letter Helper

Netanel yosef

great now its working!

thanks 😀

Paul Newcome

Excellent. Happy to help! 👍️

Netanel yosef

Hey

I ran into another problem following the formula change

Sorting changed so that the part of

parent row appears below and the children row appear up..

Paul Newcome

You are going to want to check that particular parent row's data against the other parent rows' data and see what is different. From your screenshot, I see that the second column is blank where the other rows have either a letter or a number.

Netanel yosef

The report should be presented the parent row and the children row under. according the sort (size, latter)

means i want to see the parent row for example: "32MM-139S-SPZ-W-N2" and the children rows under that get the same name plus latter A B C for example: "32MM-139S-SPZ-W-N2-A" , "32MM-139S-SPZ-W-N2-B" , "32MM-139S-SPZ-W-N2-C"

Paul Newcome

Yes. I understand what you WANT to happen.

I am suggesting that you check the actual data to ensure it's accuracy.