Column With Leading Underscore Does Not Sort Correctly in Cross-Tab Report
1 June, 2016
I have some data columns that begin with an underscore to make sure they are sorted at the beginning of lists; however, in my cross-tab report, they are showing-up between the letters V and Z; not at the beginning as expected.
Jason
Jason
Hi Jason,
Sorry to hear you're having trouble getting column names to sort as expected.
Although I think I've got the general idea of what's going on, it would be really helpful if you could upload a couple of screenshots illustrating exactly under what circumstances your sort goes awry.
Also, are these data columns named with a preceding "_" named like this at the database level, the view level, or the report level?
If you could get back to these questions when you have a free moment, that would be great!
Thanks,
-Conner
Sorry to hear you're having trouble getting column names to sort as expected.
Although I think I've got the general idea of what's going on, it would be really helpful if you could upload a couple of screenshots illustrating exactly under what circumstances your sort goes awry.
Also, are these data columns named with a preceding "_" named like this at the database level, the view level, or the report level?
If you could get back to these questions when you have a free moment, that would be great!
Thanks,
-Conner
Conner,
The data exists this way in the database. They are string values and I'm using Yellowfin to turn that into cross-tab columns (see below). You can see that the two values that begin with underscores show up between values that begin with "v" an "z".
If I take this same data and just make it part of a standard table, the values starting with underscores show up at the end of the sort.
Either way, the sorting is not as I would expect it. In my experience, an underscore is sorted before any numbers or letters.
Jason
The data exists this way in the database. They are string values and I'm using Yellowfin to turn that into cross-tab columns (see below). You can see that the two values that begin with underscores show up between values that begin with "v" an "z".
If I take this same data and just make it part of a standard table, the values starting with underscores show up at the end of the sort.
Either way, the sorting is not as I would expect it. In my experience, an underscore is sorted before any numbers or letters.
Jason
Hi Jason,
Thanks for getting back and the for the extra info.
I've done some research on this, and here's my theory:
In Windows and OSX, preceding a file name with an underscore will boost it to the top of an alphanumerically sorted list. However, this is regarded as something of a quirk/feature specific to those operating systems. In most Unix and Linux distributions, the sort is prioritized by case sensitivity. For example, if I had the set containing {_, A, b, @, C, d, E, f, !}, it would sort like this {A, C, E, _, !, @, b, d, f} (I'm not certain about the exact order of the symbol characters).
From your cross-tab screenshot, this seems to be what is happening. The starting character goes from a capital (Vacation) to a symbol, (_Reg Ele...) to a lower-case letter (z_Submit...).
You can read more about this here, although it is pretty dense in parts:
http://unix.stackexchange.com/questions/39827/how-do-i-make-ls-sort-underscore-characters-first
I believe that this ordering is done in the database, and not Yellowfin itself, when the query is passed. However, this does not explain why the underscores are moved all the way to the bottom when viewing as part of a standard table. I'm going to have to do some further research and testing to see why this is happening.
In the meantime, a way to circumvent the standard sort order could be to set up Org Ref Codes. This wiki article should get you off and running with them:
http://wiki.yellowfin.com.au/display/USER72/Org+Reference+Codes
Please let me know if you have any further questions.
Thanks!
-Conner
Thanks for getting back and the for the extra info.
I've done some research on this, and here's my theory:
In Windows and OSX, preceding a file name with an underscore will boost it to the top of an alphanumerically sorted list. However, this is regarded as something of a quirk/feature specific to those operating systems. In most Unix and Linux distributions, the sort is prioritized by case sensitivity. For example, if I had the set containing {_, A, b, @, C, d, E, f, !}, it would sort like this {A, C, E, _, !, @, b, d, f} (I'm not certain about the exact order of the symbol characters).
From your cross-tab screenshot, this seems to be what is happening. The starting character goes from a capital (Vacation) to a symbol, (_Reg Ele...) to a lower-case letter (z_Submit...).
You can read more about this here, although it is pretty dense in parts:
http://unix.stackexchange.com/questions/39827/how-do-i-make-ls-sort-underscore-characters-first
I believe that this ordering is done in the database, and not Yellowfin itself, when the query is passed. However, this does not explain why the underscores are moved all the way to the bottom when viewing as part of a standard table. I'm going to have to do some further research and testing to see why this is happening.
In the meantime, a way to circumvent the standard sort order could be to set up Org Ref Codes. This wiki article should get you off and running with them:
http://wiki.yellowfin.com.au/display/USER72/Org+Reference+Codes
Please let me know if you have any further questions.
Thanks!
-Conner
Conner,
I just wanted to add that we are in an all Microsoft environment; server, database, client. When I sort the data at the database level, the underscores show up at the beginning like I'm used to.
Jason
I just wanted to add that we are in an all Microsoft environment; server, database, client. When I sort the data at the database level, the underscores show up at the beginning like I'm used to.
Jason