Page 1 of 2

Custom Report Sorting Bugs

Posted: Wed Sep 30, 2009 9:01 pm
by mkmurray
So I forgot about the Birthday List that you can print out per organization, which is definitely the better way to do this.

Regardless, I created a custom report to make a birthday list for one specific organization. The only criterion was "Organization Class is Elders 1." The Column Order was as follows: Birthday, Preferred Name, Primary Phone Number, and Address - Street 1.

The problem was with the sort order when viewing the report. By default, it sorted by the first column, being Birthday. However, it was not sorted correctly. It went Jan through Dec, followed by one unknown birthday (a non-member record), and then proceeded to do more known birthdays Jan through Dec again. There were no repeats in the list and it was about half and half of the quorum in each Jan through Dec list. To clarify, it's just one report and one view, but the sorting didn't correctly apply to the list in its entirety.

To see if perhaps sort was just bad for the Birthday field (being of the format "DD MMM", or 01 Jan for example), I tried removing the Birthday field and using Birth Month and Birth Day as the first two displayed and sorted fields. This did not improve my results and in fact appeared to be sorted in the exact same incorrect manner.

I have also tried clicking on the column headers to see if I could somehow refresh the sort to see if it improved. The first click on Birthday did nothing (as that was already the default sort), then the second click sorted it descending, and then the third click sorted it ascending again. Surprisingly, it did improve the sorting quite a bit, but didn't completely correct it. This time all but about 3 birthdays sorted in the first "half" of the list, then the blank birthday, and then the second "half" of the list contained 2 Jan birthdays that for some reason didn't get grouped with the rest of the Jan birthdays. The same weird sorting "improvement" happened when trying Birth Month and Birth Day in place of Birthday.

As I mentioned at the top of this post, I have found the Birthday List report for each organization and that is certainly the better way to do this. But I think this exercise has uncovered bugs in the Custom Reports module.

