dynamic Sorting in SSRS

Question

 

Hi,

 

I needed a way to sort dynamically in a Reporting Services report by selecting a Column Name dynamically from a dropdown list.

 

Thanks

Prajith

Answer 1

Use a query type of text and do it this way:

 

= " SELECT <your query>  ORDER BY "  & @YourParam

 

... or use a query type of sproc and pass the param to the sproc...

 

No?

 

>L<

Answer 2

 

Thanks for your information.

 

Answer 3

There is solution for ur problem.What you have to do is, create  a parameter and provide values for it.

These values must be the column names of report.

 

Then use

=Fields(Parameters!.Value).Value

 expression in Interactive sort tab.Here(Parameters!.Value) becomes ur selected parameter i,e field name.

then finally it becomes , =Fields!.xxxx.Value

 

Just u try this, u wil get.

 

Also u go through this article, search in google

Beginning SQL Server 2005 Reporting Services Part 2

by Steve Joubert

 

 

Answer 4

Hi,

 

Thanks for your kind attention. I tried your method its working fine. But i have done it in different way. What i ahve done is passing a parameter to the query and inside the order by clause i have used as follows

 

ORDER BY

CASE WHEN @OrderBy = 'ColumnName' THEN TableName.ColumnName

WHEN @OrderBy = 'ColumnName' THEN TableName.ColumnName

END

 

by Prajith

Answer 5

Yes -- this is the same as passing it to the sproc <s>. 

 

FWIW you can do it with the interactive sort as your other response indicates as well -- the only thing you have to realize is that the result may not be any different. IOW, I believe the query is re-run in both cases, whether the user presses the "refresh report" button or uses the interactive sort feature.  But I have not tested this.

 

Does anybody reading know for sure?

 

>L<