Sort report using parameters ?

[sdonaldson]

Using CR 8.5 Developer Edition on Windows 2000.
I have a report which I wish to allow the user to sort in one of three ways - by account number, this year's turnover, or last year's turnover.
I know, and have proved, that this can be done by using a formula to select the appropriate database field(s) and including it in the Sort Fields list, but this does not appear to alter the ORDER BY clause of the SQL SELECT statement.
My understanding is that this will result in the sorting being performed locally by CR and not on the SQL server.
Is there any way of dynamically changing the ORDER BY clause according to a report parameter ?

Thanks in advance.
Shaun

 

[troyboy]

The only way I know that you could achieve this is to base the report on a stored procedure and pass the sort order as one of the parameters. As you are using SQL Server, you can use dynamic sql. for example

declare @v_ssql as varchar(8000)

set @v_ssql = 'Select * from table'
if @sort = 1 then
set @v_ssql = @v_ssql + ' order by 1 '
end if

exec (@v_ssql)

Hope this helps

 

[sdonaldson]

Thanks for that, but I cannot change or add to the SQL Server configuration.
We're going to leave the report sorting locally as we do not expect it to contain a large amount of data. If there is a noticable performance penalty then we'll address it (somehow)...

Regards,
Shaun