Argos© Reporting - Sorting a Report with data from a Union Query

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:

Free Query Image

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:

Edit Report Before

Click on the first field and then click the "Edit the Properties..." button.

The Edit Sorting Field window will open, looking like:

Edit Sort Field Before

You will want to change name to the field number, in this case 3, like:

Edit Sort Field After

When you are done with all your fields, it should look like:

Edit Report After

You will now be able to run your report with the sort order you have defined.