I would suggest to add a data item which has the code of sorting inserted. Sort the list report in this. This data item should contain the code of prompt macro, to extract the value of which column it needs to sort next. Sort the list report with this column. Create a static prompt with all the data item names by which you want the sorting to be performed. Use that prompt parameter in prompt macro code.

Create a data item in your query called something like "sortKey" and use an expression to populate the value based on their response to the prompt you define which contains a list of static choices.

case ?SortChoice?
when 'Project Name' then [ProjectName]
when 'Priority' then [Priority]
etc.
end

Then specify this sortKey data item as the sort for your list. Note that all elements of the case must return the same data type so you can't mix text and numbers. You can cast numbers to text to get the same result but be aware that numbers 1 through 10 when converted to text will sort 1, 10, 2, etc.

Lynn said...
Then specify this sortKey data item as the sort for your list. Note that all elements of the case must return the same data type so you can't mix text and numbers. You can cast numbers to text to get the same result but be aware that numbers 1 through 10 when converted to text will sort 1, 10, 2, etc.

For sorting numeric fields, I usually do the same thing that Lynn mentioned, but I also append leading zeros to the text field so the numbers will sort correctly. For example, if the maximum number will only be four characters in length, I'll use the following expression: