Dynamic SQL with Parameters

So far, you have seen how parameters can be passed onto query values, but can you rearrange the whole query with parameters? The answer is yes.

Believe it or not, the query is just an expression like any other expression. By default, it is evaluated as a constant string, but with the use of parameters and custom code blocks, you can make the query behind the report dynamic. There is a catch to all that flexibility. You must return the same number of columns with the same names no matter what the query.

A good example of this is in the ORDER BY or GROUP BY clause within a query. Although any of the controls can sort or group the results of a data set by any column, they are limited in speed and capacity of the Report Server. By contrast, most databases are built for exactly this sort of thing, and with the effective use of indexes, a lot of spare CPU cycles can be recovered.

To make a dynamic query, open the Generic Query Designer and type in an expression that evaluates to a query. The following is an example that uses the Emphasis parameter used previously:

 ="select * from test_tb order by " & Parameters!Emaphasis.Value 

You could also declare this in a custom code block:

 Function fnGetSql(Byval parameter as String) as String Return "select * from test_tb order by " & Parameters!Emaphasis.Value End Function 

And call it like this:

 =Code.fnGetSql(Parameters!Emaphasis.Value)