Data Source Sorting

A designer can use the ORDER BY clause in a query. In this case, the database performs sorting. Especially in cases in which the amount of data is large, this provides the best performance. In this case, a developer needs to consider performance implications for the database server. In addition, because the query approach has a scope of an entire data set, it might not provide needed fine-tuning for data sorting.

When necessary, a designer can parameterize data source sorting by using the following expression used as the query in a data set: ="SELECT {fields} FROM {table} ORDER BY " & Parameters!MySort.Value .

Parameter MySort should contain a valid list of database fields or numbers corresponding to the fields to use in ORDER BY .

For example, to retrieve a list of employees , use the following expression:

 ="SELECT FirstName, LastName, Title FROM Employee ORDER BY " & Parameters!MySort.   Value 

,In this e,xample, MySort could be set to a nonqueried parameter with the values FirstName and LastName or values 1 and 2 . For more details about parameters, including information on how to pass a parameter value to a report, refer to Chapter 10, "Report Parameters."

For dynamic query expressions, developers need to make sure that Parameters!MySort.Value properly corresponds to a database field or fields. In addition, a dynamic query expression has to return a valid query. Each properly placed space is crucial, such as the space between ORDER BY and a parameter.

Query design has to follow best practices to avoid SQL injection. Here is a good article on avoiding SQL injection attacks: http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/.