Parameterized Report in SSRS

The Main Purpose of writing this article is to filter the data based on parameter values……

For this.., need to create a table like this

create table employee(eid int,ename vachar(30),designation varchar(20), Salary int,eaddress varchar(200),phonenumber int)

eid	Ename	Designation	Salary	eaddress	Phonenumber
1	Rajesh	Developer	10000	Hyderabad	123456
2	Rani	SSE	100000	USA	012344556
3	Venkat	Manager	100000	USA	090909090

Now My Requirement is to Filter the data by Employee Name…

For that Open BIDS

And Select Report Server Project and add new report..

In Report ..Specify your Data Source

Most of the cases we use Shared Data Source..,

After Specify the data source..,

Create a Dataset…

In Dataset u write your Query like…

Select * from employee

After that drag and drop a table from toolbox..

And specify the columns like this

After assign the columns

Press preview to see the report..

Now…we have to filter the data by employee name..

For that we have a create a parameter and ,specify that parameter in dataset also like this…

Create a new dataset and write the specify the query as

Select ename from employee  ..,because we are filtering based on ename

In Report Parameter Properties…

Specify Report Parameter name.I specified as ename

Specify the Prompt as Select Employee Name

In Available Values Section.., Select Get Values from Query..and assign our new dataset to it.., then specify the value field as column name ename and lable field as column name ename for it..thats it.

Now assign this parameter “ename” in our dataset

Then our query will be like this

Select * from employee where ename =@ename

We assign the parameter to a variable or column by using @ symbol.

Now u see the preview…

Select the employee name and click view report.., the data will filter and the selected employee’s details will be display