Sorting custom reports on MLS

Discussions around using and interfacing with the Church MLS program.
colbyj-p40
Member
Posts: 75
Joined: Tue Jan 27, 2009 8:17 am
Location: US

Sorting custom reports on MLS

Postby colbyj-p40 » Wed Mar 04, 2009 10:03 am

Hello, I am a stake clerk. I am building some custom reports/directories for my stake president. I understand how to make custom reports, I understand how to resize the columns and make the report look how I want it. I also know how to click on a column title to get the report to sort. However, is there a way to sort for multiple columns? For example, if I am making a custom report for all Melch. Priesthood holders in a given unit, after creating the report how may I sort the report both alphabetically and by age? When you click on the "name" column it sorts alphabetically. However, if I then click on the "age" column then it sorts by age and mixes up the names. Does this make sense? I suspect it is not possible to sort this way on MLS.

Appreciate any assistance.

User avatar
mkmurray
Senior Member
Posts: 3241
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

Postby mkmurray » Wed Mar 04, 2009 10:10 am

colbyj wrote:Hello, I am a stake clerk. I am building some custom reports/directories for my stake president. I understand how to make custom reports, I understand how to resize the columns and make the report look how I want it. I also know how to click on a column title to get the report to sort. However, is there a way to sort for multiple columns? For example, if I am making a custom report for all Melch. Priesthood holders in a given unit, after creating the report how may I sort the report both alphabetically and by age? When you click on the "name" column it sorts alphabetically. However, if I then click on the "age" column then it sorts by age and mixes up the names. Does this make sense? I suspect it is not possible to sort this way on MLS.

Appreciate any assistance.

Ya know, I haven't figured out how to correctly sort by more than one criteria either.

For instance, in my case I had one column for the Primary Phone Number and another column for the Secondary Phone Number (in that precise order). I was trying to sort one column first and then the other column next, thinking it would further sort the sorted results. Some times I get the results I wanted, and sometimes the second sort completely messes up my first sort.

In my case, it seemed if I only sorted by Secondary Phone Number, that it also sorted the Primary Phone Number automatically. I wonder if it has to do with left-to-right precendence of the columns or something?
Many questions are already answered on the LDSTech wiki. Check it out!

User avatar
aebrown
Community Administrator
Posts: 14685
Joined: Tue Nov 27, 2007 8:48 pm
Location: Sandy, Utah

Postby aebrown » Wed Mar 04, 2009 10:17 am

colbyj wrote:Hello, I am a stake clerk. I am building some custom reports/directories for my stake president. I understand how to make custom reports, I understand how to resize the columns and make the report look how I want it. I also know how to click on a column title to get the report to sort. However, is there a way to sort for multiple columns? For example, if I am making a custom report for all Melch. Priesthood holders in a given unit, after creating the report how may I sort the report both alphabetically and by age? When you click on the "name" column it sorts alphabetically. However, if I then click on the "age" column then it sorts by age and mixes up the names. Does this make sense? I suspect it is not possible to sort this way on MLS.


I think I understand what you are asking for, but I cannot duplicate in MLS the behavior you are reporting. When I click on one column header and then another, MLS sorts by the second column and then by the first column. So to use your example, if I click on NAME, it sorts alphabetically. If I then click on AGE it sorts numerically by age, and for those rows that all have the same age, that group of rows is still sorted alphabetically by name.

This seems to be exactly what you are asking for, and in every test I do, it preserves the previous order as a secondary sort key when I change the primary sort key. I can even click on three headers in succession and the first one column clicked becomes the tertiary sort key, the second one is secondary, and the last column clicked is the primary sort key.

kjheinz
New Member
Posts: 3
Joined: Wed Mar 04, 2009 7:04 am
Location: Tacoma, WA, USA

Postby kjheinz » Wed Mar 04, 2009 10:19 am

I would recommend downloading it to a CVS file and using exel to do your sorting instead.

User avatar
mkmurray
Senior Member
Posts: 3241
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

Postby mkmurray » Wed Mar 04, 2009 10:20 am

kjheinz wrote:I would recommend downloading it to a CVS file and using exel to do your sorting instead.

That's actually a pretty good suggestion for those that feel more comfortable in that type of application.
Many questions are already answered on the LDSTech wiki. Check it out!

User avatar
mkmurray
Senior Member
Posts: 3241
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

Postby mkmurray » Wed Mar 04, 2009 10:27 am

Alan_Brown wrote:I think I understand what you are asking for, but I cannot duplicate in MLS the behavior you are reporting. When I click on one column header and then another, MLS sorts by the second column and then by the first column. So to use your example, if I click on NAME, it sorts alphabetically. If I then click on AGE it sorts numerically by age, and for those rows that all have the same age, that group of rows is still sorted alphabetically by name.

