I needed to create a Parameter that would allow me to select “Year” in a Drop-Down List using SQL Server Reporting Services.
In this example we will create the Parameter in the Stored Procedure.
First you must declare the Variable
Then you add to the “Where” clause
From the “Design” section of the Report
In the Report, the new parameter @Year is set automatically from the stored procedure.
To get the drop-down list you will create a new Dataset called Parameter_Year. Be sure to use DISTINCT to limit the values from this table.
Next you want to modify the Parameter from the report and change the Available Values to “Get values from a query”