Create Drop downs in reporting services’ reports

Recently, I was working on a project where I had to create drop downs as follows:

Just to help beginners, i thought it will be good to share how to do this.

Here are the steps which we follow to get the drop down on the report page so that we can choose between different options.

1. First of all , we will need to decide how many parameters we want to have in the report. Think it like a where clause e.g. lets say you want to select the employees from India region and sex should be female. We will select these values from the drop down box and based on that we will get the output.

All the next steps will be based on the above example.

2. Once we are clear about what kind of parameters we want, we will need to create the datasets. I am hoping that you are aware of all the basic concepts of RS e.g. datasets,parameters etc.

Lets create a table with some values:

create table employee(name varchar(20),sex char(1),location varchar(20),age numeric(10))

insert into employee values(‘Harsh’,’M’,’India’,27)

insert into employee values(‘Gaurav’,’M’,’India’,27)

insert into employee values(‘Divya’,’M’,’India’,27)

insert into employee values(‘Neha’,’F’,’India’,23)

insert into employee values(‘Pranab’,’M’,’India’,27)

insert into employee values(‘Sanket’,’M’,’China’,27)

Now, if we check on what will be the query with where clause:

==> select * from employee where location = ‘India’ and sex=’F’ and age=23

3. Now we are clear of the condition and we will move forward to create a report with parameters and multiple datasets as follows:

We will have one dataset which we will define while creating a report:

image

image

This will be the main dataset which we will customize and add the parameters to get the drop down thing working.Now, we have got the dataset against which we have to add the where clause to customize the output.

The next step would be to add more datasets which we could use for the where clause. For now, hold on to your thoughts as it will be more clear as we move further in the blog.

Lets create another dataset which will select the different locations from the table; right click on datasets folder and add datasets:

image

And same way another dataset for sex column.

image

Now , as we have created the datasets and it’s time for the real thing.

We will create the parameter which we will be used in the report by right clicking on parameters and add parameters:

image

This is the first parameter named @sex and next step will be to assign the value to the parameter and will appear as drop down on the report(dataset2 is query which will obtain the value of sex column):

image

and same way we will create a variable named location.Once we are done with that, we will change the query in the dataset1 as follows:

image

If we see, we have added the parameters in the where clause to get the output we are looking for. Now, If we preview the report – it will look like: –

image

HTH!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s