I received a couple of emails from the list about sorting union queries. It's fairly straight forward if you want all the reports sorted the same way, you just add the sort to the report query. If, on the other hand, you want individual reports sorted differently, is not so direct. Let's say we have query that unions the SHRLGPA and SHRTGPA tables. The actual query, written in the Free Type Mode, would be:
select 'LGPA' "Area", f_getspridenid(SFBETRM.SFBETRM_PIDM) "id", f_format_name(SFBETRM.SFBETRM_PIDM, 'LFMI') "name", SHRLGPA.SHRLGPA_GPA "gpa" from SHRLGPA, SFBETRM where ( SFBETRM.SFBETRM_PIDM = SHRLGPA.SHRLGPA_PIDM ) and ( SFBETRM.SFBETRM_TERM_CODE = '200930' and SHRLGPA.SHRLGPA_GPA_TYPE_IND = 'I' and SHRLGPA.SHRLGPA_LEVL_CODE = 'CR' ) union select 'TGPA' "Area", f_getspridenid(SFBETRM.SFBETRM_PIDM) "id", f_format_name(SFBETRM.SFBETRM_PIDM, 'LFMI') "name", SHRTGPA.SHRTGPA_GPA from SFBETRM, SHRTGPA where ( SFBETRM.SFBETRM_PIDM = SHRTGPA.SHRTGPA_PIDM and SFBETRM.SFBETRM_TERM_CODE = SHRTGPA.SHRTGPA_TERM_CODE ) and ( SFBETRM.SFBETRM_TERM_CODE = '200930' and SHRTGPA.SHRTGPA_GPA_TYPE_IND = 'I' and SHRTGPA.SHRTGPA_LEVL_CODE = 'CR' )
You then need to add two special tags using the Insert Special button. These being the −−$beginorder and −−$endorder tags. You will then end up with a query that looks like:
You can now close the Query Builder and move on to creating your report. Once your report is done, it's time to add the sort criteria. When you are in the Edit Report window, click on the Sort tab, move the fields you want to sort by into the Ordered fields and you should end up with a wondow that look ssomething like:
Click on the first field and then click the "Edit the Properties..." button.
The Edit Sorting Field window will open, looking like:
You will want to change name to the field number, in this case 3, like:
When you are done with all your fields, it should look like:
You will now be able to run your report with the sort order you have defined.