This seems to be exactly what you are asking for, and in every test I do, it preserves the previous order as a secondary sort key when I change the primary sort key. I can even click on three headers in succession and the first one column clicked becomes the tertiary sort key, the second one is secondary, and the last column clicked is the primary sort key.

It appears your comments apply to my post as well then. I'll have to look more closely into the specific situation I was trying to accomplish, and perhaps my sorting expectations were flawed.

As for colbyj's comments, I wonder if his expectations were mislead as well. We have to remember that you can only have one field chosen for the primary sort. You can't have it sorted both by age and by name at the primary sorting level. That's an unrealistic expectation that is impossible to perform.

Also, it sounds like you need to perform your sorts in backwards order (and now that I'm thinking more closely about it, it makes perfect sense). Your tertiary sort should be first, while your primary sort should be your last column click, correct?
Many questions are already answered on the LDSTech wiki. Check it out!

User avatar
aebrown
Community Administrator
Posts: 14685
Joined: Tue Nov 27, 2007 8:48 pm
Location: Sandy, Utah

Postby aebrown » Wed Mar 04, 2009 10:30 am

mkmurray wrote:Ya know, I haven't figured out how to correctly sort by more than one criteria either.


Here is my specific example:

  1. I created a custom report for MP with Full Name, Age, and Birth Country.
  2. Generated the report.
  3. I clicked on the column headers in this order: Full Name, Birth Country, Age
  4. The results are as follows:

Code: Select all

Full Name               Age  Birth Country
HIBBERD, DAVID SPENCER   24  England
CHAPMAN, MICHAEL JAMES   25  United States
DOVE, RAMON HERACLIO     26  Mexico
CLAWSON, JOSHUA ESAIAS   26  United States
MENEGAZ, KERRY JAMES     26  United States
CARDON, BRIAN CASH       27  United States
ROLLINS, JONATHAN RALPH  27  United States
BOWERS, BENJAMIN EARL    28  United States
RANGEL, CLARK EDWARD     29  United States
CARDON, TRAVIS CASH      30  United States
JEX, ALAN VESTEN         30  United States
LIEDL, JEREMIAH          30  United States
RUSSELL, JAMES ERIK      30  United States
HAMBY, MICHAEL JAMES     31  United States
KEETING, JAMES PHILLIP   31  United States
MARSH, CHRISTOPHER ERVIN 31  United States
POVEY, SHAWN FRANKLIN    31  United States
Look at the 31-year-olds. Their names are sorted properly in alphabetical order (since they're all born in the US). Look at the 26-year-olds. Their names are sorted with the one born in Mexico coming first, and then the two US people are sorted in alphabetical order. That's exactly what I would expect.

If I were to click simply on Full Name, then Age, the 26-year-olds would appear in Clawson, Dove, Menegaz order, which is also exactly what I would expect.

I don't understand how anyone would want it to work differently from this. There are two additional features that would be nice, but they wouldn't change the sort order of the results, just the ease of use:

  1. Make the sort order specification explicit, rather than depending on clicking on column headers in reverse order.
  2. Store the sort order with the report so that the report always sorts that way (right now you have to click on the column headers each time you generate the report, since the report initially appears sorted by left-to-right columns).

User avatar
aebrown
Community Administrator
Posts: 14685
Joined: Tue Nov 27, 2007 8:48 pm
Location: Sandy, Utah

Postby aebrown » Wed Mar 04, 2009 10:40 am

mkmurray wrote:...it sounds like you need to perform your sorts in backwards order (and now that I'm thinking more closely about it, it makes perfect sense). Your tertiary sort should be first, while your primary sort should be your last column click, correct?


That's right. And you can even extend that to more columns, although it's unlikely you'd have a data set with ties that extend down to the third level and would require a fourth key to sort those out.

And although you're right that the reverse clicking order makes perfect sense, it's not necessarily immediately obvious. I know perfectly well how it works, yet as I was doing my test, I clicked in the 2, 3, 1 order when I really wanted 3, 2, 1. Thus my suggestion for improving ease of use by making the sort order explicit (similar to the way Excel or any number of report generators allow sort order to be specified, where you would say essentially "Sort by Age, then by Birth Country, then by Full Name").

russellhltn
Community Administrator
Posts: 20723
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

Postby russellhltn » Wed Mar 04, 2009 11:40 am

kjheinz wrote:I would recommend downloading it to a CVS file and using exel to do your sorting instead.


Or OpenOffice's Calc which can be found on most administrative computers. :D
Have you searched the Wiki?
Try using a Google search by adding "site:tech.lds.org/wiki" to the search criteria.

colbyj-p40
Member
Posts: 75
Joined: Tue Jan 27, 2009 8:17 am
Location: US

Postby colbyj-p40 » Wed Mar 04, 2009 2:15 pm

This is all great feedback. Thank you. It appears we have a couple of solutions for me to try. Thanks much guys for your assistance!


Return to “MLS Support, Help, and Feedback”

Who is online

Users browsing this forum: No registered users and 1 guest