I have a BAQ report which is supposed to sort by employee name alphabetically. In the query, it works fine. I am using the information in an SSRS report. I use a list to put together the form. When the report comes back, it is mostly in order with the exception of a few records. If I alphabetize by first name, 4 are incorrect. If I order by last name, those same 4 are incorrect plus 2 more. If I take the sort out of the BAQ and add one to the BAQ Report, same results.
It is not printing them in Employee ID order (was a thought). If I remove the order altogether, it prints in a different list than when the sort is applied. So it kinda alphabetizes, but not all of them.
For perspective, the report has 160 records and only 4 (or 6) are incorrect.
First thoughts are leading spaces. I am guessing you checked for that. So, my next thought is, can you sort right in the SSRS instead, skipping the BAQ sort and the BAQ Report sort?
Some sort utilities are built to ignore certain character types. Not sure what windows is doing here but it could be eliminating the spaces and symbols before sorting.
This habit also goes way back to the time of paper phone books and yellow pages, where they would not include spaces when doing the sorts.
Aa b
Aab
A ab
Aa b
It could turn out random looking.
Leading spaces or unreadable characters were my first thought - not the case.
I tried sorting in the SSRS but had issues. First, I’m using a grid and not a table. Not sure if that will make a difference or not but I’m printing off forms and not a table (employee reviews) When I go into the sort section of the SSRS, no fields populate:
I tried just typing in the field name since it does show up in the report, but it just ignores it.
In this case I’m having a last name that begins Cas being inserted between ones beginning with Bar and Bet. Other examples are similar. As stated, the alphabetizing is correct within the query itself, just incorrect when the report is printed.
So I did as you suggested. In the BAQ, it is ordered by name alphabetically. I then added a sort on the BAQ Report Designer sorting by the Calculated_RowNumber. Prints in the same order.
I left it named Name for the sort for less confusion for others using it but sorted by the name.
I went back to the report and added the row number so I could see if it messed up the numbers when sending to SSRS but it does not. Report runs and prints in the row number order 1, 2, 3, 11, 4… etc.
So went back in again to the SSRS and now the field is showing in the same dropdown it wasn’t before.
Why it’s now in the tablix properties when it wasn’t before, I’m not sure. Considered I was just losing it but in the prior screenshot, it’s not there.
Once adding it there, the order is working. Thanks for the help. Greatly appreciated.
More so from imported data, But I’ve had to expand strings into individual characters and display their ASCII code. There is a space that has ASCII code 160(dec). Or there’s an em-dash vs an en-dash vs a regular dash. And there straight quotes, and fancy quotes (Both for double and single quotes).
These odd characters often get in when someone copies text from a website or PDF, and the source used some funky characters.
And I’ve no idea how Unicode encoded strings are sorted.