I want to also note the unique factors in my unit that don't apply to the majority of units and could be contributing to the odd behaviors. My ward has 2 Elders' Quorums and we also have that non-member record that is local to our unit (I actually have a question about that, and I have started another thread: http://tech.lds.org/forum/showthread.php?t=3796; it appears I have a temporary record and a nonmember record for this individual and only the temporary record with no birth date is making it into this custom report). I could see how one of those two factors could be throwing a wrench into the works.

One last thing, I just noticed that the sorting when you break up the birthday into two columns (Month and Day), the sorting for the second column (Day) does not happen at all. I would assume the default sort should always be First Column, Second Column, Third Column, etc. and not merely First Column. I remember us talking about sort orders before (and perhaps Alan_Brown can help me remember this past discussion), but I just can't think why it would make sense to only sort the first column by default, and not follow on down the rest of the columns for sorting.

Can others reproduce these bugs?

Thanks in advance.

Posted: Wed Sep 30, 2009 9:21 pm
by mkmurray
I just edited the post below, so you may want to reread the part where I added a link to another thread and some commentary if you've already read the post below before my edits.

Posted: Wed Sep 30, 2009 11:30 pm
by russellhltn
Can you check the underlying data to see if it's being sorted by birth DATE (not birthday)?

Posted: Wed Sep 30, 2009 11:38 pm
by mkmurray
RussellHltn wrote:Can you check the underlying data to see if it's being sorted by birth DATE (not birthday)?
I think I see what you mean, and I could try adding that column into the report to see if it helps make sense of how the data is being sorted. I do have to admit it would be odd functionality, since I specifically didn't include that column.

However, I was hoping someone with MLS readily available could verify my results and perhaps play with the data (like in the way you have just suggested) in an attempt to come up with an explanation that could help Church developers.

Posted: Thu Oct 01, 2009 12:30 am
by aebrown
mkmurray wrote:The problem was with the sort order when viewing the report. By default, it sorted by the first column, being Birthday. However, it was not sorted correctly. It went Jan through Dec, followed by one unknown birthday (a non-member record), and then proceeded to do more known birthdays Jan through Dec again. There were no repeats in the list and it was about half and half of the quorum in each Jan through Dec list. To clarify, it's just one report and one view, but the sorting didn't correctly apply to the list in its entirety.

The problem with custom reports sorting by birthday is definitely related to nonmembers. I just tried this on our stake data, which includes several nonmembers. The result of sorting on Birthday was a list that contained several sorted chunks. The list would contain a group of member records sorted properly, then a nonmember record with no birthdate, then another group of member records sorted properly, then another nonmember record, etc. Suppose we had 30 nonmember records in our stake. Then there would be 30 sorted groups of members. I'm sure this is exactly what you are seeing, but since you are dealing with far fewer nonmembers by restricting the report to one organization in a ward, the pattern isn't as obvious.

Somehow as the sort algorithm is processing records, as it comes across a nonmember record, it acts like a "stopper" and the algorithm stops processing the group that has already been processed and starts a new group. It seems like the bug is caused by something like that.

If you add a criterion to the custom report that says "Member Status - is - member" then you will find that all these odd problems go away.
mkmurray wrote:One last thing, I just noticed that the sorting when you break up the birthday into two columns (Month and Day), the sorting for the second column (Day) does not happen at all. I would assume the default sort should always be First Column, Second Column, Third Column, etc. and not merely First Column. I remember us talking about sort orders before (and perhaps Alan_Brown can help me remember this past discussion), but I just can't think why it would make sense to only sort the first column by default, and not follow on down the rest of the columns for sorting.

You can talk about what makes sense to you, but the (mostly) simple fact is that MLS only sorts on one column. I wouldn't call this a bug, but it seems like a limitation that wouldn't be that hard to remove.

However, MLS does retain the sorted order of records that have the same sort key for subsequent sorts on other columns. I'm not sure how to explain that better, except by giving an example. If you have columns for "birth day" and "birth month" you can sort by "birth day" (by clicking on that column header) and you will see the list sorted from 1 to 31. Then you can click on the "birth month" column header and the rows will now be sorted first by "birth month" and then by "birth day". Had you sorted at the very beginning by name, then you would see all the people who share an April 1 birthday, for example, listed in alphabetical order.

So you can do a manual multi-column sort by clicking on the column headers you want to be the sort keys in reverse order. However, a multi-column sort can only be done manually; when a report is initially displayed, it is sorted by the first column only. How the other columns are sorted when there are multiple rows with the same value in the first column seems to be random -- or at least I can't figure out the pattern as to how MLS breaks ties when the report is first displayed.

Posted: Thu Oct 01, 2009 2:51 am
by russellhltn
mkmurray wrote:I think I see what you mean, and I could try adding that column into the report to see if it helps make sense of how the data is being sorted. I do have to admit it would be odd functionality, since I specifically didn't include that column.
My working theory was that the Birth Day, Month, etc was only a "view" to the Birth Date field and the sort was acting before the view kicked in.

But Alan's post is suggesting something else.

Posted: Thu Oct 01, 2009 7:03 am
by aebrown
RussellHltn wrote:My working theory was that the Birth Day, Month, etc was only a "view" to the Birth Date field and the sort was acting before the view kicked in.

But Alan's post is suggesting something else.
Interesting theory, but since adding the "Member status - is - member" criterion completely eliminates the problem, it seems pretty clear that the problem is related to processing nonmember records.

Posted: Thu Oct 01, 2009 8:32 am
by mkmurray
Alan_Brown wrote:The problem with custom reports sorting by birthday is definitely related to nonmembers.
Thank you for the verifying this.
Alan_Brown wrote:You can talk about what makes sense to you, but the (mostly) simple fact is that MLS only sorts on one column...So you can do a manual multi-column sort by clicking on the column headers you want to be the sort keys in reverse order. However, a multi-column sort can only be done manually; when a report is initially displayed, it is sorted by the first column only.
Yes, I knew this were the case about manually sorting in reverse order. I still can't understand why only sorting the first column and leaving the other columns arbitrarily ordered would make any sense. Can anyone name one situation where that would be the best sort order?

If two or more rows had the same data in column 1 (be it birthday, name, address, phone), then it only makes sense to try and create some semblance of order by then sorting by the second column of data (and if necessary the third column and so on, so forth). What if I created a report with the first column being last name and we had 15 Johnsons? Those 15 rows would be sorted in a illogical, random order.

Posted: Thu Oct 01, 2009 8:44 am
by aebrown
mkmurray wrote:Yes, I knew this were the case about manually sorting in reverse order. I still can't understand why only sorting the first column and leaving the other columns arbitrarily ordered would make any sense. Can anyone name one situation where that would be the best sort order?
I can't. I never said it was the preferred implementation -- I only said that this is how MLS works right now.

The only advantages of the current implementation would be that it takes a bit less code and development time. I suppose it also performs faster to sort on only one key -- for example, if you have a stake report with 3000 members that includes 20 columns, sorting on 20 columns would indeed take longer than just one column. But I doubt that performance inspired the current implementation; I would guess that it was simply limited vision.

Posted: Thu Oct 01, 2009 10:29 am
by jbh001
mkmurray wrote:I want to also note the unique factors in my unit that don't apply to the majority of units and could be contributing to the odd behaviors. My ward has 2 Elders' Quorums and we also have that non-member record that is local to our unit (I actually have a question about that, and I have started another thread: http://tech.lds.org/forum/showthread.php?t=3796; it appears I have a temporary record and a nonmember record for this individual and only the temporary record with no birth date is making it into this custom report). I could see how one of those two factors could be throwing a wrench into the works.
This implies that the temporary record was assigned as a member of one of the elders quorums. I'm curious under what circumstances it would be necessary to create a temporary/nonmember record such that they would show up as a member of an elders quorum. I'm having a hard time imagining a likely scenario.