sort in report
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:
- i want to show in report the parents row and beneath te children row.
- want to sort by ["size"] 32mm, 50mm, 63mm, 110mm etc.
- sort by ["latter"]
and here the problem - i cant do that.
any idea?
Best Answer
-
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
Answers
-
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.
-
@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.
-
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
-
Are you able to provide a screenshot of the sheet itself?
-
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
-
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
-
great now its working!
thanks 😀
-
Excellent. Happy to help! 👍️
-
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..
-
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.
-
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"
-
Yes. I understand what you WANT to happen.
I am suggesting that you check the actual data to ensure it's accuracy.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives