Sorting custom reports on MLS
-
- Member
- Posts: 75
- Joined: Tue Jan 27, 2009 8:17 am
- Location: US
Sorting custom reports on MLS
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.
Appreciate any assistance.
- mkmurray
- Senior Member
- Posts: 3266
- Joined: Tue Jan 23, 2007 9:56 pm
- Location: Utah
- Contact:
Ya know, I haven't figured out how to correctly sort by more than one criteria either.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.
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?
- aebrown
- Community Administrator
- Posts: 15153
- Joined: Tue Nov 27, 2007 8:48 pm
- Location: Draper, Utah
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.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.
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.
- mkmurray
- Senior Member
- Posts: 3266
- Joined: Tue Jan 23, 2007 9:56 pm
- Location: Utah
- Contact:
- mkmurray
- Senior Member
- Posts: 3266
- Joined: Tue Jan 23, 2007 9:56 pm
- Location: Utah
- Contact:
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.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.
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?
- aebrown
- Community Administrator
- Posts: 15153
- Joined: Tue Nov 27, 2007 8:48 pm
- Location: Draper, Utah
Here is my specific example:mkmurray wrote:Ya know, I haven't figured out how to correctly sort by more than one criteria either.
- I created a custom report for MP with Full Name, Age, and Birth Country.
- Generated the report.
- I clicked on the column headers in this order: Full Name, Birth Country, Age
- 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
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:
- Make the sort order specification explicit, rather than depending on clicking on column headers in reverse order.
- 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).
- aebrown
- Community Administrator
- Posts: 15153
- Joined: Tue Nov 27, 2007 8:48 pm
- Location: Draper, Utah
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.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?
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").
-
- Community Administrator
- Posts: 34417
- Joined: Sat Jan 20, 2007 2:53 pm
- Location: U.S.
Or OpenOffice's Calc which can be found on most administrative computers.kjheinz wrote:I would recommend downloading it to a CVS file and using exel to do your sorting instead.
Have you searched the Help Center? Try doing a Google search and adding "site:churchofjesuschrist.org/help" to the search criteria.
So we can better help you, please edit your Profile to include your general location.
So we can better help you, please edit your Profile to include your general location.
-
- Member
- Posts: 75
- Joined: Tue Jan 27, 2009 8:17 am
- Location: US