You can allow the person running a report (the "report runner") to order report results by including special syntax in an ORDER BY clause in the SQL statement.
In Plateau Report Designer, create a parameter the element you want users to order by and make its selection values the orderable columns. For example, if you want users to be able to order their report by first name or last name, create a parameter with two selection values: first name and last name. You can view an example of the parameter.
In the report query, include the text [order_by_parameter:BIRT Parameter "SelectionValue1=Column1;SelectionValue2=Column2"] replacing:
"SelectionValue1" and "SelectionValue2" with the selection values as you defined them in your parameter.
"BIRT Parameter" with the parameter you created for user input at run time.
"Column1" and "Column2" for the columns in your SELECT statement that match the parameter selection values.
To fully test ordering, you must import the report to Talent Management. When it is unpublished, you can troubleshoot it, but it will not be accessible to most users.
The following example creates ordering where the report runner can order by either first name or last name of a user. FNAME and LNAME are parameter values that represent the first name and the last name of users. "OrderBy" is the name of the parameter, and a.fname/a.lname are the columns that represent the user-orderable fields. You can view an image of the parameter.
The name of the prompt text in the parameter is the "SortBy" label. Do not confuse the label with the SQL. Most users understand the ORDER BY clause as sorting, as in sorting a column in Microsoft Excel.
SELECT a.stud_id, a.lname, a.fname
FROM pa_student a
WHERE a.active = ?
/** AND a.stud_id in [UserSearch]
AND [security:pa_student a]
ORDER BY [order_by_parameter OrderBy "FNAME=a.fname;LNAME=a.lname"]
*/
If the user selects to order by last name, then the run time query is:
SELECT a.stud_id, a.lname, a.fname
FROM pa_student a
WHERE a.active = user selection
/* SEARCH FILTER START*/
AND a.stud_id IN (result of user filter)
/* SEARCH FILTER END*/
/* SEC FILTER START*/
AND domain filtering
/* SEC FILTER END*/
/*ORDER BY START*/
ORDER BY a.lname
/*ORDER BY END*/