There are certain key words in a T-SQL query than cannot be parameterized, including the TOP statement. Consider the followong attempt to parameterize the number of rows returned from the query results:
Select Top @TopCustomerCount
F.CustomerKey, F.OrderDateKey,
Case When DC.MiddleName Is Null Then
FirstName + ' ' + LastName
Else FirstName + ' ' + MiddleName + '.' + ' ' +
LastName End CustomerName,
Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate,
Sum(F.SalesAmount) SalesAmount
From
FactInternetSales F, DimTime DT, DimCustomer DC
Where
F.OrderDateKey = DT.TimeKey
And F.CustomerKey = DC.CustomerKey
And Convert(Int,DT.CalendarYear) = @Year
Group By
F.CustomerKey, F.OrderDateKey, DC.MiddleName,
DC.FirstName, DC.LastName, DT.FullDateAlternateKey
Order By
F.OrderDateKey Desc, CustomerName
This query will not work because the query designer can’t resolve the TopCustomerCount parameter. To solve this problem, build an expression to concatonate the entire query, with parameterized literal values like the following example:
=”Select Top ” & Parameters!TopCustomerCount.Value & ” ” _
& ” F.CustomerKey, F.OrderDateKey, ” _
& ” Case When DC.MiddleName Is Null Then “ _
& “ FirstName + ‘ ‘ + LastName ” _
& ” Else FirstName + ‘ ‘ + MiddleName + ‘.’ + ‘ ‘ “_
& “ + LastName End CustomerName, ” _
& ” Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate, ” _
& ” Sum(F.SalesAmount) SalesAmount ” _
& “From ” _
& ” FactInternetSales F, DimTime DT, DimCustomer DC ” _
& “Where ” _
& ” F.OrderDateKey = DT.TimeKey ” _
& ” And F.CustomerKey = DC.CustomerKey ” _
& ” And Convert(Int,DT.CalendarYear) = ” _
& Parameters!Year.Value & ” Group By ” _
& ” F.CustomerKey, F.OrderDateKey, DC.MiddleName, “ _
& “ DC.FirstName, DC.LastName, DT.FullDateAlternateKey ” _
& “Order By ” _
& ” F.OrderDateKey Desc, CustomerName”
This same technique can be used to resolve complex decision structures in code before building the SQL Statement string. For even more flexibility, create a custom function in the report properties Code window and use Visual Basic.NET code to build and return the entire query string.