Dynamic Pick Lists: Pick List SQL

The SQL statement entered here is used to build the dynamic pick list by retrieving the current values from the specified database connection.

The SQL statement must follow a specific naming convention and must include at least two columns. The column that contains the values to pass to the report must be aliased as ValueColumn. The column that contains a description to display in the pick list must be aliased as DescriptionColumn. If you do not have a separate description column, include the value column twice and alias one of them as the DescriptionColumn.

Example SQL Statement:

SELECT StateAbbreviation As ValueColumn, Description As DescriptionColumn FROM StateList

@UserID:

It is also possible to use the currently logged on user as the value to use as the basis of a parameter. To have the current user substituted as the value at runtime, enter @USERID in the 'Where' clause.

@UserID Example:

SELECT SaleID As ValueColumn, SaleAmount As DescriptionColumn FROM Sales WHERE SalesPersonID = @UserID

You can also use @UserID2, @UserID3, @UserID5 or @UserID5 within the SQL statement.  These tokens will be substituted with values that are entered in the Users record.  They can be used when the User Login is not the value that you want to pass to the SQL statement.  You can found out more about adding the UserID Tokens in the Users section